How to use dynamic sql in pl/sql?

Question

I have the following pl/sql procedure as an example (my procedure is much bigger, but my problem is regarding dynamic sql). The problem for me is how to use the variable V_DATE inside SQL_STMT:

CREATE OR REPLACE PROCEDURE TEST (DATE_A IN DATE) IS    
   V_DATE DATE;    
   SQL_STMT VARCHAR2(4000);    
BEGIN     
   V_DATE = DATE_A;
   SQL_STMT = 'CREATE TABLE TABLE_1 AS 
               SELECT * 
                 FROM TABLE_2 A 
                WHERE A.DATE_FROM = V_DATE';
    
   EXECUTE IMMEDIATE SQL_STMT;
   COMMIT;        
END TEST;
/

William Robertson · Answer 1 · 2021-03-29T16:36:37.623

You would need to concatenate the value. (Normally bind variables are a good idea, but they can't be used with DDL.)

create or replace procedure test
    ( date_a in date )
as
    sql_stmt varchar2(4000);
begin
    sql_stmt :=
        'create table table_1 as select * from table_2 a '||
        'where a.date_from = date '''||to_char(date_a,'YYYY-MM-DD')||'''';

    dbms_output.put_line(sql_stmt);
    execute immediate sql_stmt;
end test;

The generated statement will be something like:

create table table_1 as select * from table_2 a where a.date_from = date '2021-03-27'

Note that this will only work once, as after that the table will already exist so the create table statement will fail, but this is just an example to demonstrate the syntax.

Kevin Seymour · Answer 2 · 2021-03-29T17:33:11.593

While concatenation is an option, because of SQL injection (although maybe not in play here with a DATE) and the hard parse you are going to experience, I might explore the following option instead. You are going to generate some more undo in this scenario, but it might be acceptable for what you are doing. You could also add APPEND or NOLOGGING hints to see if that helps. There is some good comparisons of those hints here.

DROP TABLE dba_objects_date;

CREATE OR REPLACE PROCEDURE date_test(date_a IN DATE) AS
  sql_stmt VARCHAR2(4000);
BEGIN
  sql_stmt := 'create table dba_objects_date as select * from dba_objects a ' ||
              'where 0 = 1';
  EXECUTE IMMEDIATE sql_stmt;

  sql_stmt := 'insert into dba_objects_date select * from dba_objects a ' ||
              'where a.created >= :date_from';

  EXECUTE IMMEDIATE sql_stmt
    USING date_a;
END date_test;
/

BEGIN
  date_test(SYSDATE - 14);
END;
/

SELECT COUNT(*)
  FROM dba_objects_date;
-- 821

How to use dynamic sql in pl/sql?

2 Answers2