Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Marco Function - Passing a list for "WHERE IN" Clause - Bug or DESIGN

user552932Mar 1 2022 — edited Mar 1 2022

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.

Comments

User_H3J7U

Bug or DESIGN
By design the query is returned at hard parse time, the parameter values is accessed at runtime.
Any ideas ? Suggestion ?
Split the string at runtime.

user552932

That does not fully explain the error - WHERE IN is a standard SQL clause ? - and I can not split the list of ids that I am looking up. Needs to be clearer...

User_H3J7U

That does not fully explain the error - WHERE IN is a standard SQL clause ?
Parameter acts like a bind variable. fahrbahnproj.employee_func('100, 101') -> ... employee_id in (:p_list_of_ids) -> ... employee_id in ('100, 101')  -> ORA-01722: invalid number
I can not split the list of ids that I am looking up.

with function sm(numlist varchar2) return varchar2 sql_macro as
  nl varchar2(32767 byte);
begin
  return 'select id from json_table(''[''||sm.numlist||'']'', ''$[*]'' columns (id number path ''$''))';
end;
select * from sm('11,22,33')
/

        ID
----------
        11
        22
        33

To split string you can use a table/varray function instead of sql_macro.

Solomon Yakobson 
CREATE OR REPLACE
 FUNCTION EMPLOYEE_FUNC(
                        P_LIST_OF_IDS VARCHAR2
                       )
   RETURN VARCHAR2
   SQL_MACRO
   IS
   BEGIN
       RETURN Q'{
                 SELECT  EMPLOYEE_ID,
                         FIRST_NAME,
                         LAST_NAME,
                         EMAIL
                   FROM  EMPLOYEES
                   WHERE EMPLOYEE_ID IN (
                                         SELECT  TO_NUMBER(REGEXP_SUBSTR(P_LIST_OF_IDS,'\d+',1,LEVEL))
                                           FROM  DUAL
                                           CONNECT BY LEVEL <= REGEXP_COUNT(P_LIST_OF_IDS,',') + 1
                                        )
                }';
END EMPLOYEE_FUNC;
/

Function created.

SQL> SELECT  *
  2    FROM  EMPLOYEE_FUNC('100')
  3  /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
        100 Steven               King                      SKING

SQL> SELECT  *
  2    FROM  EMPLOYEE_FUNC('100,101')
  3  /


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
        101 Neena                Kochhar                   NKOCHHAR
        100 Steven               King                      SKING

SQL>

SY.

1 person found this helpful
Frank Kulash

Hi, user551641
WHERE IN is a standard SQL clause ? 
That's right, IN is a standard SQL operator. When the right operand is a single value, as in
employee_id IN (101)
then it's equivalent to
employee_id = 101
'101,101' is a single VARCHAR2 value, not two NUMBER values.
I can not split the list of ids that I am looking up.
If you can't split the string (or, even better, collect the values in First Normal Form, e.g. a global temporary table) in the first place, then you can do something like this:

SELECT   employee_id, first_name, last_name, email
FROM	 hr_employees
WHERE	 INSTR  ( ',' || :p_list_of_ids        || ','
	 	, ',' || TO_CHAR (employee_id) || ','
		) > 0
;

or you could split the sting inside the function.

BluShadow

the parameter you pass in, is a single string. Just because your string contains commas doesn't mean that Oracle will magically know that it should be a list of values, and it's not just substituted into the query string as if it's a dynamically built SQL. As user_<whatever> says, it acts like a bind variable, so Oracle replaces the value in the string with the single value you pass it, as a whole.... it's expecting a set of numbers and you're giving it a string that isn't a number... simple as.

1 - 6

Post Details

Added on Mar 1 2022
#function, #sql, #sqldeveloper
6 comments
1,415 views
1 mention