This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Sunday, January 10, 2010

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!

1 comment :

  1. Hi!

    Just to remind you that every DDL is performing implicit commit-so use with persuasions in transactions!

    Hvani

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign