REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM
REM Free use of this script requires this disclaimer/disclosure
REM remaining with the script.
REM
REM Send questions or comments to "dave@dasages.com"
REM **************************************************************
set echo off
accept x prompt "Enter the table to fix length: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set long 32767
prompt set longchunksize 32767
prompt set lines 32767
prompt set trimspool on
prompt set pagesize 0
prompt spool &y
select decode(rownum,1,'Select ','||')|| -- Displays "SELECT" in front of 1st line, "||" in front of any other line
CASE when substr(data_type,1,4) = 'DATE' then -- checks for DATE column
'nvl(to_char('||column_name||',''YYYYMMDD''),''00000000'')' -- codes DATE-column output/ null = all zeroes
when substr(data_type,1,4) = 'LONG' then -- checks for LONG column
'rpad('||column_name -- left justifies LONG column
||',(select max(length('||column_name||')) from &x))' -- creates code to "try" to determine longest LONG
-- but this code will fail when one attempts to execute the 'temp.sql' generated script.
when substr(data_type,1,4) = 'CLOB' then -- checks for CLOB column
'rpad(nvl('||column_name||',null_clob)' -- left justifies CLOB column
||',(select max(length('||column_name||')) from &x))' -- returns length of longest CLOB for fixed-length
when substr(data_type,1,4) = 'VARC' then -- checks for VARCHAR2 column
'rpad(nvl('||column_name||','' '')' -- left justifies VARCHAR2 column
||','||ltrim(to_char(data_length,'9999'))||')' -- hard codes length of VARCHAR2 column
when substr(data_type,1,4) = 'CHAR' then -- checks for fixed-length CHAR column
'rpad(nvl('||column_name||','' '')' -- left justifies fixed-length CHAR column
||','||ltrim(to_char(data_length,'9999'))||')' -- hard codes length of VARCHAR2 column
when substr(data_type,1,4) = 'NUMB' and data_scale is not null and data_precision is not null then
-- checks for NUMBER column and ensures explicit precision and scale
[b]'cobol_sign('||column_name||','|| -- produces a value with overpunched rightmost chr. for COBOL sign handling[/b]
'lpad(substr(nvl('||column_name||',0)' -- raw numeric source column
||'* power(10,'||data_scale||')' -- multiplies raw numeric value by a power of 10
-- equalling "scale" for decimal alignment
||',1,' -- substr decimal-aligned string begining with leftmost character (position 1)
||data_precision||')' -- specifies maximum length of substring of decimal-aligned column value
||','||data_precision -- specifies fixed length of resulting left-zero-padded numeric column
||',''0'')' -- specifies left padding as zeroes
[b]||')' -- closes "COBOL_SIGN" function[/b]
when substr(data_type,1,4) = 'NUMB' and data_scale is null and data_precision is null then
-- checks for NUMBER created without explicit precision and scale, i.e. "NUMBER" instead of "NUMBER (x,y)"
[b]'cobol_sign('||column_name||','|| -- produces a value with overpunched rightmost chr. for COBOL sign handling[/b]
'lpad(substr(nvl('||column_name||',0)' -- raw numeric source column
||'* power(10,' -- multiplied by a power of 10 equalling "scale" (calculated below)
||'(select max(length(substr('||column_name -- specify numeric column for decimal check
||',instr('||column_name||',''.'')' -- locate decimal
||'+1' -- then begin string just beyond decimal
||')' -- end substr from behind decimal
||')' -- end determining length of post-decimal string
||')' -- end finding greatest post-decimal string
||'from &x' -- specifies target table to subquery
||')' -- end sub-query returning longest post-decimal magnitude
||')' -- end of POWER function that specifies number of decimal point-alignment digits
||',1,' -- substr decimal-aligned string begining with leftmost character (position 1)
-- for a total substring length calculated by sum of maximum pre-decimal digits
-- plus max post-decimal digits:
||'(Select max(length(trunc('||column_name||')' -- produce pre-decimal string
||')' -- end determining length of pre-decimal digits
||')' -- end finding longest set of pre-decimal digits
||'from &x'-- specifies target table to subquery
||')' -- end sub-query that returns longest pre-decimal length
||'+' -- plus longest post-decimal length (following)
||'(Select max(length(substr('||column_name -- specify numeric column for post-decimal-length check
||',instr('||Column_name||',''.'')' -- locate decimal in numeric value
||'+1' -- then begin string just beyond decimal
||')' -- end substr from behind decimal string
||')' -- end determining length of post-decimal string
||')' -- end determining greatest length of post-decimal string
||'from &x' -- specifies target table to subquery
||')' -- end sub-query that returns longest post-decimal length
||')' -- end of substr of decimal-aligned, decimal-removed value
||',' -- ends argument 1 of LPAD
-- following section reproduces "length" argument of LPAD. As above, the LPAD length results
-- from sum of maximum pre-decimal digits plus max post-decimal digits:
||'(Select max(length(trunc('||column_name||')' -- produce pre-decimal string
||')' -- end determining length of pre-decimal digits
||')' -- end finding longest set of pre-decimal digits
||'from &x'-- specifies target table to subquery
||')' -- end sub-query that returns longest pre-decimal length
||'+' -- plus longest post-decimal length (following)
||'(Select max(length(substr('||column_name -- specify numeric column for post-decimal-length check
||',instr('||Column_name||',''.'')' -- locate decimal in numeric value
||'+1' -- then begin string just beyond decimal
||')' -- end substr from behind decimal string
||')' -- end determining length of post-decimal string
||')' -- end determining greatest length of post-decimal string
||'from &x' -- specifies target table to subquery
||')' -- end sub-query that returns longest post-decimal length
||',''0'')' -- LPAD's left-fill character definition
[b]||')' -- closes "COBOL_SIGN" function[/b]
else
'Datatype ("'||data_type||'") for column "'||column_name||'" not supported.'
end fixed_lengths
from user_tab_columns
where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt
REM**********************************************************************************************************************************
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
I got back to you within 3 minutes of your earlier post, but it appears that you must have gone to bed right after you hit the [Submit Post] button. I would love to deliver "the final solution" to you tonight, but it would be a little useless if I choose wrong on the "NULL numbers" issue.