SQL PL/SQL dynamic variable binding

Aad Published at Dev

AAD

I would like to run below query:

SELECT 
    * 
FROM 
    TABLE1
WHERE 
    COL1 = :DynamicValue1
    AND  COL2 = :DynamicValue2
USING 
    USERENTEREDVALUE1, USERENTEREDVALUE2;

I don't want to use EXECUTE IMMEDIATE.

How can I use using keyword in select query? When I run this I get pop up to enter value but it gives error Ora-00933

Kaushik Nayak

You may use substitution variables

DEFINE lname = 'Rogers'
DEFINE mgrid = 122

SELECT *
FROM employees
WHERE last_name = '&lname'
AND manager_id  = '&mgrid';

When you run this in SQL developer or SQL* Plus, you get

old:SELECT *
FROM employees
WHERE last_name = '&lname'
AND manager_id  = &mgrid
new:SELECT *
FROM employees
WHERE last_name = 'Rogers'
AND manager_id  = 122

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        134 Michael              Rogers                    MROGERS                   650.127.1834         26-08-06 ST_CLERK         2900                       122            50

Or use Bind variables

VARIABLE   lname VARCHAR2(40) 
VARIABLE  mgrid NUMBER 
EXEC :lname := 'Rogers'
EXEC :mgrid := 122
VARIABLE x REFCURSOR


BEGIN
     OPEN :x FOR SELECT *
                 FROM employees
                 WHERE last_name =:lname 
                 AND manager_id =:mgrid;
END;
/

    PRINT x

Result

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.



EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        134 Michael              Rogers                    MROGERS                   650.127.1834         26-08-06 ST_CLERK         2900                       122            50

Collected from the Internet

Please contact [email protected] to delete if infringement.