Describe on table with number file inconsistent 1

[wolves]

Doing a describe on a table defined as such:

desc table_num

Name           Null?    Type
-------------- -------- ----------------------------
NUMBER1                  NUMBER(10,2)
NUMBERON2                NUMBER(10,2)

I get this when I select data:

Select * from table_num;

    NUMBER1    NUMBERON2 
 ---------- ------------   
    1024.66            0

The data is not the same length. Spooling to and counting the lengths confirm that each are different length.

Any ideas?

Thx in advance.

 

[SantaMufasa]

Yes, wolves, when you create a NUMBER column in Oracle and if your specification is "10,2", this means that the largest (absolute) value you can store is 99999999.99. if you attemp to store a value of 29.447893, then Oracle stores "29.45". If you store a value of 3.0015786, then Oracle stores "3" (since 3.00 and 3 are equal algebraically). Unlike other data storage schemes (e.g., COBOL data files), Oracle does zero fill with insignificant digits either to the left or the right. That is why the appearances of your data are what they are.

If, however, you wish to display values that are decimal aligned, then you can use the characteristics of SQL*Plus:

select 0 from dual;

         0
----------
         0

col a format 999.99
select 0 a from dual;

      A
-------
    .00

col b format 990.00
select .1 b from dual;

      B
-------
   0.10

Does this appear to explain and resolve your situation?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[wolves]

Thanks Mufasa, it does explain the situation. I'll have to find a way to resolve my problem though.
What I am trying to do is run scripts and spool to files where then I will use as input to COBOL programs which the input file has the same format that matches the table. But when the spool file is read into the program, not all the number fields are the same length because of how you explained it above. So I'll have to find a work around.
Thanks again for the enlightment.

 

[SantaMufasa]

How would you like a script that produces fixed-length column/records with signed right-hand bytes specifically for input to COBOL programs?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[wolves]

How would you like a script that produces fixed-length column/records with signed right-hand bytes specifically for input to COBOL programs?

Been trying to search and do that with no luck yet. But the internet is my friend, I am doomed to find one. But if you know of one, it would be appreciated.

I figured, that since Oracle stores all numeric data in variable length format, correct, then I would just have to reformat the number fields to their correct size.

 

[wolves]

I assume I can just lpad all my number fields to what I need to get the same result.

 

[carp]

Wolves -
You need to understand - SantaMufasa is the king of incredible scripts/procedures/functions! If he asks you if you would like a certain script, you can bet the farm that he either already has it or will build it shourtly.

Meanwhile, for my two cents - if you are going to have this particular requirement for some time and only on this table, then you might also consider creating a view that formats the output the way you want it, then have your calling program go to the view instead of the table.

 

[SantaMufasa]

Wolves,

Yes, the Internet is your friend...especially Tek-Tips. Here are my scripts that do what you want. (I've widened the screen display to avoid unsightly/unwanted line-wrap.)

The first script creates a user-defined function, "COBOL_SIGN" that transforms a numeric value into the signed-right-byte format that COBOL expects. Then the ensuing sections define the main script and a sample invocation and output:

Section 1 -- COBOL_SIGN function:

create or replace function cobol_sign (val number, str varchar2) return varchar2 is
    sign_val    number;
    last_chr    varchar2(1);
    punched_chr varchar2(1);
    return_str  varchar2(100);
begin
    sign_val  := sign(val);
    last_chr  := substr(str,-1);
    case when sign_val = -1 then
             punched_chr := translate(last_chr,'0123456789','}JKLMNOPQR');
             return_str  := substr(str,1,length(str)-1)||punched_chr;
         when sign_val in (0,1) or sign_val is null then
             punched_chr := translate(last_chr,'0123456789','{ABCDEFGHI');
             return_str  := substr(str,1,length(str)-1)||punched_chr;
    end case;
    return return_str;
end;
/

Function created.

Section 2 -- "GenFixed.sql" script:

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
            'cobol_sign('||column_name||','|| -- produces a value with overpunched rightmost chr. for COBOL sign handling
            '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
                  ||')' -- closes "COBOL_SIGN" function
       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)"
            'cobol_sign('||column_name||','|| -- produces a value with overpunched rightmost chr. for COBOL sign handling
            '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
               ||')' -- closes "COBOL_SIGN" function
       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**********************************************************************************************************************************

Section 3 -- Sample test data:

select * from test2;

         A B              C                    D         E                                                           F
---------- -------------- -------------------- --------- -------------------------------------------------- ----------
       1.5 14 chrs. long  18 chrs. long max.   08-MAY-05 CLOB expression; fixed length = longest string.
        27 7 chars.       abc                  07-MAY-05 abc                                                  12345.35
       -45
  99999.99 abc            abc                  03-MAY-05 This is a short CLOB                               1234567.12
           wxyz           wxyz                 29-MAR-05 wxyz                                                 12345678
         0 wxyz           wxyz                 29-MAR-05 wxyz                                               .123456789

Section 4 -- Invocation of "GenFixed.sql" with overpunches for numeric expressions:

SQL> @genfixed
Enter the table to fix length: test2
Enter the flat file to write: test2.txt

Following output is generated script that writes text output from table "test2"

set echo off
set feedback off
set heading off
set long 32767
set longchunksize 32767
set lines 32767
set trimspool on
set pagesize 0
spool temp.txt
Select cobol_sign(A,lpad(substr(nvl(A,0)* power(10,2),1,7),7,'0'))
||rpad(nvl(B,' '),14)
||rpad(nvl(C,' '),20)
||nvl(to_char(D,'YYYYMMDD'),'00000000')
||rpad(nvl(E,null_clob),(select max(length(E)) from test2))
||cobol_sign(F,lpad(substr(nvl(F,0)* power(10,(select max(length(substr(F,instr(F,'.')+1)))from test2)),1,
(Select max(length(trunc(F)))from test2)+
(Select max(length(substr(F,instr(F,'.')+1)))from test2)),(Select max(length(trunc(F)))from test2)+
(Select max(length(substr(F,instr(F,'.')+1)))from test))
from test2
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "temp.txt"

000015{14 chrs. long 18 chrs. long max.  20050508CLOB expression; fixed length = longest string.0000000000000000{
000270{7 chars.      abc                 20050507abc                                            0001234535000000{
000450{                                  00000000                                               0000000000000000{
999999Iabc           abc                 20050503This is a short CLOB                           0123456712345670{
000000{wxyz          wxyz                20050329wxyz                                           1234567800000000{
000000{wxyz          wxyz                20050329wxyz                                           0000000012345678I

Output file = "test2.txt"

Let us know, Wolves if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[wolves]

Going to give it a spin and let you know. By the looks of it, this seems to be exactly what I need.
Thanks again and will let you know of the results.

 

[carp]

Wolves -
What did I tell you?
Dave IS the king!

 

[wolves]

He is the king there Carp, he is the king. I just finished running the script with tremendous success.

Thanks again Dave, you have saved me what would take a day of work into a couple minuts, at the most.

Thanks again. I can only hope to be able to return the favor one day, but I still have much to learn.