DDL in PL/SQL

DDL is for in experienced users sometimes a problem how o perform and even more-how to control result. Here is one way of solution for that. In mine case ddl is inside package tools_pkg.

Implementation

procedure ddl (ddl_text in varchar2, rows_processed OUT integer) is
AUTHID CURRENT_USER
  c            integer;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, ddl_text, dbms_sql.native);
  rows_processed := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
  -- if not DML then enlarge result for 1 (regular result returns zero)!
  if (INSTR (upper(ltrim(ddl_text)),'INSERT ')<=0) AND 
     (INSTR (upper(ltrim(ddl_text)),'UPDATE ')<=0) AND
     (INSTR (upper(ltrim(ddl_text)),'DELETE ')<=0) then
    rows_processed := 1;
  end if; 
exception
  when others then
    begin
      dbms_sql.close_cursor(c);
    exception
      when others then
        null;
    end;
    rows_processed:=-1;
end ddl;

"ddl" procedure in action

Here are some brief examples that will show DDL in action...in PL/SQL code. Important is that we can see the number of executed actions.

CREATE PUBLIC SYNONYM example

Let us create 90 public synonyms (XXX10..XXX99):

SQL> declare
  2    i   pls_integer; 
  3    tot pls_integer default 0;
  4  begin
  5    for j in 10..99 loop
  6      tools_pkg.ddl('CREATE OR REPLACE PUBLIC SYNONYM XXX'||j||' for damirv.tools_pkg',i);
  7      tot := tot+i;
  8    end loop;
  9    dbms_output.put_line('Synonyms created: '||tot);
 10  end;
 11  /
Synonyms created: 90

PL/SQL procedure successfully completed.

SQL> 

DROP PUBLIC SYNONYM example

Let's drop PUBLIC SYNONYMS:

SQL> declare
  2    i   pls_integer; 
  3    tot pls_integer default 0;
  4  begin
  5    for rec_cur_synonyms in (select synonym_name 
  6                               from dba_synonyms 
  7                              where OWNER='PUBLIC' and synonym_name like 'XXX__') loop
  8      tools_pkg.ddl('DROP PUBLIC SYNONYM ' || rec_cur_synonyms.synonym_name,i);  
  9      tot := tot+i;
 10    end loop;
 11    dbms_output.put_line('Synonyms dropped: '||tot);
 12  end;
 13  /
Synonyms dropped: 90

PL/SQL procedure successfully completed.

SQL> 

CREATE USER example

Let us create user:

SQL> set serveroutput on size 1000000;
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('grant resource, connect to user1 identified by q',i);
  5    dbms_output.put_line('Users created: '||i);
  6  end;
  7  /
Users created: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE 
  2  from dba_users 
  3  where username='USER1';

USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
------------ -------------------- ---------------------
USER1        USERS                TEMP

DROP USER example

Here we made a deliberate mistake (user2 doesn't exists):

SQL> set serveroutput on size 1000000;
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools.ddl('drop user USER2 cascade',i);
  5    dbms_output.put_line('Users dropped: '||i);
  6  end;
  7  /
Users dropped: -1

PL/SQL procedure successfully completed.

Now let's drop created user "USER1":

SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('drop user USER1 cascade',i);
  5    dbms_output.put_line('Users dropped: '||i);
  6  end;
  7  /
Users dropped: 1

PL/SQL procedure successfully completed.

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from dba_users 
  3  where username='USER1';

no rows selected

SQL>

DML in action

DML statements sre also supported in DDL procedure. We'll use rownum to control number of inserted records):

SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('INSERT INTO damirv.xx (select object_name, owner from dba_objects where rownum<=5)',i);
  5    dbms_output.put_line('Records inserted: '||i);
  6  end;
  7  /
Records inserted: 5

PL/SQL procedure successfully completed.

SQL>

Cheers!