sqloracle-databaseexecute-immediate

How do we pass a complete sql query from a table to an insert statement in PL/SQL using execute immediate


I'm trying to use the SQL queries stored in a table, in a insert statement inside the procedure.

Below is the statement_table,

STMT_ID STATMENT1                                              STATEMENT2
S001     INSERT INTO TABLE1 (S_ID, REQUEST_NUM,CASE_ID,C1,C2)   select min(s_id) s_id, REQUEST_NUM, CASE_ID, trim(c1),
                                                                ABS(max(case when source = 'S1' then  c2 end) +
                                                                max(case when source = 'S2' then -c2 end))
                                                                from TABLE2 where REQUEST_NUM =REQUEST_IN 
                                                                group by  REQUEST_NUM , CASE_ID, trim(c1)
                                                                order by S_ID    

Below is the procedure block,

 execute immediate 'select '||STATEMENT1||'+'||STATEMENT2||'   FROM statement_table where stmt_id='S001';

The result that I am trying to get is,

INSERT INTO TABLE1 (S_ID, REQUEST_NUM,CASE_ID,C1,C2)    
 select min(s_id) s_id, REQUEST_NUM, CASE_ID, trim(c1),
 ABS(max(case when source = 'S1' then  c2 end) +
 max(case when source = 'S2' then -c2 end))
 from TABLE2 where REQUEST_NUM =REQUEST_IN 
 group by  REQUEST_NUM , CASE_ID, trim(c1)
 order by S_ID    

Please help.


Solution

  • Here's how.

    First, your (actually, my) statements. I'm going to insert a row into Scott's DEPT table.

    SQL> select * from test;
    
            ID
    ----------
    ST1
    --------------------------------------------------------------------------------
    ST2
    --------------------------------------------------------------------------------
             1
    insert into dept (deptno, dname, loc)
    select max(deptno) + 1, 'stack', 'zoom' from dept order by 1
    
    
    SQL> select * From dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
             1 x              y
    

    Code; DBMS_OUTPUT is used to verify whether the statement is correct or not.

    SQL> set serveroutput on
    SQL> declare
      2    l_st1 test.st1%type;
      3    l_st2 test.st2%type;
      4    l_str varchar2(1000);
      5  begin
      6    select st1, st2
      7      into l_st1, l_st2
      8      from test
      9      where id = 1;
     10
     11    l_str := l_st1 ||' '|| l_st2;
     12    dbms_output.put_line(l_str);
     13    execute immediate l_str;
     14  end;
     15  /
    insert into dept (deptno, dname, loc) select max(deptno) + 1, 'stack', 'zoom'
    from dept order by 1
    
    PL/SQL procedure successfully completed.
    

    Result:

    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
            41 stack          zoom        --> this was inserted
             1 x              y
    
    6 rows selected.
    
    SQL>