Re: Help: dynamic sql

After connecting as SYS try to use:

  GRANT EXECUTE ON DBMS_SQL TO <another_user>;

I hope it will help.

badstreetboy_at_my-dejanews.com wrote:
>
> I was trying to use dynamic sql package 'dbms_sql' to execute DDL statements
> within PL/SQL. After I run the 2 startup packages(dbmssql.sql, prvtsql.plb)
> as user 'SYS', I am only able to run the dbms_sql package as 'SYS'. When I
> connect to another user to use dbms_sql package, I got an error.
>
> SQL> execute exec('create table t1(f1 integer)');
>
> ERROR at line 1:
> ORA-04068: existing state of packages has been discarded
> ORA-04063: package body "ANOTHER_USER.DBMS_SQL" has errors
> ORA-06508: PL/SQL: could not find program unit being called
> ORA-06512: at "ANOTHER_USER.EXEC", line 5
> ORA-06512: at line 1
>
> Please help. Thanks!!
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

-- 
Valery A. Sorokin
ProSoft, Russia, Moscow, Information Systems Division
Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640
E-mail: vsorokin_at_dd.ru OR vsorokin_at_prosoftmpc.msk.su
http://www.dd.ru