In this tutorial, you will learn to how to pass the parameters to Oracle SQL Script and use that parameters in SQL statements. The following examples show how to pass the Character String, Numeric, and Date type parameters to SQL script.
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