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
SELECT EMPNO,
ENAME,
MGR,
SAL,
COMM
FROM SCOTT_EMP
WHERE JOB = '&1'
/
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
/
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