PL/SQL Block 에서 DDL 사용하기

PL/SQL Block 에서 DDL 사용하기

--*************************************************************
BEGIN 

EXECUTE IMMEDIATE 'Drop Table PJSegmentOnly';

EXECUTE IMMEDIATE 'Create Table PJSegmentOnly AS
                                   Select "SegmentID" SEGMENTID from PassengerJourneyLeg@NaviODS';
END; 

--*************************************************************

-->> Delete Insert 보다 속도면에서 유리함.

--Delete PJSegmentOnly

--        ;

--    Commit

--;

--Insert Into  EJMIS.PJSegmentOnly  Select "SegmentID" SEGMENTID from PassengerJourneyLeg@NaviODS

--        ;

--    Commit

--; 

Another method is using of DBMS_SQL package.

SQL> CREATE OR REPLACE PROCEDURE dynamic_sql AS

  2       v_cursor integer;

  3       row_process  integer;

  4     BEGIN

  5       v_cursor := DBMS_SQL.OPEN_CURSOR;

  6            DBMS_SQL.PARSE(v_cursor, 'GRANT CREATE TABLE TO ARJU',DBMS_SQL.NATIVE);

  7            row_process := DBMS_SQL.EXECUTE(v_cursor);

  8       DBMS_SQL.PARSE(v_cursor, 'CREATE TABLE Test_SQL (col1 DATE)',DBMS_SQL.NATIVE);

  9       row_process := DBMS_SQL.EXECUTE(v_cursor);

 10       DBMS_SQL.CLOSE_CURSOR(v_cursor);

 11     END;

 12     /

Procedure created.

SQL> exec dynamic_sql

PL/SQL procedure successfully completed.