CODEWITHSUNDEEP
oracle-databaseplsql
dawriter
dawriter

Reputation: 435

Oracle dynamic parameters

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?

Upvotes: 0

Views: 657

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51980

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;

Upvotes: 1

Related Questions