Hi Bhat,
DBA is a role under Oracle7 (although is was a system privilege under version
6). Roles are not effective in stored procedures. Invoker's rights changes that
somewhat in 8i, but for now you have to grant the system privilege directly to
the procedure owner. You cannot rely on privileges obtained via roles.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Wednesday, 18 April 2001 21:16
To: Multiple recipients of list ORACLE-L
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:[EMAIL PROTECTED]]
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 "[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
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).
