Oracle Bind Variables
SqlDbx supports bind variables which similar to SQL*PLUS Bind Variables.
Creating and using Bind Variables
You create bind variables with the VARIABLE command. For example
VARIABLE varName1 VARCHAR2
VARIABLE varName2 NUMBER
VARIABLE varName3 CLOB
VARIABLE varName4 REFCURSOR
You reference bind variables in scripts by typing a colon (:) followed
immediately by the name of the variable.
For example
VARIABLE ret_val NUMBER;
BEGIN
:ret_val := 2;
END;
After executing this block you will see output in grid automatically
Using REFCURSOR Bind Variables
REFCURSOR bind variables allow to fetch and display results of a
SELECT
statement contained in PL/SQL block or they can be used to reference PL/SQL cursor variables in stored
procedures and functions. Also REFCURSOR bind variable can be returned from a stored procedure.
VARIABLE employee_info
REFCURSOR
BEGIN
OPEN :employee_info
FOR SELECT EMPNO, SAL FROM
SCOTT.EMP;
END;
After executing this block results automatically will be displayed in grid.
Using REFCURSOR Variables in stored procedures
CREATE PROCEDURE EmpReport (emp_ref
IN OUT REF CURSOR)
AS
BEGIN
OPEN emp_ref
FOR SELECT EMPNO, SAL FROM SCOTT.EMP;
END;
VARIABLE rc REFCURSOR;
BEGIN
EmpReport (:rc);
END;