Wednesday, January 17, 2018

ORACLE - How call DDL command from stored procedure + how to create global temporary table

For calling DDL (Data definition command) from Oracle PL/SQL stored procedure use execute immediate command:
function sql_temporary_piecelist return number
is
begin

  /* delete - if exists */

  execute immediate
   '
   drop table temporary_piecelist
  ';

  /* create new one */

  execute immediate
   '
   create global temporary table temporary_piecelist
   ( key number,
    name varchar2(25 )
   )
   on commit preserve rows
  ';

  return( 0 );
end;
Note: "On commit preserve rows" clause ensure, that global temporary table will be exists to end of the oracle session (..then will be deleted).

No comments:

Post a Comment