How to get a record using EXECUTE IMMEDIATE?
14,449
It all works fine for me (Oracle 10G), one I correct the syntax error in your function definition (the unwanted semi-colon on the first line):
SQL> create type my_rec is object (id integer, name varchar2(30))
2 /
Type created.
SQL> create FUNCTION func1 (par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec
2 is
3 l_rec my_rec := my_rec (1, 'x');
4 begin
5 return l_rec;
6 end;
7 /
Function created.
SQL> CREATE OR REPLACE
2 FUNCTION get_result (func_name IN VARCHAR2, par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec
3 IS
4 rec1 my_rec;
5 BEGIN
6 EXECUTE IMMEDIATE 'SELECT ' || func_name || '(:par1, :par2) FROM DUAL'
7 INTO rec1
8 USING IN par1, IN par2;
9 RETURN rec1;
10 END;
11 /
Function created.
SQL> select get_result ('func1',1,2) from dual;
GET_RESULT('FUNC1',1,2)(ID, NAME)
-------------------------------------------------
MY_REC(1, 'x')
Author by
engro
Updated on June 04, 2022Comments
-
engro almost 2 years
I have a bunch of functions with signatures like:
FUNCTION func1 (par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec;
and I have a function for retrieving data from this bunch of functions:
FUNCTION get_result (func_name IN VARCHAR2, par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec; IS rec1 my_rec; BEGIN EXECUTE IMMEDIATE 'SELECT ' || func_name || '(:par1, :par2) FROM DUAL' INTO rec1 USING IN par1, IN par2; RETURN rec1; END;
but this code fails with ORA-01007 'variable not in select list'.
How can I rewrite statement? -
engro almost 15 yearswhat did you mean: concatenate values of variables with dynamic-sql string instead of using 'USING' clause?
-
ATorras almost 15 yearsYes. I admit this is a security hole just in the case that par1 and par2 are not "safe" strings.
-
engro almost 15 yearsit works when I define my_rec as an object type, but doesn't work when my_rec defined as a record type in package. Do you have any idea why?
-
tuinstoel almost 15 years@negr-o, a record type in a package is only known within PL/SQL, you can't use it in SQL.