errors in pl/sql function [message #40716] |
Fri, 01 November 2002 18:27 |
pras
Messages: 57 Registered: June 2002
|
Member |
|
|
Hi,
Following is the procedure and I am having errors as shown below. Any help?
Thanks!
Prasanna
1 create or replace function createpriv
2 (v_username varchar2,priv1 varchar2)
3 return varchar2 as
4 username varchar2(200):=decrypt1(v_username);
5 priv varchar2(200):=decrypt1(priv1);
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 begin
8 execute immediate 'grant'||priv||'to'||username;
9 return username ;
10 return priv;
11* end;
SQL> /
Function created.
SQL> select createpriv('148148148','127139138138129127144') from dual;
select createpriv('148148148','127139138138129127144') from dual
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "PRASANNA.CREATEPRIV", line 8
ORA-06512: at line 1
|
|
|
Re: errors in pl/sql function [message #40721 is a reply to message #40716] |
Sat, 02 November 2002 18:06 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You need some extra spaces in the dynamic statement. And there is no real need to declare a variable for the decrypted privilege:
execute immediate 'grant ' || decrypt1(priv1) || ' to ' || username;
|
|
|
|