>-----Original Message-----
>From: Igor Neyman [mailto:[EMAIL PROTECTED]
>
>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 ;
when others then
if dbms_sql.is_open (c_dynsql)
then
dbms_sql.close_cursor (c_dynsql) ;
end if ;
raise ;
end drop_object ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
