RE: Problem with DBMS_SQL

From: <LBhat_at_LEVI.com>
Date: Wed, 18 Apr 2001 04:03:26 -0700
Message-ID: <F001.002EBA71.20010418031543@fatcity.com>

Hi Steve,

The account has DBA privilege and by using a direct DDL I am able to create the table.

Anything else I can check-up.

Thanks,
- Bhat

                -----Original Message-----
                From:   Steve Adams [mailto:steve.adams_at_ixora.com.au]
                Sent:   Wednesday, April 18, 2001 7:01 PM
                To:     Multiple recipients of list ORACLE-L
                Subject:        RE: Problem with DBMS_SQL

                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).

-- 
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 - 06:03:26 CDT