PL/SQL: Procedure Linking

[Exie]

Hi,

I have 2 oracle proceedures. For my example, I'll call them A and B. Procedure A accepts a parameter called Z of type varhcar2. Z is a string that represents the next procedure that needs to be called (eg. Z = 'B').

How can I use the value of Z to invoke procedure B ?

 

[LKBrwnDBA]

Use dynamic SQL. (EXECUTE IMMEDIATE)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Exie]

Thanks, thats a great tip. It doesnt seem to quite do what I want though. Here's another example:

   procedure migrate(table_nm IN VARCHAR2) is 
   begin

       -- disable constraints for table_nm 
       -- truncate table 
      execute immediate table_nm; 
      commit;
       -- enable constraints for table_nm 
   exception 
         when others then
            raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
            rollback;
   end migrate;


   procedure table1 is 
   begin
      .... content to migrate this table ....
   end table1;

I then call exec migrate('table1'); and have it just migrate that one table. When I use execute immediate I get an SQL parse error.

 

[LKBrwnDBA]

Maybe you need to add a begin--end:

...
      execute immediate 'BEGIN '||table_nm||'; END;'; 
...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

What exactly is table_nm ? If it's a table name, the code "execute immediate table_nm" is meaningless. You need something like:

execute immediate 'truncate table '||table_nm;

 

[LKBrwnDBA]

@dagon:

table_nm is the name of the parameter from exie's (the poster) procedure, and in context actually would contain a procedure name.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

Why not call it proc_nm then ?

 

[LKBrwnDBA]

Who knows, ask exie

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb