Message-Id: <25988.338987@fatcity.com> From: "Jacques Kilchoer" Date: Tue, 22 Jul 2003 14:53:39 -0700 Subject: RE: Who Says Oracle does not listen >-----Original Message----- >From: Igor Neyman [mailto:ineyman@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. -- drop_object parameters: -- owner and object_name must be in the correct case, without extraneous spaces -- to drop a user, leave object_name null -- to drop a context, private database link, directory, outline, profile, role, -- rollback segment or tablespace: leave owner null -- acceptable values for object_type: -- "cluster", "context", "database link", "dimension", -- "directory", "function", "index", "indextype", -- "java source", "java class", "java resource", -- "library", "materialized view", "materialized view log", -- "operator", "outline", "package", "package body", -- "procedure", "profile", "role", "rollback segment", -- "sequence", "snapshot", "snapshot log", -- "synonym", "table", "tablespace", "trigger", -- "type", "type body", "user", "view" create procedure drop_object (owner varchar2, object_name varchar2, object_type varchar2) is cmd_syntax varchar2 (40) ; sql_statement varchar2 (200) ; c_dynsql pls_integer ; ignore pls_integer ; 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) ; -- error for following object_types: -- java source, java class, java resource no_java exception ; pragma exception_init (no_java, -29501) ; 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) ; -- error for following object_types: -- materialized view, snapshot no_snapshot exception ; pragma exception_init (no_snapshot, -12003) ; -- error for following object_types: -- materialized view log, snapshot log no_snapshot_log exception ; pragma exception_init (no_snapshot_log, -12002) ; -- error for following object_types: -- function, package, package body, procedure no_source exception ; pragma exception_init (no_source, -04050) ; no_synonym exception ; pragma exception_init (no_synonym, -01434) ; no_pub_synonym exception ; pragma exception_init (no_pub_synonym, -01432) ; -- error for following object_types: -- table, view no_table exception ; pragma exception_init (no_table, -00942) ; 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) ; -- error for following object_types: -- context, directory, function, java source, java class, -- java resource, library, package, package body, procedure, -- type, type body no_object_generic exception ; pragma exception_init (no_object_generic, -04043) ; 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 ; 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 ;