Re: Help: dynamic sql

A copy of this was sent to badstreetboy_at_my-dejanews.com (if that email address didn't require changing) On Tue, 20 Oct 1998 01:18:42 GMT, you 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
>

it looks like you ran the packages not as the user sys but rather as the user ANOTHER_USER. There should only be one copy of dbms_sql in the entire database. It must be installed when connected as INTERNAL or SYS. It is installed by default, you should not have needed to install it. It is granted execute to public by default, you should have just been able to use it.

Please run the following query when logged in as SYS:

select 'drop package ' || owner || '.dbms_sql;'   from dba_objects
 where owner not in ( 'SYS' )
   and object_name = 'DBMS_SQL'
   and object_type = 'PACKAGE'
/

If that returns any rows, run the drop statements it generated.

>Please help. Thanks!!
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.