Re: DBMS_SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 23 Oct 1998 14:15:44 GMT
Message-ID: <36318f80.6553974@192.86.155.100>

A copy of this was sent to "miguel" <jmiguel_at_cet.pt> (if that email address didn't require changing) On 23 Oct 1998 11:40:42 GMT, you wrote:

>Can someone help me?
>Oracle 7.3.3 on NT 4.0
>
>When user SCOTT execute:
>DECLARE
> CURSOR_NAME INTEGER;
> RET INTEGER;
>BEGIN
> CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(CURSOR_NAME,'CREATE TABLE BB(A NUMBER(3))',DBMS_SQL.V7);
> RET := DBMS_SQL.EXECUTE(CURSOR_NAME);
> DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
>END;
>/
>everything is OK
>but when use a procedure like:
>CREATE OR REPLACE PROCEDURE TEST(STRING IN VARCHAR2) AS
> CURSOR_NAME INTEGER;
> RET INTEGER;
>BEGIN
> CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(CURSOR_NAME,STRING,DBMS_SQL.V7);
> RET := DBMS_SQL.EXECUTE(CURSOR_NAME);
> DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
>END;
>/
>and try to execute:
>EXECUTE TESTE ('CREATE TABLE CC(A NUMBER(2))');
>an error ocurr:
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "SCOTT.TEST", line 6
>ORA-06512: at line 1
>
>Why????

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant create table to <OWNER>;

>
>Thnks
>Miguel
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Oct 23 1998 - 09:15:44 CDT