PL/SQL Dynamic case when statement for parameters to pass into Java

Question

I am fairly new to PL/SQL so I haven't been able to piece out some of the logic (specifically execute immediate)

In my PL/SQL statement, I am essentially trying to create a trigger for a prompt where the user could either have D1.SESSION or D1.A. I am using variables to create this trigger

Wherby if searchType = 1 then the user would input D1.SESSION data
else the user would input Input data

This trigger would be fed into a Java app. where the searchType would be defined before running the application

The issue I am getting is when I declare the searchType as :type, it doesn't seem to recognize the execution at the execute immediately.

{ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "STMT" when expecting one of the following:

   := . ( @ % ;
}

Please let me know where I am going wrong and if you provide any resources that you could point me towards the solution, that would be great!

Below is my query:

DECLARE
   stmt varchar2(32767);  
   searchType number(3) := 1;  
BEGIN
   stmt := 'SELECT
   D1."A" AS "A",
   D1."ID" AS "ID",
   D1."ID_TYPE" AS "IDTYPE",
   D1."SESSION" AS "SESSION"        
FROM
   "DB"."TABLE" D1
WHERE (';

   case
       when :type = '1' then stmt := stmt || 'D1."SESSION" = (?SESSION)';
       when :type = '2' then stmt := stmt || 'D1."A" in (?input)';
   end case;
   stmt := stmt ||')';
   
   EXECUTE IMMEDIATE stmt using searchType;
END;

Please let me know how I can improve formating my questions on Stack. Thanks :)

Trigger is a special database object that fires automatically **at the database side** when some event occurs. It doesn't accept any input. If you want to run your PL/SQL statement from your app, then it should not be a trigger, but a procedure/function/anonymous block. And you do not need any dynamic logic, all parts of the statement is static (e.g. you do not change identifiers dynamically, only values). — astentx, Aug 04 '22 at 06:42

score 0 · Answer 1 · answered Aug 04 '22 at 05:58

How about this?

Note that

this is SQL*Plus so I'm using a substitution variable (instead of a bind variable); see line #3
I'm not executing the SELECT statement as it is invalid in pure SQL because of the WHERE clause, so I'm just displaying it on the screen. You'd remove DBMS_OUTPUT.PUT_LINE call and use EXECUTE IMMEDIATE instead

SQL> DECLARE
  2     stmt        VARCHAR2 (32767);
  3     searchtype  NUMBER (3) := &par_type;  --> you'd use :par_type
  4  BEGIN
  5     stmt :=
  6  'SELECT D1."A",
  7         D1."ID",
  8         D1."ID_TYPE",
  9         D1."SESSION"
 10  FROM "DB"."TABLE" D1
 11  WHERE ' || CASE
 12               WHEN searchtype = '1' THEN 'D1."SESSION" = (?SESSION)'
 13               WHEN searchtype = '2' THEN 'D1."A" in (?input)'
 14             END;
 15
 16     DBMS_OUTPUT.put_line (stmt);
 17
 18     --   EXECUTE IMMEDIATE stmt;
 19  END;
 20  /
Enter value for par_type: 2
SELECT D1."A",
       D1."ID",
       D1."ID_TYPE",
       D1."SESSION"
FROM
"DB"."TABLE" D1
WHERE D1."A" in (?input)

PL/SQL procedure successfully completed.

SQL>

PL/SQL Dynamic case when statement for parameters to pass into Java

1 Answers1