CODEWITHSUNDEEP
oracle-databaseoracle-apex
kiric8494
kiric8494

Reputation: 205

How to pass my input from text area to my Interactive query

I have a APEX application where I want to pass the text area input to my interactive grid report query

Textarea

ABC

XYZ

HSJKS

HSSK

JK

HSJK

Query written on interactive report

  select * from emp where food_id in (:P2_TEXTAREA);

Note : I have added a change event on :P2_TEXTAREA -> If any change happens the interactive report get refresh with data

The text entered in TEXTAREA is in vertical format and their is no space after every row. As I posted the question to show that I have entered newline

Upvotes: 0

Views: 659

Answers (2)

MT0
MT0

Reputation: 168796

You cannot pass multiple values using a single bind variable. What you need to do is use a sub-string match:

SELECT *
FROM   emp
WHERE  CHR(10) || REPLACE(:P2_TEXTAREA, CHR(13)) || CHR(10)
         LIKE '%' || CHR(10) || food_id || CHR(10) || '%';

Note: It is unclear if in your input the new line is CR/LF (windows) or just LF (unix/linux) so REPLACE is used to strip CR characters from the input to normalise it to just a LF character.

db<>fiddle here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143133

It won't work that way; you'll have to split values into rows and then use the result in select statement.

I don't have your tables nor data so I'll use Scott's sample schema.

This table "simulates" page item:

SQL> create table test (p2_textarea varchar2(200));

Table created.

Sample values, each of them in a new row:

SQL> insert into test (p2_textarea) values(
  2  '10
  3  30');

1 row created.

Here's how:

SQL> select *
  2  from dept
  3  where deptno in (select regexp_substr(p2_textarea, '[^' ||chr(10)||']+', 1, level)
  4                   from test
  5                   connect by level <= regexp_count(p2_textarea, chr(10)) + 1
  6                  );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO

SQL>

In your case, that would be

select * 
from emp 
where food_id in (select regexp_substr(:P2_TEXTAREA, '[^' ||chr(10)||']+', 1, level)
                  from test
                  connect by level <= regexp_count(:P2_TEXTAREA, chr(10)) + 1
                 );

Upvotes: 1

Related Questions