Re: Problem with dynamic create table inside procedure using DBMS_SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Jul 1998 00:35:30 GMT
Message-ID: <35c3bfbc.4215090@192.86.155.100>

A copy of this was sent to lcsantos_at_my-dejanews.com (if that email address didn't require changing) On Wed, 29 Jul 1998 14:29:29 GMT, you wrote:

> Hello All!
>
> I´m gotting the error ORA-01031: insufficient privileges when I try to
>execute a procedure that has a call to dbms_sql to create a table. The user
>running the procedure has create table privilege. If I try to create the table
>in SQL*plus, all works fine. If I use a anonymous PL/SQL block in SQL*Plus to
>create the table with DBMS_SQL, all works fine. The problem is just with the
>DBMS_SQL Create table INSIDE a procedure.
>
> The worst: If I try to do same with a DROP TABLE (the same table,
>created direct in SQL*Plus) with DBMS_SQL inside the procedure, all works
>fine!!!
>

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 dbms_sql won't be able to do it either.

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

> I have tested this in Oracle 7.3.3.5.0 for NT, 7.3.3.0.0 for Digital
>Unix and 7.3.3.4.0 for HP-UX. In all three I got the same error! Is this a
>generic bug in 7.3.3 release? Does anyone knows if in 7.3.4 is this fixed?
>
> Thanks for any help!
>
> Best regards
> Luis Santos
> lsantos_at_pobox.com, luiss_at_boavista.com.br
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

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 Wed Jul 29 1998 - 19:35:30 CDT