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.
[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]
[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"
SET LINESIZE 300;
SET COLSEP ',';