Passing Value Parameters To SQL Script In Oracle

In this tutorial you will learn to how to pass the parameters to SQL Script and use that parameters in SQL statements. The following examples shows how to pass the Character String, Numeric and Date type parameters to SQL script.

Passing parameters to sqlplus script in Oracle

Suppose we have an SQL script “MyScript.Sql” and the following statements are in it and we need to pass the JOB, MANAGER and HIREDATE parameters to get the results.

So we will call that script as following:

@ C:ScriptsMyScript.sql SALESMAN 7698 31-MAR-2015

— Where (1) JOB => SALESMAN, (2) MGR => 7698 AND (3) HIREDATE => 31-MAR-2015

Parameters can be read sequentially, for eg. if you want to access JOB parameter which is in first position, then you can reference it with &1 and second parameter with &2 and so on.

In the following statement we are getting the results on the behalf of first parameter JOB which is Character String type and String type parameters can be reference within single quotes, eg. ‘&1’

SELECT EMPNO,
ENAME,
MGR,
SAL,
COMM
FROM SCOTT_EMP
WHERE JOB = ‘&1’
/

In the following example, it will display the records for particular Manager by its second Numeric type parameter MGR, which can be access without quoted string as &2

SELECT EMPNO,
ENAME,
SAL,
COMM
FROM SCOTT_EMP
WHERE MGR = &2
/
The following example will show the records matching with first two parameters, one is String and another is Numeric.

SELECT EMPNO,
ENAME,
SAL,
COMM
FROM SCOTT_EMP
WHERE JOB = ‘&1’ AND MGR = &2
/

The following example will display the records by matching third parameter, which is of Date type and Date type parameter also would be refer in quotes:

SELECT EMPNO, ENAME, HIREDATE
FROM SCOTT_EMP
WHERE HIREDATE = ‘&3’
/

The following example shows how to use parameter in PL/SQL Block:

SET SERVEROUTPUT ON;

DECLARE
v VARCHAR2 (100);
BEGIN
SELECT ENAME
INTO V
FROM SCOTT_EMP
WHERE EMPNO = &2;

DBMS_OUTPUT.put_line (v);
END;
/

Calling Script From Within A Script By Parameter

@ C:ScriptsMyScript.sql Emp.sql

Contents of MyScript.sql

@ C:Scripts&1

— It will run the Emp.sql script

Passing Value Parameters To SQL Script In Oracle

Vinish Kapoor