Skip to Main Content
  • Questions
  • EXECUTE IMMEDIATE with multiple bind variables and SELECT INTO returning a single record but multiple columns

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sam.

Asked: June 01, 2017 - 1:39 am UTC

Last updated: June 21, 2021 - 1:35 pm UTC

Version: 11.2.0.4 Enterprise

Viewed 10K+ times! This question is

You Asked

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.

and Connor said...

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 :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-22992: cannot use LOB locators selected from remote tables

Tejas Abhimanyu Pednekar, June 19, 2021 - 12:31 pm UTC

Getting error : ORA-22992: cannot use LOB locators selected from remote tables

dtm.ndsdata is the blob column


query :

declare
cursor c_level_list is
select distinct level_id from psf_dtm_config;
r_level_list c_level_list%rowtype;

cdc_table_name varchar(256);
begin
open c_level_list;
loop
fetch c_level_list into r_level_list;
exit when c_level_list%notfound;

cdc_table_name := 'ndsa_dtm_129_' || r_level_list.level_id;

execute immediate
'insert into psf_amd_dtm_height
(
id,
dtmusagetype,
versionid,
ndsdata,
tilepatternid
)
select
cf_psf_tools.get_packed_tile_id(dtm.id, ' || r_level_list.level_id || ') as id,
0 as dtmusagetype,
dtm.versionid,
dtm.ndsdata,
null as tilepatternid
from ' || cdc_table_name || ' dtm
join psf_dtm_clipped_tiles t on dtm.id = t.tile_id and t.level_id = ' || r_level_list.level_id;
end loop;
commit;
end;

Chris Saxon
June 21, 2021 - 1:35 pm UTC

I'm not sure what's going on here; insert into ... select ..., clob_data from t@dblink is allowed.

Please post a complete example showing this problem.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library