Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Who Says Oracle does not listen

RE: Who Says Oracle does not listen

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 22 Jul 2003 14:53:39 -0700
Message-Id: <25988.338987@fatcity.com>

>-----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 ;

    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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US