How to export/extract Oracle Tables into Fixed-length ASCII Files 1

[CraigJConrad]

I am quite new to Oracle, and have searched extensively to find an answer to this problem, so I hope that I am not asking something too obvious to others.

We have a client running Oracle for an application being sunset. In order to accomodate this, we need to perform extensive processing on the final data using Cobol programs that run on a different server (ie, these programs can not have access to the Oracle database directly).

Therefore, we need to extract (export?) data from the Oracle tables into a flat file to be shipped to the server for processing by Cobol. The flat files must be in "traditional" Cobol FD format -- i.e. each field is fixed-length (no CSV). Of course, the text fields must be in ASCII (not binary) and ideally the numeric fields would be ASCII (not COMP) as well.

What is the best approach to do this? Purchasing of a reasonably-priced tool is an acceptable solution.

Thanks in advance for any assistance.

Craig

 

[CraigJConrad]

Mufasa,

Our updates crossed in the network. This looks perfect for most of what I would need (perhaps all I need for my immediate problem, anyway). Do you think that VARCHAR length and/or NULL (all NULLABLE fields) could be supported? I *really* don't mean to be taking unfair advantage of you!!!

I really am amazed at what you've done for me, and how quickly you did so!!!

Craig

 

[SantaMufasa]

Craig,

Your assessment is correct. I just felt that you probably would not want add a separate indicator field for each and every numeric field in your COBOL file definitions. That seems to me to be a lot of contrived infrastructure for the COBOL program. If you want to add that functionality, it is certainly doable. Also, adding such indicators to the GenFixed.sql script causes the script to be far less generic as a Fixed-length field generator. But if that is behaviour you always want in your fixed-length generator, then that is certainly a reasonable adjustment to the script's logic. You decide.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Craig,

Yes, the GenFixed.sql script handles all NULL fields for the supported data types. I converts NULL character-type expressions into fixed-length blank spaces (which is what COBOL wants) and NULL numeric expressions into fixed-length signed (overpunched) zeroes.

Is that what you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Mufasa,

OK, I understand -- especially for numbers. Do you have the same concern about VARCHAR lengths?

Thanks, Craig

 

[SantaMufasa]

And, BTW Craig, you have not been taking advantage of me. If I can create a generic, multi-purpose script that others can use as well, then I'm doing what I like to do. If I felt that someone was taking advantage, I have God-given agency to choose not to participate. I enjoy what I do here on Tek-Tips...it's recreational. I must admit, though, if GenFixed.sql does what you wanted, I believe I will go to bed now.

Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

OK, get some well-deserved sleep! And thanks very much.

Craig

 

[SantaMufasa]

Do you have the same concern about VARCHAR lengths?

I'm not particularly worried. For large character fields, you may run into some sort of limitation on SQL*Plus's ability to handle the data. At that point, we'd probably need to slightly redesign the script to run as a PL/SQL block that outputs via the "utl_file" packaged procedures that can read from, and write to, flat files directly. Let me know if that becomes an issue.

Cheers and g'nite.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Mufasa,

We are attempting to use the script and it isn't selecting anything. I assume that it is related to your comment that the script would only work for tables that the user owns.

We are needing to use the script to export all the tables in the database, without regard to who the owner is.

Is this an easy change?

Thanks, Craig

 

[SantaMufasa]

No, Craig, it's not a big deal. I should have built the script to prompt for the user in the first place. Additionally, having to respond to prompts to run GenFixed.sql for every table in every user schema is, IMHO a gigantic task. I can modify the above script to run as a single PL/SQL script that will create fixed-length records for every table that every Oracle user owns (except for the administrative users such as SYS and SYSTEM).

To make the script do exactly what you want, it would be good to resolve questions real time. Then, when we're done, we can post the final version here. You can contact me via signature information below.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Mufasa, I went to your web page and called your number (your outgoing message is messed up, I think). I'll be up for awhile (I am in the US, but working on a project in Singapore, so my hours are strange).

I like your ideas. Perhaps prompt for a specific table name, but allow a wildcard to do them all..?

If you wanted to add further usefulness (this is not a request on my part, btw, but it would be nice), generate a Cobol FD that matches the layout of the fixed-length records you are extracting...

Thanks, Craig

 

[CraigJConrad]

I have taken a slightly different tact on this problem, in that I have written Visual Basic code to generate SQL extract script. As the layouts were already in a spreadsheet (table name, column name, data type, length and precision), it was easy to do, and consistent with the actual Cobol FD's (copybooks) that I also generate from this Excel VBA macro.

The heart of the code for the SQL script is below. Note that it: (a) supports signed numbers; (b) supports null values in that it will place a x'FF' in the first byte of any null values (except type 'NUMBER').

I appreciate the generalized extract that Dave provided above, and there will likely be times that that would be the best and fastest approach to a fixed-format extract. For my immediate need, the following is best suited (and I learned a LOT over the past week about SQL!).

NOTES:
(a) "column" is the name of the table's column
(b) "picType" is the data type (e.g. NUMBER, CHAR, etc)
(c) "pic" is the actual Cobol PIC clause (e.g. "9(05)V(02)")
(d) "gen" indicates if a filler should be forced, rather than actually fetching the value (perhaps to "mask" some fields without changing the record layout, or because you use this for multiple tables which are "similar", but not quite identical)
(e) "signed" indicates if NUMBER types should be treated as a signed number (thereby using host Cobol's standard of an overpunch on the final digit)
(f) this uses "countPICBytes" which is a simple function to determine the number of bytes a Cobol PIC clause will consume in data. Easy to write yourself, or perhaps you already have the information. In my case, the PIC clause *could* be a field larger (or smaller) than the table column is defined for, so I needed this to ensure that the extracted data would match the actual COBOL copybook in use.
(g) you will see that this will cleanse the data of any CR/LF that might be lurking -- these are replaced by spaces.
(h) there is always room for improvement, but I don't anticipate making any to this, as it serves its purpose as it is. Feel free to comment, though.

Private Function makeSQLExtractString(ByVal column As String, _
                                      ByVal picType As String, _
                                      ByVal pic As String, _
                                      ByVal gen As Boolean, _
                                      ByVal signed As Boolean)
bytes = countPICBytes(pic, "")
numeric = False
decPlaces = 0
'If this is numeric pic, then we need to see if there are digits after decimal
If (InStr(pic, "9") > 0) Then
    numeric = True
    i = InStr(pic, "V")
    If (i > 0) Then
        tString = Mid(pic, i + 1)
        decPlaces = countPICBytes(tString, "")
    End If
End If

tString = ""
Select Case UCase(picType)
    Case "NUMBER"
        'Note that any NULL value will become a zero
        If (decPlaces = 0) Then
            tString0 = "lpad( nvl( " & Trim(column) & " , 0), " & Trim(bytes) & ", '0')"
        Else
            tString0 = "lpad( Round( nvl( " & Trim(column) & " , 0) * " & _
                        "Power( 10, " & Trim(decPlaces) & "), 0), " & Trim(bytes) & ", '0')"
        End If
        If signed Then
            tString1 = "substr( " & tString0 & ", 1," & Trim(bytes - 1) & ")"
            tString2 = "decode( substr( " & tString0 & ", " & Trim(bytes) & ")" & _
                       " || " & _
                       "sign( nvl( " & Trim(column) & " , 0))" & _
                       ", '00', '{', '01','{', '11','A', '21','B', '31','C', '41','D'" & _
                       ", '51','E', '61','F', '71','G', '81','H', '91','I'" & _
                       ", '0-1', '}', '1-1','J', '2-1','K', '3-1','L', '4-1','M'" & _
                       ", '5-1','N', '6-1','O', '7-1','P', '8-1','Q', '9-1','R')"
            tString = tString1 & " || " & tString2
        Else
            tString = tString0
        End If
    Case "CHAR"
        'Note that any NULL value will have a X'FF' in first byte
        tString = "nvl( " & Trim(column) & ", chr(255))"
        tString = "translate( " & tString & ", chr(10) || chr(13), chr(32) || chr(32))"
        If (bytes > 1) Then
            tString = "rpad( " & tString & ", " & Trim(bytes) & ")"
        End If
    Case "VARCHAR2"
        'Note that any NULL value will have a X'FF' in first byte
        tString = "nvl( " & Trim(column) & ", chr(255))"
        tString = "translate( " & tString & ", chr(10) || chr(13), chr(32) || chr(32))"
        tString = "rpad( " & tString & ", " & Trim(bytes) & ")"
    Case "CLOB"
        'Note that any NULL value will have a X'FF' in first byte
        tString = "rpad( nvl( " & Trim(column) & ", chr(255)), " & Trim(bytes) & ")"
    Case "DATE"
        'Note that any NULL value will return a '00000000' in first 8 bytes
        tString = "nvl( to_char( " & Trim(column) & ", 'YYYYMMDD'), '00000000')"
        If (bytes > 8) Then
            tString = "rpad( " & tString & ", " & Trim(bytes) & ")"
        End If
        If byes < 8 Then
            tString = "substr( " & tString & ", 1, " & Trim(bytes) & ")"
        End If
    Case Else
        MsgBox "Invalid <picType> specified --> " & picType
        Exit Function
End Select

If Not gen Then
    Select Case UCase(picType)
        Case "NUMBER"
            tString = "'" & repeat("0", bytes - 1) & "{'"
        Case "DATE"
            tString = "'" & repeat("0", bytes) & "'"
        Case Else
            tString = "lpad( chr(32), " & Trim(bytes) & ")"
    End Select
End If
    
makeSQLExtractString = tString

End Function
[code]

 

[SantaMufasa]

Craig,

Looks good ! Are you good to go, or do you need additional assistance from my side?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Dave,

For needs on this project, I'm good to go. I think that there is a definite place, though, for what you were working on (you still had a couple of things to add to it, as I recall), and I am sure that many people would benefit from it.

I appreciate all of your help, and I learned enough in the process to enable me to forge ahead with the Excel approach.

BTW, I *do* have a question -- we seem to be hitting a limit on the size of the extract files. We are using the following before the actual SELECT:

set termout off
set feedback off
set heading off
set long 32767
set longchunksize 32767
set lines 32767
set trimspool on
set pagesize 0
spool SQLExtract.txt

Craig