Press "Enter" to skip to content

SQL*PLUS Script Examples

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:

  1. SQL Script to Generate Simple Report in a Text File Using a Query.
  2. Generate CSV file Using SQL Script.
  3. Parameterized SQL Script.
  4. Calling a Stored Procedure in SQL Script.
  5. Inserting and Updating Rows Using SQL Script.
  6. 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.

See also: