Oracle dynamic parameters

Question

I'm struggling to create a dynamic sql parametrized query. It involves using 'IS NULL' or 'IS NOT NULL'

Here's a simple pl/sql query:

CREATE OR REPLACE PROCEDURE GET_ALL_INFORMATION 
(
  "PARAM_START_DATE" IN DATE,
  "PARAM_END_DATE" IN DATE,
  "PARAM_IS_SUBMITTED" IN NUMBER,
  "EXTRACT_SUBMITTED_CONTACTS" OUT sys_refcursor
) IS

sql_stmt    VARCHAR2(3000);
PARAM_CONDITION VARCHAR2(20);

BEGIN

IF PARAM_IS_SUBMITTED = 1 THEN
     PARAM_CONDITION := 'NOT NULL'; 
    ELSE
     PARAM_CONDITION :=  'NULL';
  END IF;

  sql_stmt := ' SELECT
  REGISTRATION_NUMBER,
  NAME PROVIDER_TYPE,
  ORGANIZATION
  FROM TABLE_A 
  WHERE
  P.DATE_FINALIZED IS :A;

  OPEN EXTRACT_SUBMITTED_CONTACTS FOR sql_stmt USING PARAM_CONDITION;

Whereas the parameter (:A) in (USING PARAM_CONDITION) should have 'NULL' or 'NOT NULL'. It does not seem to work the way I envisioned.

Am I missing something?

Bind parameters can't be used in that fashion. Also, you seem to be missing a closing single-quote. If you're going to use dynamic SQL, just create your string value to directly contain the is null or is not null. — GriffeyDog
– GriffeyDog, Commented Dec 22, 2014 at 22:14

Sylvain Leroux · Accepted Answer · 2014-12-22 22:33:41Z

As explained by GriffeyDog in a comment above, bind parameters could only be used as place holder for values. Not to replace keywords or identifiers.

However, this is not really an issue here, as you are using dynamic SQL. The key idea ifs that you build your query as a string -- and it will be parsed at run-time by the PL/SQL engine when you invoke EXECUTE or OPEN .. FOR.

Simply said, you need a concatenation -- not a bound parameter:

  ...
  sql_stmt := ' SELECT
  REGISTRATION_NUMBER,
  NAME PROVIDER_TYPE,
  ORGANIZATION
  FROM TABLE_A 
  WHERE
  P.DATE_FINALIZED IS ' || PARAM_CONDITION;
  --                    ^^

  OPEN EXTRACT_SUBMITTED_CONTACTS FOR sql_stmt;

Collectives™ on Stack Overflow

Oracle dynamic parameters

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related