Hello,
I'm building a function that always receives 3 input values. These incoming values are used as bind variables in assembling a SQL query string that is later executed using an EXECUTE IMMEDIATE statement.
The SQL query string contains a SELECT INTO statement that returns a single record with 3 columns as its output. How do I use the multiple bind variables to supply them into the SQL query string and how do I capture the 3 column values returned from executing the sql query string using EXECUTE IMMEDIATE?
This is what I have tried but doesn't work:-
CREATE OR REPLACE FUNCTION fn_test(p1 IN NUMBER, p2 IN DATE, p3 IN VARCHAR2)
RETURN VARCHAR2
IS
s_query_stmt VARCHAR2(1000 CHAR);
r1 VARCHAR2(100 CHAR);
r2 VARCHAR2(100 CHAR);
r3 VARCHAR2(100 CHAR);
BEGIN
s_query_stmt := 'SELECT
c1, c2, c3
INTO r1, r2, r3
FROM t AS t1
LEFT JOIN t AS t2 ON t1.c7 = t2.c8 --Note: A self-join happening here!
WHERE t2.c8 = :x --Note: p1 value should go here!
AND t2.c9 = :y --Note: p2 value should go here!
AND t1.c1 = ( SELECT c_other FROM tb_other WHERE c_other = :x AND b_other = :y )'; --Note: p1 value should go into :x and p2 into :y bind variables again in that order!
EXECUTE IMMEDIATE s_query_stmt INTO r1, r2, r3 USING p1, p2, p1, p2;
RETURN
CASE LOWER(p3)
WHEN 'g' THEN r1
WHEN 'u' THEN r3
ELSE r2
END;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
All the 3 columns returned by the SELECT statement (c1, c2, c3) are indeed of type VARCHAR2 that need to be dumped into r1, r2, r3 respectively. However, the final return result of this function would depend on the CASE statement evaluation and only one of the string values would be returned.
Basically, the output of running this function should only show 1 row with 1 column value.
Appreciate any help.
The 'into' goes outside the string, eg
SQL> create table t ( c1 int, c2 int, c3 int, c8 int, c9 date );
Table created.
SQL> create table t2 ( cx int, cy int, cz date );
Table created.
SQL>
SQL> insert into t values (1,1,1,1,date '2000-01-01');
1 row created.
SQL> insert into t2 values (1,1,date '2000-01-01');
1 row created.
SQL>
SQL> CREATE OR REPLACE FUNCTION fn_test(p1 IN NUMBER, p2 IN DATE, p3 IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4
5 s_query_stmt VARCHAR2(1000 CHAR);
6 r1 VARCHAR2(100 CHAR);
7 r2 VARCHAR2(100 CHAR);
8 r3 VARCHAR2(100 CHAR);
9
10 BEGIN
11
12 s_query_stmt := 'SELECT
13 c1, c2, c3
14 FROM t
15 WHERE c8 = :x --Note: p1 value should go here!
16 AND c9 = :y --Note: p2 value should go here!
17 AND c1 = ( SELECT cx FROM t2 WHERE cy = :x AND cz = :y )'; --Note: p1 value should go into :x and p2 into :y bind variables again in that order!
18
19 EXECUTE IMMEDIATE s_query_stmt INTO r1, r2, r3 USING p1, p2, p1, p2;
20 return 'x';
21
22 END;
23 /
Function created.
SQL>
SQL> select fn_test(1,date '2000-01-01',1) from dual;
FN_TEST(1,DATE'2000-01-01',1)
----------------------------------------------------------------------------------------------------------------------------------
x
And you'll notice I removed the
EXCEPTION
WHEN OTHERS THEN
RAISE;
because that's just bad news :-)