CODEWITHSUNDEEP
oracle-databaseplsqloracle12cdynamic-sql
Vivek N
Vivek N

Reputation: 991

Oracle - How to define Dynamic SQL with dynamic bind parameters?

I have this simple search stored procedure in Oracle database and want to specify the values for my bind variables dynamically. For example if only employee no is specified, my sql will have only one bind variable but if both employee no and name are specified, my sql will have two bind variables. How can I achieve this in my "USING" clause?

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_SEARCH_TEST 
    (IN_EMP_NO              IN VARCHAR2,
    IN_EMP_NAME              IN VARCHAR2,
    OUT_C_SEARCH_RESULT           OUT sys_refcursor)  
IS
SQL_QUERY VARCHAR2(500);

BEGIN

SQL_QUERY := 'SELECT * FROM EMPLOYEE WHERE ' ;

IF (IN_EMP_NO IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || 'EMPLOYEE_NO = :1 ';
END IF;

IF (IN_EMP_NAME IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || ' AND EMPLOYEE_NAME = :2';
END IF;

dbms_output.put_line(SQL_QUERY);

OPEN OUT_C_SEARCH_RESULT FOR
    SQL_QUERY USING <<How to define dynamically>>;   

END SP_EMPLOYEE_SEARCH_TEST;

Upvotes: 2

Views: 3863

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

If you have max. 2-3 optional parameters I would go for the manual way:

IF IN_EMP_NO IS NULL AND IN_EMP_NAME IS NULL THEN
    OPEN OUT_C_SEARCH_RESULT FOR SQL_QUERY;
ELSIF IN_EMP_NO IS NOT NULL AND IN_EMP_NAME IS NULL THEN
    OPEN OUT_C_SEARCH_RESULT FOR SQL_QUERY USING IN_EMP_NO;
ELSIF IN_EMP_NO IS NULL AND IN_EMP_NAME IS NOT NULL THEN
    OPEN OUT_C_SEARCH_RESULT FOR SQL_QUERY USING IN_EMP_NAME;
ELSIF IN_EMP_NO IS NOT NULL AND IN_EMP_NAME IS NOT NULL THEN
    OPEN OUT_C_SEARCH_RESULT FOR SQL_QUERY USING IN_EMP_NO, IN_EMP_NAME;
END IF;

or build your query like this:

SQL_QUERY := 'SELECT * FROM EMPLOYEE WHERE ' ;
SQL_QUERY :=  SQL_QUERY || ' (EMPLOYEE_NO = :1 OR :2 IS NULL)';
SQL_QUERY :=  SQL_QUERY || ' AND (EMPLOYEE_NAME = :3 OR :4 IS NULL)';
OPEN OUT_C_SEARCH_RESULT FOR
   SQL_QUERY USING IN_EMP_NO, IN_EMP_NO, IN_EMP_NAME, IN_EMP_NAME;

Otherwise if you want to have more flexibility then have a look at DBMS_SQL package. A valid example is this one:

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_SEARCH_TEST 
    (IN_EMP_NO              IN VARCHAR2,
    IN_EMP_NAME              IN VARCHAR2,
    OUT_C_SEARCH_RESULT           OUT sys_refcursor)  
IS

SQL_QUERY VARCHAR2(500);
cur INTEGER;
ret NUMBER;
bind BOOLEAN := FALSE;

BEGIN


IF (IN_EMP_NO IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || 'AND EMPLOYEE_NO = :p1 '; -- note the space at the end!
    bind := TRUE;
END IF;

IF (IN_EMP_NAME IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || 'AND EMPLOYEE_NAME = :p2 '; 
    bind := TRUE;
END IF;

IF bind then
   SQL_QUERY := 'SELECT * FROM EMPLOYEE '||REGEXP_REPLACE(SQL_QUERY, '^AND', 'WHERE');    
ELSE
   SQL_QUERY := 'SELECT * FROM EMPLOYEE';
END IF:
dbms_output.put_line(SQL_QUERY);

cur := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cur, SQL_QUERY, DBMS_SQL.NATIVE);

IF (IN_EMP_NO IS NOT NULL) THEN
    DBMS_SQL.BIND_VARIABLE(cur, ':p1', IN_EMP_NO);
END IF;    
IF (IN_EMP_NAME IS NOT NULL) THEN
    DBMS_SQL.BIND_VARIABLE(cur, ':p2', IN_EMP_NAME);
END IF;

ret := DBMS_SQL.EXECUTE(cur);
OUT_C_SEARCH_RESULT := DBMS_SQL.TO_REFCURSOR(cur);


END SP_EMPLOYEE_SEARCH_TEST;

Upvotes: 2

Sentinel
Sentinel

Reputation: 6449

Just pass them both but change your dynamic portion:

IF (IN_EMP_NO IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || 'EMPLOYEE_NO = :1 ';
ELSE
    SQL_QUERY :=  SQL_QUERY || ':1 IS NULL ';
END IF;

IF (IN_EMP_NAME IS NOT NULL) THEN
    SQL_QUERY :=  SQL_QUERY || ' AND EMPLOYEE_NAME = :2';
ELSE
    SQL_QUERY :=  SQL_QUERY || ' AND :2 IS NULL';
END IF;

That way the query still has the same number of binds.

You could also rewrite the query like this:

SELECT * FROM EMPLOYEE
 WHERE EMPLOYEE_NO = NVL(:1, EMPLOYEE_NO)
   AND EMPLOYEE_NAME = NVL(:2, EMPLOYEE_NAME)

Which also still takes the same number of parameters and gets the desired results when bind values are null.

Upvotes: 1

Related Questions