Access SYS procedure from dba user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Access SYS procedure from dba user

  1. #1
    Join Date
    Jan 2001
    Posts
    66

    Access SYS procedure from dba user

    Using the the code at the bottom to build a procedure in sys, I ran into an unexpected issue. When I attempt to execute the following it does not work even though I granted Public and created a Public synonym:
    exec CHG_COLNM('BDOPS','BUG','DEBUG_STR','BUGS');

    The following ERRORS:
    BEGIN CHG_COLNM('BDOPS','BUG','DEBUG_STR','BUGS'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00905: object BDOPS.CHG_COLNM is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Although the procedure works fine when I assign the owner SYS:
    exec sys.CHG_COLNM('BDOPS','BUG','DEBUG_STR','BUGS');
    PL/SQL procedure successfully completed.

    Why can't I call this procedure without indicated the schema owner? It has a Public Grant and Synonym.

    ----------------------------------
    CREATE OR REPLACE
    procedure SYS.chg_colnm(
    user in varchar2, -- name of the schema.
    table_name in varchar2, -- name of the table.
    old_name in varchar2, -- name of the column to be renamed.
    new_name in varchar2 -- new name of the column.
    ) as
    id number;
    col_id number;
    cursor_name1 INTEGER;
    cursor_name2 INTEGER;
    ret1 INTEGER;
    ret2 INTEGER;
    begin
    select object_id into id from dba_objects where
    object_name=UPPER(table_name)
    and owner=UPPER(user) and object_type='TABLE';
    select col# into col_id from col$ where obj#=id and
    name=UPPER(old_name);
    dbms_output.put_line(col_id);
    update col$ set name=UPPER(new_name)
    where obj#=id and col#=col_id;
    commit;
    cursor_name1 := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name1, 'ALTER SYSTEM FLUSH
    SHARED_POOL',DBMS_SQL.native);
    ret1 := DBMS_SQL.EXECUTE(cursor_name1);
    DBMS_SQL.CLOSE_CURSOR(cursor_name1);
    cursor_name2:= DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name2, 'ALTER SYSTEM
    CHECKPOINT',DBMS_SQL.native);
    ret2:= DBMS_SQL.EXECUTE(cursor_name2);
    DBMS_SQL.CLOSE_CURSOR(cursor_name2);
    end;
    /

    -- Grants for CHG_COLNM

    GRANT EXECUTE ON sys.chg_colnm TO public
    /

    -- Synonym CHG_COLNM

    CREATE PUBLIC SYNONYM chg_colnm
    FOR sys.chg_colnm
    /

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Obviously you have a procedure named CHG_COLNM also in schema BDOPS, not only in schema SYS. You can verify this by:
    SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'CHG_COLNM';

    And that procedure in schema BDOBS is obviously invalid. Plus you abviously are calling this procedure logged as user BDOBS or your public synonym is pointing to that procedure in schema BDOBS or you have a private synonym pointing there.
    Last edited by jmodic; 03-04-2003 at 07:23 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width