0

I have a Stored Procedure that outputs the DDL of another Stored Procedure. I need to execute this DDL dynamically. Meaning, the second Stored Procedure should get created when the first Stored Procedure is executed.

Here's the pseudocode for P_GEN:

CREATE OR REPLACE PROCEDURE P_GEN
AS
c1 SYS_REFCURSOR;  
stmt varchar2(4000);
   
BEGIN

open c1 for  
SELECT TXT FROM 
(
SELECT 
        'CREATE OR REPLACE PROCEDURE P_DEL' ....
    TXT
    FROM TBL
) ;

DBMS_SQL.RETURN_RESULT(c1);

END;
/

P_DEL, when created, would have the following code:

CREATE OR REPLACE PROCEDURE P_DEL
AS
BEGIN
    DEL('TEMP1');
    DEL('TEMP2');
    DEL('TEMP3');
END;
/

I need P_DEL to be created when P_GEN is executed. How to accomplish this?

2
  • Perhaps, instead of returning the CREATE PROCEDURE text, you should just execute it using DBMS_SQL.
    – mustaccio
    Commented Feb 18 at 23:12
  • I am not getting the syntax right for it. I tried adding these: iCursorID := DBMS_SQL.Open_Cursor; DBMS_SQL.Parse (iCursorID,'c1',DBMS_SQL.native); iReturned := DBMS_SQL.Execute (iCursorID); DBMS_SQL.Close_Cursor (iCursorID); Commented Feb 19 at 1:08

1 Answer 1

0

This is how I understood the problem.

Sample tables (that should be - I guess - deleted):

SQL> select count(*) from temp1;

  COUNT(*)
----------
         4

SQL> select count(*) from temp2;

  COUNT(*)
----------
        14

This is table that contains PL/SQL code, i.e. a create procedure p_del statement:

SQL> select * from tbl;

TXT
--------------------------------------------------------------------------------
create or replace procedure p_del as
begin
  del('temp1');
  del('temp2');
end;

Does P_DEL exist? Not yet:

SQL> select count(*) from user_objects where object_name = 'P_DEL';

  COUNT(*)
----------
         0

This is the del procedure which is being called from p_del; it (del) must exist, otherwise p_del will be created, but with errors. Disregard possible SQL injection at the moment, that's not the issue right now.

SQL> create or replace procedure del (par_table_name in varchar2) is
  2    l_str varchar2(200);
  3  begin
  4    l_str := 'delete from ' || par_table_name;
  5    execute immediate l_str;
  6  end;
  7  /

Procedure created.

p_gen procedure; it reads contents of the tbl table and executes it. The outcome (in this example) is that p_del procedure gets created:

SQL> create or replace procedure p_gen as
  2  begin
  3    for cur_r in (select txt from tbl) loop
  4      execute immediate cur_r.txt;
  5    end loop;
  6  end;
  7  /

Procedure created.

SQL> begin
  2    p_gen;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Does p_del exist now? Yes:

SQL> select count(*) from user_objects where object_name = 'P_DEL';

  COUNT(*)
----------
         1

Let's run it:

SQL> begin
  2    p_del;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Result: both sample tables are now empty:

SQL> select count(*) from temp1;

  COUNT(*)
----------
         0

SQL> select count(*) from temp2;

  COUNT(*)
----------
         0

SQL>

I didn't quite understand whether p_del should also "automatically" be executed; it can be, just call it within p_gen's loop, e.g.

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_gen as
  2  begin
  3    for cur_r in (select txt from tbl) loop
  4      execute immediate cur_r.txt;
  5      p_del;                             --> here
  6    end loop;
  7  end;
  8  /

Procedure created.

SQL> begin
  2    p_gen;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select count(*) from temp1;

  COUNT(*)
----------
         0

SQL>
1
  • Thanks! The issue is PL/SQL code in table TBL is dynamically generated one line at a time, joining a few tables. So first row will be 'CREATE OR REPLACE PROCEDURE ...' , second row would be 'AS', third row would be 'BEGIN', 4th row would be "DEL('TEMP1');" ... etc etc. All rows would need be UNION-ed in order to get the complete code for the Procedure. And that can vary dynamically. Not every time will the same number of tables will get deleted. Once the cursor is created dynamically with the PL/SQL code, how to get it executed is the question. Commented Feb 19 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.