Skip to Main Content
  • Questions

  • How to pass multiple value to my parameter in SQL query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, RAVI.

Asked: May 14, 2020 - 7:05 pm UTC

Last updated: July 22, 2024 - 12:59 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a query ,I want to pass multiple values to pbd_prod_code is this possible ,as I am getting null v after passing 1001,1002 values to the parameter

SELECT field_key,
field_lable,
FROM json_table(
(SELECT PBD_BLOCK_INFO
FROM PIIM_PROD_BLOCK_DEFN_TEST
WHERE PBD_PROD_CODE in (:PBD_PROD_CODE)
AND PBD_BLOCK_ID = :PBD_BLOCK_ID ),
'$[*]' COLUMNS(field_lable path '$.fieldlabel',
field_key path '$.fieldkeyid'));

and Connor said...

Here's a video walk through on the technique


Rating

  (3 ratings)

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

Comments

APEX

Alex, November 18, 2021 - 1:35 am UTC

What should the syntax be for a passing a multiple values from an APEX LOV into a Page report SQL statement
Connor McDonald
November 18, 2021 - 3:13 am UTC

A multi-select will return the selected values as a colon delimited string, eg

CONNOR:CHRIS:TOM

You can use the technique as per the video to convert that into rows

Faisal, May 11, 2022 - 1:29 pm UTC

Select is working but how we can use same logic to update the multiple records for the given list
Connor McDonald
May 16, 2022 - 2:35 am UTC

Not sure what you mean. Can you give us a full test case with some notes on what you want to achieve?

Oracle BI Publisher

sushanth, July 18, 2024 - 3:25 pm UTC

I am getting an error message "invalid SQL" when trying to pass multiple values through bind variable, but I am successful by passing one value using the below code. Please help me

REGEXP_LIKE(uxhmpcContaminant1MultiSele, '(^|,)(' || REPLACE(:uuu_p_contaminant, ',', '|') || ')(,|$)')
Chris Saxon
July 22, 2024 - 12:59 pm UTC

What do you mean pass multiple values through bind variable? You can only bind one value to each variable.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.