-
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
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|