Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with DBMS_SQL

RE: Problem with DBMS_SQL

From: Bond Mike A Contr OC-ALC/TILC <Mike.Bond_at_tinker.af.mil>
Date: Fri, 31 Aug 2001 04:59:55 -0700
Message-ID: <F001.0037EDEF.20010831050034@fatcity.com>

Hi,

I found this in metalink. I was able to duplicate your problem and even though I have DBA role, I could not execut ethe proc until i granted myself 'create table' directly.

"If a user is trying to create a table using DBMS_SQL, the user must have create table privilege granted directly and not via a role. "

Mike

-----Original Message-----
Sent: Friday, August 31, 2001 7:27 AM
To: Multiple recipients of list ORACLE-L

Daft question but do you have the priviliges to create tables? Silly I know...

"hit any user to continue"


Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com

-----Original Message-----
Sent: 31 August 2001 13:01
To: Multiple recipients of list ORACLE-L

Dear All

Every time when I try to create table with the DBMS_SQL I get error


ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "DL.SP_CR", line 6
ORA-06512: at line 2
-----------

The procedure code is trivial and I call it as

begin

   sp_cr('CREATE TABLE XTTBL(a NUMBER)'); end;
/

CREATE OR REPLACE procedure SP_CR(pvi_str VARCHAR2) AS   cid INTEGER;
  err_code INTEGER;
begin
  cid := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cid, pvi_str, DBMS_SQL.native);   err_code:=DBMS_SQL.EXECUTE(cid);
  DBMS_SQL.CLOSE_CURSOR(cid);
 END;
/

What might be wrong here?

Yes, I can try native SQL (I'm running 8i) but code has to be portable to the 8.05

Cheers,
Dimitri
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Dmitriy M. Labutin
  INET: Dmitriy_Labutin_at_ep.minsk.by

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: Thomas, Kevin
  INET: Kevin.Thomas_at_calanais.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: Bond Mike A Contr OC-ALC/TILC
  INET: Mike.Bond_at_tinker.af.mil
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 Fri Aug 31 2001 - 06:59:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US