2

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;
Improve this question
3
  • Vivek, similar to your proposition to me, There is a problem with IF statements in case the search is only based on employee_name. You should get rid of AND in the beginning then. Commented Mar 2, 2018 at 22:08
  • 1
    The optimal way is to have DIFFERENT SQL statements for the different possibilities, and just run the one that "fits". Otherwise, if you write a complex WHERE clause to accommodate the various combinations, the resulting query will be slow at least in some cases. Commented Mar 2, 2018 at 23:19
  • @mathguy This is for giant search page for which there can be quite a large no of possibilities and hence was looking for some right optimal solution. Commented Mar 2, 2018 at 23:50

2 Answers 2

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I believe the second approach of yours can work for me. My only concern is that it will add lots of extra bind variables. When you get a chance, pls provide the concrete example.
@VivekN, please see example using DBMS_SQL package.
I like the DBMS_SQL package solution but I opted to use the native dynamic sql option as that fits my need and is faster.
1

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.

Improve this answer

4 Comments

employee_name = nvl(:2, employee_name) will miss the few rows where the employee name is NULL (if there are any).
You're absolutely right if either of the columns contain null values. At the risk of losing the ability to use indexes the predicates can be written like this nvl(EMPLOYEE_NAME,'IS NULL') = coalesce(:1, EMPLOYEE_NAME, 'IS NULL')
@Sentinel I like your first approach. It allows me to have same number of bind variable. I will try that out and get back.
@Sentinel I ended up using your first approach. It worked as expected.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.