RE: Problem with DBMS

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 18 Apr 2001 03:25:07 -0700
Message-ID: <F001.002EBA46.20010418030033@fatcity.com>

Hi Bhat,

The CREATE TABLE privilege probably needs to be granted directly.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

PS. Please don't copy "questions_at_ixora.com.au" on questions to the list.

-----Original Message-----
Sent: Wednesday, 18 April 2001 19:14
To: ORACLE-L_at_fatcity.com
Cc: questions_at_ixora.com.au

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: Steve Adams
  INET: steve.adams_at_ixora.com.au

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 - 05:25:07 CDT

RE: Problem with DBMS_SQL