Thanks for the question, Sagar.

Asked: April 06, 2024 - 12:25 pm UTC

Last updated: April 22, 2024 - 1:07 am UTC

Version: ADBS

Viewed 1000+ times

You Asked

Hi Team

I have below scenario.

Step#1) User clicks to particular App UI screen.

Step#2) User selects multiple filters on UI - say filter1, filter2 which correspond to table columns.

Step#3) For each filter selected by user, he needs to enter data - say Mark (for filter1), Will (for filter2) based on which search will be performed on the respective filters (aka table columns).

Step#4) User inputs from above Steps#2, 3 are passed to PLSQL API which returns desired SQL result in paginated manner (pageSize: 50).

User inputs from Step#2, 3 will be dynamic.

I have tried to implement this using native dynamic SQL, but looks like I have hit an end road here. Able to use dynamic values in "USING" clause, but not able to return the data from SELECT statement with EXECUTE IMMEDIATE.

Shared above LiveSQL link which has re-producible test case.

If I comment line "BULK COLLECT INTO l_arId, l_arName, l_arType" in the procedure, the block executes successfully.
But I need the result set from SELECT statement in procedure as output.

Looking for some advise here.

Thanks a bunch!

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/bjydr0sjvqfr5kuyijkbregjt

and Connor said...

Thanks for the test case.

Ultimately, you generated the following PLSQL block

begin
  EXECUTE IMMEDIATE 
      'SELECT  id,
               name,
               type
      FROM test_dy
      WHERE (LOWER(name) LIKE LOWER(:3)   OR LOWER(name) LIKE LOWER(:5) )
      ORDER BY name asc
      OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY'
      BULK COLLECT INTO l_arId, l_arName, l_arType
      USING '%Mark%', 'Will%';
end;

Notice that in the scope of that block, it has no idea what l_arId, l_arName, l_arType are. Even you *generated* this block inside the parent block, it doesn't mean it *knows* that what the parent block is.

Thus you need to let this generated block know what to do with those return values


SQL>
SQL> CREATE OR REPLACE TYPE typ_arrChar IS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL>
SQL> CREATE TABLE test_dy (id VARCHAR2(512 CHAR), name VARCHAR2(512 CHAR), type VARCHAR2(128), CONSTRAINT testdy_id_pk PRIMARY KEY(id));

Table created.

SQL> INSERT INTO test_dy VALUES('id1', 'Mark', 'Full-time');

1 row created.

SQL> INSERT INTO test_dy VALUES('id2', 'Will', 'Full-time');

1 row created.

SQL> INSERT INTO test_dy VALUES('id3', 'Joe', 'Part-time');

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE testDy_prc
  2  (
  3      i_sql_1         IN      VARCHAR2,
  4      i_sql_2         IN      VARCHAR2
  5  )
  6  AS
  7      l_sql           VARCHAR2(32767);
  8      l_arId          typ_arrChar := typ_arrChar();
  9      l_arName        typ_arrChar := typ_arrChar();
 10      l_arType        typ_arrChar := typ_arrChar();
 11  BEGIN
 12      l_sql := 'SELECT  id,
 13                      name,
 14                      type
 15              FROM test_dy
 16              WHERE
 17              ('  || CHR(10)
 18                  || i_sql_1
 19                  || CHR(10) ||
 20              ')
 21              ORDER BY name asc
 22              OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
 23          ';
 24
 25     EXECUTE IMMEDIATE
 26          ' BEGIN
 27              EXECUTE IMMEDIATE ''' || l_sql || '''
 28                  BULK COLLECT INTO :l_arId, :l_arName, :l_arType
 29                  USING ' || i_sql_2 ||';END;' using out l_arId, out l_arName, out l_arType;
 30
 31      IF (l_arName.COUNT>0) THEN
 32          FOR i IN 1 .. l_arName.COUNT
 33          LOOP
 34              dbms_output.put_line('name: ' || l_arName(i));
 35          END LOOP;
 36      END IF;
 37  END testDy_prc;
 38  /

Procedure created.

SQL>
SQL> set serverout on
SQL> DECLARE
  2      l_sql_1                     VARCHAR2(32767);
  3      l_sql_2                     VARCHAR2(32767);
  4  BEGIN
  5      l_sql_1 := 'LOWER(name) LIKE LOWER(:3)   OR LOWER(name) LIKE LOWER(:5)';
  6      l_sql_2 := '''%Mark%'', ''Will%''';
  7
  8      testDy_prc
  9      (
 10          i_sql_1         =>       l_sql_1,
 11          i_sql_2         =>       l_sql_2
 12      );
 13  END;
 14  /
name: Mark
name: Will

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Rating

(5 ratings)

Comments

Thanks, follow-up query

Sagar, April 12, 2024 - 6:50 am UTC

Thanks Connor. Above worked fine.

I have follow-up use case. Here I'm passing multiple nested table arrays as input with dynamic filter clause with TABLE().

Here is LiveSQL script with re-producible test case:
https://livesql.oracle.com/apex/livesql/s/bkw5tpucrwryeanwir5sard22

Can you help here please. I feel dynamic SQL is bit tricky sometimes. You learn something new every day.

Thanks!!

benefit of cursor sharing ?!?!?!

Rajeshwaran Jeyabal, April 16, 2024 - 5:57 am UTC

If we go with this route, we lose the benefit of cursor sharing and will lead to SQL Injection vulnerability.

better have a look at this Oracle magazine article from Tom kyte.
https://asktom.oracle.com/Misc/oramag/on-popularity-and-natural-selection.html

or go through the chapter#16 on Expert one on one Oracle book.

April 22, 2024 - 1:06 am UTC

Will review, any thoughts on above?

Sagar, April 17, 2024 - 5:37 am UTC

Thanks for the above reply. Will surely review the above link.

For now, is it possible for you to review the LiveSQL link I've shared and just point what I can do to get it working for now. In next iteration, will try to implement the way described in your shared link.

Appreciate your help here.

to the above request.

Rajeshwaran Jeyabal, April 18, 2024 - 2:09 pm UTC

you cannot pass the array instance like this

   l_array1.EXTEND;
    l_array1(l_array1.COUNT) := 'Mark';

    l_array2.EXTEND;
    l_array2(l_array2.COUNT) := 'Full-Time';

    -- Dynamically generated from Step#3
    l_sql_2 := 'l_array1, l_array2';

instead can you take JSON route like this? that is make your inputs to be represented as JSON

{ "inputs" : { "l_array1" :["Mark"], "l_array2" :["Full-time"] } }

then your code will be like this

demo@PDB1> create or replace procedure testdy_v2_prc
  2  (
  3      i_sql_1         in      varchar2,
  4      i_sql_2         in      varchar2
  5  )
  6  as
  7      l_sql           long;
  8      l_inputs_01    sys.odcivarchar2list := sys.odcivarchar2list();
  9     l_inputs_02    sys.odcivarchar2list := sys.odcivarchar2list();
 10     l_data  sys.odcivarchar2list := sys.odcivarchar2list();
 11  BEGIN
 12
 13      l_sql := 'SELECT  id FROM test_dy where '|| i_sql_1 ;
 14
 15     select jt.array_elements
 16             bulk collect into l_inputs_01
 17     from json_table(  treat( i_sql_2 as json ) , '$.inputs.l_array1[*]'
 18             columns ( array_elements varchar2(20) path '$' ) ) jt ;
 19
 20     select jt.array_elements
 21             bulk collect into l_inputs_02
 22     from json_table(  treat( i_sql_2 as json ) , '$.inputs.l_array2[*]'
 23             columns ( array_elements varchar2(20) path '$' ) ) jt ;
 24
 25     execute immediate l_sql
 26             bulk collect into l_data
 27             using l_inputs_01 , l_inputs_02 ;
 28
 29     for x in ( select * from table( l_data ) )
 30     loop
 31             dbms_output.put_line( ' output == '|| x.column_value );
 32     end loop;
 33  end;
 34  /

Procedure created.

demo@PDB1>
demo@PDB1> begin
  2     testdy_v2_prc(
  3             i_sql_1 => ' name IN (SELECT column_value FROM TABLE(:1)) AND type IN (SELECT column_value FROM TABLE(:2)) ' ,
  4             i_sql_2 => ' { "inputs" : { "l_array1" :["Mark"], "l_array2" :["Full-time"] } } ' );
  5  end;
  6  /
 output == id1

PL/SQL procedure successfully completed.

demo@PDB1>

but I am still afride of passing the PREDICATES like the above, you should be read the chapter#16 Dynamic sql from Expert one on one oracle, where he deals with these kind of examples/scenario (making dynamic predicates) in detail.

April 22, 2024 - 1:07 am UTC

but I am still afride of passing the PREDICATES like the above

Agreed.

Thanks

A reader, April 18, 2024 - 5:07 pm UTC

Thanks for the response. Appreciate it!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks, follow-up query

benefit of cursor sharing ?!?!?!

Will review, any thoughts on above?

to the above request.

Thanks

More to Explore

PL/SQL demos

PL/SQL docs

Returning data in EXECUTE IMMEDIATE with dynamic values in USING clause

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks, follow-up query

benefit of cursor sharing ?!?!?!

Will review, any thoughts on above?

to the above request.

Thanks

More to Explore

PL/SQL demos

PL/SQL docs