Access SYS procedure from dba user

[thomgreen]

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
/

 

[SamBones]

You're asking this in the wrong forum. Please try one of the Oracle forums.

 

[thomgreen]

Oops... My bad.