Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate
Execute Immediate [message #216671] Mon, 29 January 2007 20:27 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I made a Function as follows

Create Or Replace Function func(name Varchar2) Return Number Is
qry Varchar2(100);
Begin
qry := 'Create Table '||name||'(join_date Date)';
Execute Immediate qry;
Return 1;
End;
SQL> /

Function created.

While Calling it said

SQL> Declare
2 num Number;
3 Begin
4 num := func('MyName');
5 End;
6 /
Declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "RITESH.FUNC", line 5
ORA-06512: at line 4

Why?Please tell me
Re: Execute Immediate [message #216673 is a reply to message #216671] Mon, 29 January 2007 20:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You probably don't have CREATE TABLE privilege. Try the CREATE TABLE statement at the SQL*Plus prompt rather than enclosing it in dynamic PL/SQL.

Ross Leishman
Re: Execute Immediate [message #216674 is a reply to message #216671] Mon, 29 January 2007 20:35 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do not apply within PL/SQL procedure/function
Re: Execute Immediate [message #216679 is a reply to message #216671] Mon, 29 January 2007 21:32 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
INVOKER RIGHTS is the key:

SQL> drop user test_user;

User dropped.

SQL> grant connect, resource to test_user identified by test_user;

Grant succeeded.

SQL> conn test_user/test_user
Connected.
SQL> Create Or Replace Function func(name Varchar2)
  2  Return Number Is
  3  qry Varchar2(100);
  4  Begin
  5  qry := 'Create Table '||name||'(join_date Date)';
  6  Execute Immediate qry;
  7  Return 1;
  8  End;
  9  /

Function created.

SQL> Declare
  2  num Number;
  3  Begin
  4  num := func('MyName');
  5  End;
  6  /
Declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_USER.FUNC", line 6
ORA-06512: at line 4


SQL> Create Or Replace Function func(name Varchar2)
  2  Return Number
  3  authid current_user Is
  4  qry Varchar2(100);
  5  Begin
  6  qry := 'Create Table '||name||'(join_date Date)';
  7  Execute Immediate qry;
  8  Return 1;
  9  End;
 10  /

Function created.

SQL> Declare
  2  num Number;
  3  Begin
  4  num := func('MyName');
  5  End;
  6  /

PL/SQL procedure successfully completed.

SQL> desc myname
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOIN_DATE                                          DATE

SQL>
Previous Topic: Date Generation
Next Topic: UPDATE A ROWS IN A TRIGGER FOR SAME TABLES
Goto Forum:
  


Current Time: Mon Dec 05 11:00:53 CST 2016

Total time taken to generate the page: 0.14508 seconds