Problem with DBMS_SQL

From: <LBhat_at_LEVI.com>
Date: Wed, 18 Apr 2001 02:38:32 -0700
Message-ID: <F001.002EBA08.20010418021109@fatcity.com>

Hi Gurus,

I am experincing a problem with a procedure containing DBMS_SQL to create a table.

On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas.

HP-UX : Oracle 7.3.4.4.1

Thanks.
- Bhat

Here is the procedure

---

create or replace PROCEDURE create_table_mbn015 IS

   dyn_sql LONG;

   cid     INTEGER;
   a       integer;
   b       varchar2(100);

   abcd integer;
BEGIN
   cid := DBMS_SQL.OPEN_CURSOR;
   dyn_sql := 'CREATE TABLE mbn015

      STORAGE (INITIAL 5M NEXT 5M)
      TABLESPACE MUGDBDATA1
      AS(  SELECT DISTINCT         p.item,         p.loc,
p.cppprodmethod,         c.loadoffsetdur,         p.scheddate,
       (p.scheddate - c.loadoffsetdur/1440) calcdate      FROM
stsc.planorder p,            stsc.cppprodmethodstep c
    WHERE p.item = c.item     AND   p.loc = c.loc     AND   p.cppprodmethod
= c.cppprodmethod     AND   c.stepnum = 20)';

   DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7);    abcd := DBMS_SQL.EXECUTE(cid);
   dbms_output.put_line(abcd);
   DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
WHEN OTHERS THEN
   DBMS_SQL.CLOSE_CURSOR(cid);
   a := sqlcode;
   b := substr(sqlerrm,1,100);
   INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015;
/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: LBhat_at_LEVI.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Apr 18 2001 - 04:38:32 CDT