Re: Please HELP !!!(It makes me crazy)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/15
Message-ID: <34cd1fcd.215807725@inet16>#1/1

On Wed, 14 Jan 1998 21:50:06 GMT, stiger_at_chat.ru (Mikhail) wrote:

[snip]

>>>Hello, everyone,
>>>I try create a storage procedure that produces a table_name and
>>>creates a table with this table_name in run time.
>>>I use
>>>…
>>>table_name VARCHAR2(6);
>>>command_run VARCHAR2(200);
>>>BEGIN
>>>…
>>>cursor_name:= DBMS_SQL.OPEN_CURSOR;
>>>command_run:=’CREATE TABLE ‘ || table_name || ‘ AS SELECT * FROM emp’;
>>>DBMS_SQL.PARSE(cursor_name, command_run, DBMS_SQL.V7);
>>>row_processed:= DBMS_SQL.EXECUTE(cursor_name);
>>>DBMS_SQL.CLOSE_CURSOR(cursor_name);
>>>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>>It does not work.
>>>But, if I use
>>>command_run=’DROP TABLE ‘|| table_name ||;

[snip]

>>Is it reporting an error message, could you let us know. It is quite possible
>>that it is permissions related, since code executed in stored procedures relies
>>on specifically granted privileges (e.g. CREATE TABLE) and not those available
>>through a role (e.g. DBA)
>
>Thank you for your interest in this problem.
>Compiler doesn’t produce any errors and I have the permission for
>CREATE TABLE.
How about the runtime error message? In the code below you should put a RAISE in the exception block to allow the error to propagate back up to sql*plus so we can see that this is probably an Insufficient privelege error.

Are you sure you have create table granted to you? I bet you have the ability to create a table via a ROLE and roles are never enabled stored procedures. to see if you can create a table in a stored procedure, do this in sqlplus:

SQL> set role none;

Role set.

SQL> create table foo ( x int );
create table foo ( x int )
*
ERROR at line 1:
ORA-01031: insufficient privileges

---

If that happens, then

SQL> grant create table to USERNAME;

and try it again. Once you can create table with no roles enabled, you can create a procedure that creates tables.

[snip]

>BEGIN
>cursor_name:= DBMS_SQL.OPEN_CURSOR;
>SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'MON_YY') INTO table_name FROM
>dual;
>command_run:='DROP TABLE ' || table_name;
>DBMS_OUTPUT.PUT_LINE('Table name is: ' || table_name);
>DBMS_SQL.PARSE(cursor_name, command_run, DBMS_SQL.V7);
>row_processed:= DBMS_SQL.EXECUTE(cursor_name);
>DBMS_SQL.CLOSE_CURSOR(cursor_name);
>EXCEPTION
> WHEN OTHERS THEN
> DBMS_SQL.CLOSE_CURSOR(cursor_name);

• put a raise here to allow the error to propagate to sqlplus
• so you can see what the error is, or just comment out
• the exception block for now raise;

>END;
>

[snip]  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Thu Jan 15 1998 - 00:00:00 CST