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 "[EMAIL PROTECTED]" on questions to the list.
-----Original Message-----
Sent: Wednesday, 18 April 2001 19:14
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
