Here I am giving some examples of SQL*PLUS scripts in Oracle. By using SQL scripts you can do almost anything in Oracle database, there are no restrictions such as you can create the files using SPOOL command, you can call any stored procedure and function, or you can execute any PL/SQL anonymous blocks. Also, you can run the DDL commands and all these tasks you can do in a single SQL*PLUS script. The following are the list of examples I am giving below:
- SQL Script to Generate Simple Report in a Text File Using a Query.
- Generate CSV file Using SQL Script.
- Parameterized SQL Script.
- Calling a Stored Procedure in SQL Script.
- Inserting and Updating Rows Using SQL Script.
- Execute DDL Commands in SQL Script.
In SQL Script, we should always set some important environment variables to get the output of the script correctly. I have used some settings in all of my script examples below, and the following are the settings with their descriptions. I used REM command to remark the description text.
REM The SET ECHO statement controls whether or not the text of a file is displayed. SET ECHO OFF REM The SET VERIFY statement is used to turn on and off the display of command lines that have had substitutions performed. SET VERIFY OFF REM The SET TERMOUT statement controls the display of the output generated by statements executed from a file. SET TERM OFF REM The SET SPACE statement is used to specify how many spaces separate columns in displays and printouts. The default value is one (1). SET SPACE 0 REM The SET FEEDBACK statement controls whether or not SQLPLUS displays the "x rows returned" messages. By default, feedback is on. SET FEEDBACK OFF REM The SET HEADING statement controls the printing of headers for reports. SET HEADING ON REM The SET PAGESIZE statement sets the length of the report in lines. SET PAGES 10000 REM The SET LINESIZE statement sets the default length for SQLPLUS script lines. SET LINE 10000 REM The SET TRIMSPOOL statement determines whether SQL*Plus allows trailing blanks at the end of each spooled line. SET TRIMS ON
1. SQL Script to Generate Simple Report in a Text File Using a Query
The following script will generate a simple text file report from the EMP table using the SQL query.
set echo off
set verify off
set term off
set space 0
set feedback off
set heading on
set pages 10000
set line 10000
set trims on
Spool emp.txt
SELECT empno,
ename,
job,
sal,
hiredate
FROM EMP
WHERE deptno = 20;
Spool off
exit
Test (Save as emp.sql)
sqlplus scott/[email protected] @emp.sql
Output
EMPNOENAME JOB SALHIREDATE
---------------------------------------------------------------------------------------
7369SMITH CLERK 80017-DEC-80
7566JONES MANAGER 297502-APR-81
7788SCOTT ANALYST 300019-APR-87
7876ADAMS CLERK 110023-MAY-87
7902FORD ANALYST 300003-DEC-81
2. Generate CSV file Using SQL Script.
The following SQL*PLUS script will generate the CSV file from a query.
set echo off
set verify off
set term off
set space 0
set feedback off
set heading off
set pages 10000
set line 10000
set trims on
Spool emp.csv
SELECT chr(34)||empno||chr(34)||','||
chr(34)||ename||chr(34)||','||
chr(34)||job||chr(34)||','
||sal
FROM EMP
WHERE deptno = 30;
Spool off
exit
Test (Save as empcsv.sql)
sqlplus scott/[email protected] @empcsv.sql
Output
"7499","ALLEN","SALESMAN",1600 "7521","WARD","SALESMAN",1250 "7654","MARTIN","SALESMAN",1250 "7698","BLAKE","MANAGER",2850 "7844","TURNER","SALESMAN",1500 "7900","JAMES","CLERK",950
3. Parameterized SQL Script.
In the below SQL Script, we will generate the CSV file by passing the filename as a parameter and the department number as a parameter. In SQL*PLUS, the given parameters can be accessed as &1 if one parameter passed and if two then &1, &2. Below is the example:
set echo off
set verify off
set term off
set space 0
set feedback off
set heading off
set pages 10000
set line 10000
set trims on
Spool &1
SELECT chr(34)||empno||chr(34)||','||
chr(34)||ename||chr(34)||','||
chr(34)||job||chr(34)||','
||sal
FROM EMP
WHERE deptno = &2;
Spool off
exit
Test
sqlplus scott/[email protected] @emp.sql emptest.csv 10
The emptest.csv file will be generated for department number 10. Note: Use single quotes to match parameter values with character fields, for instance, ENAME = '&3'.
4. Calling a Stored Procedure in SQL Script.
In the following script, it will call the stored procedure COMPUTE_SAL by passing the employee number as IN parameter and n_sal as OUT parameter.
SET ECHO OFF SET VERIFY OFF SET TERM OFF SET SERVEROUTPUT ON var n_sal number BEGIN compute_sal (7369, :n_sal); END; / exit
Test (Save as empsal.sql)
sqlplus scott/[email protected] @empsal.sql
5. Inserting and Updating Rows Using SQL Script.
The following script will insert two rows into the EMP table and then will update all salary for 10% increment.
SET ECHO OFF
SET VERIFY OFF
SET DEFINE OFF;
INSERT INTO EMP (EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
VALUES (7369,
'SMITH',
'CLERK',
7902,
TO_DATE ('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
800,
217.5,
20);
INSERT INTO EMP (EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
VALUES (7499,
'ALLEN',
'SALESMAN',
7698,
TO_DATE ('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1600,
188,
30);
COMMIT;
UPDATE EMP
SET sal = sal + (sal * 10 / 100);
COMMIT;
exit
Test (Save as empins.sql)
sqlplus scott/[email protected] @empins.sql
Output
1 row created. 1 row created. Commit complete. 19 rows updated. Commit complete.
6. Execute DDL Commands in SQL Script.
In the following script, it will execute the ALTER TABLE Statement to add new column BONUS in the EMP table and then will update the BONUS column with 1000.
SET ECHO OFF SET VERIFY OFF SET DEFINE OFF; ALTER TABLE EMP ADD BONUS NUMBER(10); Update EMP Set BONUS = 1000; COMMIT; exit
Test (Save as empalter.sql)
sqlplus scott/[email protected] @empalter.sql
All above examples are based on SCOTT user's EMP table. You can download the SCOTT schema script from the following link: Download SCOTT Schema Script.