Executing a procedure gives this error

Posted on 2006-04-04
Last Modified: 2008-01-09
After sucessfully upgrading to 10g from 9i . Executing a procedure gives this error.

SQL> exec adp_grants

ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SYSADM.DC_GRANTS", line 72
ORA-06512: at line 1
Question by:ballioballi
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what is the code of the adp_grants procedure?

    Author Comment

    It is very similar to this:-
    Runs good in all 9i env since 3 years

    CREATE OR REPLACE procedure adp_grants is
    b_sql varchar2(200);
    sql1_cursor        INTEGER;
       execute_sql1             INTEGER;

    cursor c1 is
    select 'grant select on '||object_name||' to query_only'
      from (select object_name
      from user_objects
     where object_type in ('VIEW', 'TABLE')
    select table_name
      from USER_TAB_PRIVS
    where grantee='QUERY_ONLY'
      and privilege = 'SELECT')
     union all
    select 'grant insert, delete, update on '||object_name||' to modify_also'
      from (select object_name, 3
      from user_objects
     where object_type in ('VIEW', 'TABLE')
      and SUBSTR(object_name,1,6) = 'PS_ADP_'

     union all
    select 'drop public synonym '||synonym_name
    from ( select synonym_name, table_name
             from all_synonyms
            where table_owner='ADP'
              and owner='PUBLIC'
    open c1;
    fetch c1 into b_sql;
    exit when c1%notfound;
       sql1_cursor := dbms_sql.open_cursor;
       dbms_sql.parse(sql1_cursor, b_sql, dbms_sql.native);
    dbms_output.put_line(' '||b_sql||' ');
       execute_sql1 := dbms_sql.execute(sql1_cursor);
    end loop;
    close c1;


    Author Comment

    This script is not the full script but it has  similar more lines for some more "table_owner."
    Same is working  fine in 9i

    Author Comment

    Any update ?? Experts??
    LVL 17

    Expert Comment

    That should be simple. The name of the object is already existing because,

    1.  You try to grant on something that is already granted.
    2. You try to drop something that is being still used.

    Check it out. Since you've a dbms_output.put_line, while executing find out which grant is throwing error then analyze.
    LVL 16

    Accepted Solution

    >>  ORA-00955: name is already used by an existing object

    The above error clearly says that an "An attempt was made to create either a table, view, cluster, index, or synonym that already exists"

    Search for any "create" in the procedure.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to recover a database from a user managed backup

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now