SQLPlus – Create View structure files using spooling

Question

Oracle version: 12c SQL Developer: 4.1.1.19

I’m using the code from the following question as a start: SQLPlus - spooling to multiple files from PL/SQL blocks

I want to create a process that:

1. Export the results of the following to a single file (about 110 rows) by SPOOLING:

   SPOOL c:\temp\view_test.csv
   
   select 'desc '||view_name||';'FROM SYS.ALL_VIEWS WHERE OWNER = 'AN_OWNER_NAME' and substr(VIEW_NAME,1,2)='SN';
   
   SPOOL OFF
   

2. For each ‘DESC’ line, create a csv file with the results of the ‘DESC’

   desc SPECIFIC_VIEW_NAME;
   

3. The results will look like this in each CSV for each view (about 110)

Name                   Null     Type
---------------------- -------- -------------- 
ID                     NOT NULL NUMBER(19)
SYS_ID                 NOT NULL VARCHAR2(1000)
ACTIVE                          VARCHAR2(1000)
APRM_ID                         VARCHAR2(1000)
APRM_KEY                        VARCHAR2(1000)
BUSINESS_ONLY                   VARCHAR2(1000)

Here is the code that I am currently using. When I run this code I am prompted to enter a value for dynamic_filename:

set pagesize 0
set trimspool on  
set headsep off 
set feedback off
set echo off 
set verify off
set timing off
set linesize 4000

DEFINE rows_per_file = 50


-- Create an sql file that will create the individual result files
SET DEFINE OFF

SPOOL c:\temp\generate_one.sql

PROMPT COLUMN which_dynamic NEW_VALUE dynamic_filename
PROMPT

PROMPT SELECT 'c:\temp\run_#'||TO_CHAR( &1, 'fm000' )||'_result.txt' which_dynamic FROM dual
PROMPT /

PROMPT SPOOL &dynamic_filename

PROMPT SELECT *
PROMPT   FROM ( SELECT a.*, rownum rnum
PROMPT            FROM ( select 'desc '||view_name||';'FROM SYS.ALL_VIEWS WHERE OWNER = 'AN_OWNER_NAME' and substr(VIEW_NAME,1,2)='SN' ) a
PROMPT           WHERE rownum <= ( &2 * 50 ) )
PROMPT  WHERE rnum >= ( ( &3 - 1 ) * 50 ) + 1
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

SET DEFINE &


-- Define variable to hold number of rows
-- returned by the query
COLUMN num_rows NEW_VALUE v_num_rows

-- Find out how many rows there are to be
SELECT COUNT(*) num_rows
  FROM ( SELECT LEVEL num_files FROM dual CONNECT BY LEVEL <= 120 );


-- Create a master file with the correct number of sql files
SPOOL c:\temp\run_all.sql

SELECT '@c:\temp\generate_one.sql '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files ) file_name
  FROM ( SELECT LEVEL num_files 
           FROM dual 
        CONNECT BY LEVEL <= CEIL( &v_num_rows / &rows_per_file ) )
/

SPOOL OFF

-- Now run them all
@c:\temp\run_all.sql

Answer 1

I asked around the office and someone suggested I use:

select TABLE_NAME,COLUMN_NAME, DATA_TYPE from SYS.ALL_TAB_COLS

And that got me what I needed.