dbms_sqlparse

[mb]

I have this plsql block which will compile and run fine as an anonymous block but if I try to turn it into a procedure I get the error ORA-01031: insufficient privileges. Any ideas?

DECLARE
--
grant_text varchar2(255);
cid integer;
cursor c is
SELECT sequence_name FROM user_sequences;
BEGIN
FOR rec in c
LOOP
BEGIN
cid:= dbms_sql.open_cursor;
dbms_sql.parse(cid, 'CREATE public synonym ' || rec.sequence_name || ' for ' || rec.sequence_name, dbms_sql.native);
dbms_sql.close_cursor(cid);
dbms_output.put_line( 'synonym ' || rec.sequence_name || ' created ');
EXCEPTION
WHEN OTHERS THEN
DECLARE
error_code NUMBER := SQLCODE;
error_msg VARCHAR2 (300) := SQLERRM;
BEGIN
IF error_code = -00955 THEN
dbms_output.put_line( 'synonym ' || rec.sequence_name || ' already exists');
ELSE
dbms_output.put_line ( 'error: '|| SQLERRM);
END IF;
END;
END;
END LOOP;
END;
/

[TimHall]

Hi.

Procedure, Functions and Packages run with owner rights unless explicitly told not to. Check the package owner has privilege to access all objects referenced.

Privileges granted via a role are not valid for packages. The package owner must be granted them directly.

I'm not sure if this will help your problem directly but I'm too tired to try it out. Sorry

[chrisrlong]

Originally posted by TimHall
I'm not sure if this will help your problem directly but I'm too tired to try it out. Sorry

Yeah - he spent all his energy arguing with me (on another thread) which can be a very tiring (and trying) experience -just ask my wife

His answer should be right on the money, however.

- Chris