Home » SQL & PL/SQL » SQL & PL/SQL » Getting the function value with arguments by using execute immediate (Oracle 9i)
Getting the function value with arguments by using execute immediate [message #347379] Thu, 11 September 2008 07:38 Go to next message
rtjk
Messages: 33
Registered: February 2005
Member
Hi
I have a problem with below procedure.

Its not taking the runtime value;
declare
value VARCHAR2(200);
1st_arg NUMBER;
sel_val varchar2(4000);
v_sql varchar2(4000);
2nd_arg varchar2(4000);

BEGIn
1st_arg :=4016;
2nd_arg := 'MY_NAME';

SELECT resolve_func_name || '(' || REPLACE(resolve_func_arg_list, ';' ,',') || ')' into sel_val
from Table1 where id =1870;

Select gives ==> FUNCTION_3(1st_arg,2nd_arg)


v_sql := ' SELECT ' || sel_val || ' ' || 'FROM DUAL';

EXECUTE IMMEDIATE v_sql into value;
DBMS_OUTPUT.PUT_LINE( value);
END ;

When i execute this i am getting error like

ORA-00904: "2nd_arg": invalid identifier
ORA-06512: at line 19

With other record

SELECT resolve_func_name || '(' || REPLACE(resolve_func_arg_list, ';' ,',') || ')' into sel_val
from Table1 where id =1871;

Select gives ==> FUNCTION_2(1st_arg)

ORA-00904: "1st_arg": invalid identifier
ORA-06512: at line 19

Please help me with this.



Re: Getting the function value with arguments by using execute immediate [message #347387 is a reply to message #347379] Thu, 11 September 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Getting the function value with arguments by using execute immediate [message #347396 is a reply to message #347379] Thu, 11 September 2008 08:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Execute Immediate had no idea what 1st_arg and 2nd_ard are meant to be.
You will need to pass them into the execute immediate statement as bind variables before you can use them that way
Previous Topic: Column Formatting (merged)
Next Topic: oracle
Goto Forum:
  


Current Time: Wed Dec 07 18:23:57 CST 2016

Total time taken to generate the page: 0.14065 seconds