How to pass multiple parameters to a bind variable in a query with = sign ?

gg

Hi Everyone,

The user will select multiple pick value from screen like 100, 101, 102 etc

and those values need to be passed to the below query and generate the

o/p for further processing.

Can some one please tell how can i pass multiple values to a bind parameter in a query having a equal to sign?

SELECT tab1.PICKLIST_KEY icrggq_0, NULL icrggq_1, 0 icrggq_2

 FROM tab1

 WHERE dc_code = 942

  AND tab1.pick_value = :p_pick_value;

There are hundereds of queries like this and we will not be able to modify all of them manually.

Is it possible to be done by plsql procedure or function ?

We are receving the multiple values through front end Oracle Forms screen.

Thanks

Gautam

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16824801#Comment_16824801

Or the string get's passed as e.g. ',100,101,102,' and then the where clause could be...

WHERE :p_pick_value like '%,'||dc_code||',%'

but that does have it's limitations, such as lack of index usage, or limits on the size of the string (if many values are being passed).

Or, the input string needs to be split with SQL to separate values (various methods to do that, including a connect by statement) before joining to the results.

SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from emp
  3  where empno in (
  4    with t as (select '&input_string' as txt from dual)
  5    select REGEXP_SUBSTR (txt, '[^,]+', 1, level)
  6    from t
  7    connect by level <= length(regexp_replace(txt,'[^,]*'))+1
  8*   )
SQL> /
Enter value for input_string: 7369,7844,7788
old   4:   with t as (select '&input_string' as txt from dual)
new   4:   with t as (select '7369,7844,7788' as txt from dual)


     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20

Whichever way though, the SQL's would need changing to cater for it... no real way of getting the "=" to match multiple values.

Frank Kulash

Hi, @gg

Can some one please tell how can i pass multiple values to a bind parameter in a query having a equal to sign?

Sorry, a bind variable can only have one value at a time. That value can be a string, such as the 13-character string '100, 101, 102', but if you use if you want to use it in a SQL statement like

WHERE  dc_code IN (:p_pick_value)

it won't check if dc_code is any of the three numbers 100, 101 and 102; it will check if dc_code is the 13-character string. To treat that string as a comma-delimited list of numbers requires dynamic SQL.

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16824801#Comment_16824801

Or the string get's passed as e.g. ',100,101,102,' and then the where clause could be...

WHERE :p_pick_value like '%,'||dc_code||',%'

but that does have it's limitations, such as lack of index usage, or limits on the size of the string (if many values are being passed).

Or, the input string needs to be split with SQL to separate values (various methods to do that, including a connect by statement) before joining to the results.

SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from emp
  3  where empno in (
  4    with t as (select '&input_string' as txt from dual)
  5    select REGEXP_SUBSTR (txt, '[^,]+', 1, level)
  6    from t
  7    connect by level <= length(regexp_replace(txt,'[^,]*'))+1
  8*   )
SQL> /
Enter value for input_string: 7369,7844,7788
old   4:   with t as (select '&input_string' as txt from dual)
new   4:   with t as (select '7369,7844,7788' as txt from dual)


     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20

Whichever way though, the SQL's would need changing to cater for it... no real way of getting the "=" to match multiple values.

gg

Thank you so much Frank and Blushadow.

User_H3J7U

SQL> alter session set sql_translation_profile=stp_test1;

Session altered.

SQL> var p_pick_value varchar2(1000 char)
SQL> exec :p_pick_value := '11,22,33'

PL/SQL procedure successfully completed.

SQL> with t(n) as (select level from dual connect by level<=30)
  2  select * from t where n = :p_pick_value;

         N
----------
        11
        22

create or replace package stp_test1 as
procedure translate_sql(sql_text       in clob,
                       translated_text out clob);
end stp_test1;
/

create or replace package body stp_test1 as
procedure translate_sql(sql_text       in clob,
                       translated_text out clob) is
  rex varchar2(512 byte) := '((([_#$[:alnum:]]+|"[^"]+")\.){0,2}([_#$[:alnum:]]+|"[^"]+"))\s*=\s*:p_pick_value(\s|$)';
begin
  if regexp_like(sql_text, rex, 'i') then
    translated_text := regexp_replace(sql_text, rex,
                                    '\1 in (select "." from xmltable(:p_pick_value columns "." number))',1,1,'i');
  end if;
end translate_sql;
end stp_test1;
/

begin
 dbms_sql_translator.create_profile('STP_TEST1');
 dbms_sql_translator.set_attribute ('STP_TEST1', 'FOREIGN_SQL_SYNTAX', 'FALSE');
 dbms_sql_translator.set_attribute ('STP_TEST1', 'RAISE_TRANSLATION_ERROR', 'TRUE');
 dbms_sql_translator.set_attribute ('STP_TEST1', 'TRANSLATOR', 'STP_TEST1');
end;
/

--exec dbms_sql_translator.drop_profile('STP_TEST1')
--drop package stp_test1;