| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with DBMS_SQL
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: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). -- 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 - 06:54:33 CDT
 |
 |