I have an issue with SQL Macros.
I was hoping to pass a list of , separated values to the Macro and use a “where in” clause
However this would seem to be not possible
I have put together an example here using Employee Table
Right at the end when I expand the SQL you will see that “where in” clause has be replaced with “=”, Not what I was expecting
So if only one value in parameter list then works, more than one the macro fails.
Any ideas ? Suggestion ?
SQL> set echo on
SQL> set SERVEROUTPUT ON
SQL>
SQL> -- Database Version
SQL> -- -----------------------------------------------------------------------------
SQL> select * from v$version ;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
Version 19.13.0.0.0
SQL>
SQL> -- Returns 4 rows from Employee Table using a , sepearted list
SQL> -- -----------------------------------------------------------------------------
SQL> SELECT employee_id, first_name, last_name, email FROM u203379.employees WHERE employee_id IN ( 100, 101, 102, 103 );
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
100 Steven King SKING
101 Neena Kochhar NKOCHHAR
102 Lex De Haan LDEHAAN
103 Alexander Hunold AHUNOLD
SQL>
SQL> -- Create SQL Macro Function - Also to take a , sep list
SQL> -- -----------------------------------------------------------------------------
SQL> CREATE OR REPLACE FUNCTION employee_func (
2 p_list_of_ids varchar2
3 ) RETURN VARCHAR2 SQL_MACRO IS
4 BEGIN
5 RETURN q'{
6 SELECT employee_id, first_name, last_name, email from u203379.employees where employee_id in (p_list_of_ids)
7 }';
8 END employee_func;
9 /
Function EMPLOYEE_FUNC compiled
SQL>
SQL> -- Test with one list value
SQL> -- -----------------------------------------------------------------------------
SQL> select * from fahrbahnproj.employee_func('100');
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
100 Steven King SKING
SQL>
SQL> -- Test with two list value
SQL> -- -----------------------------------------------------------------------------
SQL> select * from fahrbahnproj.employee_func('100, 101');
Error starting at line : 31 in command -
select * from fahrbahnproj.employee_func('100, 101')
Error report -
ORA-01722: invalid number
SQL>
SQL>
SQL> DECLARE
2 l_clob CLOB;
3 BEGIN
4 dbms_utility.expand_sql_text(
5 input_sql_text => 'select * from fahrbahnproj.employee_func(''100, 101'')',
6 output_sql_text => l_clob
7 );
8 dbms_output.put_line('/*');
9 dbms_output.put_line(lower(l_clob));
10 dbms_output.put_line('*/');
11 END;
12 /
/*
select "a1"."employee_id" "employee_id","a1"."first_name" "first_name","a1"."last_name" "last_name","a1"."email" "email" from (select "a2"."employee_id" "employee_id","a2"."first_name" "first_name","a2"."last_name" "last_name","a2"."email" "email" from (select "a3"."employee_id" "employee_id","a3"."first_name" "first_name","a3"."last_name" "last_name","a3"."email" "email" from "u203379"."employees" "a3" where "a3"."employee_id"='100, 101') "a2") "a1"
*/
PL/SQL procedure successfully completed.