Dynamic SQL

Example:

CREATE OR REPLACE PROCEDURE INSERT
IS
lc_sql      VARCHAR2(1000);
BEGIN

--insert records in tmp tables
lc_sql := ''||
' insert into tmp_records_master '||
     ' (select * '||
         'from records_master PARTITION (records'||to_char(sysdate-1, 'yyyymm')||') '||
       ' where datum between to_date(sysdate-1,''dd.mm.rrrr'')
         ' and to_date(sysdate,''dd.mm.rrrr'') '||
         ' and datum != to_date(sysdate,''dd.mm.rrrr''))';
EXECUTE IMMEDIATE lc_sql;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
     ROLLBACK;
END INSERT;

CREATE OR REPLACE PROCEDURE INSERT

lc_sql VARCHAR2(1000);

BEGIN

--insert records in tmp tables

lc_sql := ''||

' insert into tmp_records_master '||

' (select * '||

'from records_master PARTITION (records'||to_char(sysdate-1, 'yyyymm')||') '||

' where datum between to_date(sysdate-1,''dd.mm.rrrr'')

' and to_date(sysdate,''dd.mm.rrrr'') '||

' and datum != to_date(sysdate,''dd.mm.rrrr''))';

EXECUTE IMMEDIATE lc_sql;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END INSERT;

TOOLS

POSTS