Create SQL*Plus Script Dynamically From SELECT Statement In Oracle

How to create SQL*Plus Script Dynamically in Oracle

I have created a stored procedure in Oracle to generate SQL*Plus script from a SELECT statement. It will take SQL SELECT statement as parameter and on that behalf it will create the SQL*Plus script as you can see in below example screen-shot, but it is having some limitations, which I will describe below but still it will definitely speed-up your work in creating SQL*Plus script from any given SQL Query. Also note that it will not PARSE the query, it will just read the columns from the statement between SELECT and FROM. You can pass the following types of SELECT statements as parameter to this procedure:
[success title=”SELECT Without Aliases” icon=”check-circle”]
SELECT
EMPNO, ENAME, JOB,
   MGR, HIREDATE, SAL,
   COMM, DEPTNO
FROM SCOTT_EMP S
[/success]
[success title=”SELECT With Table Reference” icon=”check-circle”]
SELECT
S.EMPNO, S.ENAME, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/success]
[success title=”SELECT With Aliases” icon=”check-circle”]
SELECT
S.EMPNO emp_no, S.ENAME employee_name, S.JOB Job,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/success]
[error title=”SELECT With Expressions NOT ALLOWED” icon=”exclamation-circle”]
SELECT
NVL(S.EMPNO, ‘19009’) emp_no, S.ENAME employee_name, Decode(S.JOB, ‘ABC’, ‘Y’) Job,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/error]
NOTE: No need to pass the WHERE Clause of your query just pass the statement from “Select to… From “, you can add your query WHERE clause into script after the script generation.

Below is the stored procedure which you need to create in your schema to create SQL Scripts from it:

[code type=”SQL”]CREATE OR REPLACE PROCEDURE create_sql_script (
   i_query        IN     VARCHAR2)
AS
   TYPE t_columns IS TABLE OF VARCHAR2 (100)
                        INDEX BY BINARY_INTEGER;

   v_columns   t_columns;
   v_query VARCHAR2 (4000)
         := REPLACE (REPLACE (i_query, CHR (13), ”), CHR (10), ‘ ‘);
   v_string VARCHAR2 (4000)
         := LTRIM (
               LOWER(SUBSTR (v_query,
                             INSTR (LOWER (v_query), ‘select ‘),
                             INSTR (LOWER (v_query), ‘ from’))),
               ‘select ‘)
            || ‘,’;
   i           NUMBER := 1;

   FUNCTION getstring (source_string    IN VARCHAR2,
                       field_position   IN NUMBER,
                       unterminated     IN BOOLEAN DEFAULT FALSE,
                       delimiter        IN VARCHAR2 DEFAULT ‘,’)
      RETURN VARCHAR2
   IS
      iptrend           PLS_INTEGER := 0;
      iptrstart         PLS_INTEGER := 0;
      vcsourcestrcopy   VARCHAR2 (2000) := source_string;
   BEGIN
      IF unterminated
      THEN
         vcsourcestrcopy := vcsourcestrcopy || delimiter;
      END IF;

      IF field_position > 1
      THEN
         iptrstart :=
            INSTR (vcsourcestrcopy,
                   delimiter,
                   1,
                   field_position – 1)
            + LENGTH (delimiter);
      ELSE
         iptrstart := 1;
      END IF;

      iptrend :=
         INSTR (vcsourcestrcopy,
                delimiter,
                1,
                field_position);
      RETURN SUBSTR (vcsourcestrcopy, iptrstart, (iptrend – iptrstart));
   END getstring;                                         /* String Version */
BEGIN
   DBMS_OUTPUT.enable (200000);

   WHILE getstring (v_string,
                    i,
                    FALSE,
                    ‘,’) IS NOT NULL
   LOOP
      v_columns (i) :=
         RTRIM (LTRIM (getstring (v_string,
                                  i,
                                  FALSE,
                                  ‘,’)));

      IF INSTR (v_columns (i), ‘.’) > 0
      THEN
         v_columns (i) :=
            SUBSTR (v_columns (i), INSTR (v_columns (i), ‘.’) + 1);
      END IF;

      IF INSTR (v_columns (i), ‘ ‘) > 0
      THEN
         v_columns (i) :=
            LTRIM (SUBSTR (v_columns (i), INSTR (v_columns (i), ‘ ‘)));
      END IF;

      i := i + 1;
   END LOOP;

   DBMS_OUTPUT.put_line (‘REM ********************************************’);
   DBMS_OUTPUT.put_line (‘REM * Script Name   : [Name].SQL’);
   DBMS_OUTPUT.put_line (‘REM * Description     : []’);
   DBMS_OUTPUT.put_line (‘REM * Author           : []’);
   DBMS_OUTPUT.put_line (‘REM * Created Date  : []’);
   DBMS_OUTPUT.put_line (‘REM * Parameter      : []’);
   DBMS_OUTPUT.put_line (‘REM ********************************************’);
   DBMS_OUTPUT.put_line (‘ ‘);
   DBMS_OUTPUT.put_line (‘SET     HEADING   ON;’);
   DBMS_OUTPUT.put_line (‘SET     TERM      ON;’);
   DBMS_OUTPUT.put_line (‘SET     ECHO      OFF;’);
   DBMS_OUTPUT.put_line (‘SET     WRAP      OFF;’);
   DBMS_OUTPUT.put_line (‘SET     FEED      OFF;’);
   DBMS_OUTPUT.put_line (‘SET     VER       OFF;’);
   DBMS_OUTPUT.put_line (‘SET     UNDERLINE ON;’);
   DBMS_OUTPUT.put_line (‘SET     UNDERLINE =;’);
   DBMS_OUTPUT.put_line (‘SET     PAGESIZE  100;’);
   DBMS_OUTPUT.put_line (‘SET     LINESIZE  300;’);
   DBMS_OUTPUT.put_line (‘SET     COLSEP ,;’);
   FOR n IN v_columns.FIRST .. v_columns.LAST
   LOOP
      DBMS_OUTPUT.put_line(   ‘COLUMN  ‘
                           || LTRIM (UPPER (v_columns (n)))
                           || ‘ FORMAT A20 HEADING ‘
                           || CHR (34)
                           || LTRIM (UPPER (v_columns (n)))
                           || CHR (34));
   END LOOP;

   DBMS_OUTPUT.put_line (‘Spool YourFileName.csv’);
   DBMS_OUTPUT.put_line (REPLACE (i_query, ‘;’, ‘ ‘));
   DBMS_OUTPUT.put_line (‘/’);
   DBMS_OUTPUT.put_line (‘Spool Off;’);
   DBMS_OUTPUT.put_line (‘Exit’);
   DBMS_OUTPUT.put_line (‘/’);
   DBMS_OUTPUT.put_line (‘ ‘);
END;[/code]

Call this procedure as following:

[code type=”SQL”]set serveroutput on;
BEGIN
   create_sql_script (
      ‘SELECT
S.EMPNO Employee_Number, S.ENAME Employee_Name, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S;’);
END;[/code]
Now capture the output generated through DBMS_OUTPUT command and paste it into file and save it as .SQL. Below is the output of above SELECT statement:
[code type=”SQL”]REM ********************************************
REM * Script Name   : [Name].SQL
REM * Description   : []
REM * Author        : []
REM * Created Date  : []
REM * Parameter     : []
REM ********************************************
SET     HEADING   ON;
SET     TERM      ON;
SET     ECHO      OFF;
SET     WRAP      OFF;
SET     FEED      OFF;
SET     VER       OFF;
SET     UNDERLINE ON;
SET     UNDERLINE =;
SET     PAGESIZE  100;
SET     LINESIZE  300;
SET     COLSEP ‘,’;

COLUMN  EMPLOYEE_NUMBER FORMAT A20 HEADING “EMPLOYEE_NUMBER”
COLUMN  EMPLOYEE_NAME FORMAT A20 HEADING “EMPLOYEE_NAME”
COLUMN  JOB FORMAT A20 HEADING “JOB”
COLUMN  MGR FORMAT A20 HEADING “MGR”
COLUMN  HIREDATE FORMAT A20 HEADING “HIREDATE”
COLUMN  SAL FORMAT A20 HEADING “SAL”
COLUMN  COMM FORMAT A20 HEADING “COMM”
COLUMN  DEPTNO FORMAT A20 HEADING “DEPTNO”

Spool YourFileName.csv

SELECT
S.EMPNO Employee_Number, S.ENAME Employee_Name, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
/
Spool Off;
Exit
/[/code]
You need to make some changes in header section and in COLUMN statements to specify exact length of the line and column and if column is numeric type then need to change the format also, as shown in below example:

COLUMN  SAL FORMAT 999999.99 HEADING “SAL”

You must change the line size according to your output and column separator if it is other than Comma ‘,’

SET     LINESIZE  300;
SET     COLSEP ‘,’;

Please comment below if you got issues during execution, thanks.