Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Who Says Oracle does not listen
>-----Original Message----- >From: Igor Neyman [mailto:ineyman_at_perceptron.com] > >In this case performance is not an issue. >I don't drop/create/modify tables/columns/synonyms every minute. > The script runs, when we install new release of our product, > happens once in a few months.
oops! Forgot the comments.
I hear what you're saying, but even if it's an install only run rarely, it's still nice to have the install run faster if it can. I don't know how many objects you are dropping and recreating so it may not be an issue.
Here's a sample drop procedure that catches exceptions. Of course to do it in a PL/SQL procedure the owner of the procedure will need privileges granted explicitly. But it can serve as an example of which exceptions to include in a PL/SQL anonymous block.
no_cluster exception ;
pragma exception_init (no_cluster, -00943) ;
no_cluster2 exception ;
pragma exception_init (no_cluster2, -02227) ;
no_dblink exception ;
pragma exception_init (no_dblink, -02024) ;
no_dimension exception ;
pragma exception_init (no_dimension, -30333) ;
no_index exception ;
pragma exception_init (no_index, -01418) ;
no_indextype exception ;
pragma exception_init (no_indextype, -29833) ;
no_indextype2 exception ;
pragma exception_init (no_indextype2, -29825) ;
no_operator exception ;
pragma exception_init (no_operator, -29807) ;
no_outline exception ;
pragma exception_init (no_outline, -18002) ;
no_profile exception ;
pragma exception_init (no_profile, -02380) ;
no_role exception ;
pragma exception_init (no_role, -01919) ;
no_rbs exception ;
pragma exception_init (no_rbs, -01534) ;
no_sequence exception ;
pragma exception_init (no_sequence, -02289) ;
no_synonym exception ;
pragma exception_init (no_synonym, -01434) ;
no_pub_synonym exception ;
pragma exception_init (no_pub_synonym, -01432) ;
no_tablespace exception ;
pragma exception_init (no_tablespace, -00959) ;
no_trigger exception ;
pragma exception_init (no_trigger, -04080) ;
no_user exception ;
pragma exception_init (no_user, -01918) ;
begin
cmd_syntax := rtrim (ltrim (lower (object_type))) ; if cmd_syntax = 'materialized view log'
or cmd_syntax = 'snapshot log'
then
cmd_syntax := cmd_syntax || ' on' ; end if ;
sql_statement := 'drop ' ;
if (cmd_syntax = 'synonym' or cmd_syntax = 'database link')
and lower (owner) = 'public'
then
sql_statement := sql_statement || 'public ' || cmd_syntax || ' ' ; elsif cmd_syntax = 'context' or cmd_syntax = 'database link'
or cmd_syntax = 'directory' or cmd_syntax = 'outline' or cmd_syntax = 'profile' or cmd_syntax = 'role' or cmd_syntax = 'rollback segment' or cmd_syntax = 'tablespace' then sql_statement := sql_statement || cmd_syntax || ' ' ; else sql_statement := sql_statement || cmd_syntax || ' "' || replace (owner, '"', '""') || '"' ; if cmd_syntax != 'user' then sql_statement := sql_statement || '.' ; end if ;
if cmd_syntax != 'user'
then
sql_statement := sql_statement || '"' || replace (object_name, '"', '""') || '"' ;end if ;
c_dynsql := dbms_sql.open_cursor ;
dbms_sql.parse (c_dynsql, sql_statement, dbms_sql.native) ;
ignore := dbms_sql.execute (c_dynsql) ;
dbms_sql.close_cursor (c_dynsql) ;
exception
when no_cluster or no_cluster2 or no_dblink or no_dimension
or no_index or no_indextype or no_indextype2 or no_java or no_operator or no_outline or no_profile or no_role or no_rbs or no_sequence or no_snapshot or no_snapshot_log or no_object_generic or no_synonym or no_pub_synonym or no_table or no_tablespace or no_trigger or no_user or no_source then if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; null ;Received on Tue Jul 22 2003 - 16:53:39 CDT
![]() |
![]() |