4 Replies Latest reply: Sep 10, 2013 4:48 AM by Etbin RSS

    Reg: Extracting chars from flat file (SQL Loader) -

    ranit B

      Hi Experts,

       

      I'm trying to meet a typical problem of extracting 4000 characters from a field in flat file (Using SQL Loader). But this field (in flat file) is of length 15000 chars.

      So, SUBSTR(col_x, 1, 4000) is not able to handle this and records are getting inserted into bad file.

      (SQL function 'SUBSTR' can accept a maximum of 4000 input char length)

       

      I even tried REGEXP_REPLACE but no good.

      Also, this is not a fixed-width data file. So POSITION(x : y) can't be used.

       

      Could you please suggest any pointers for this?

       

      Help much appreciated.

      -- Ranit

        • 1. Re: Reg: Extracting chars from flat file (SQL Loader) -
          Etbin

          Never tried it (always used External Tables for that)

          Check Loading Objects, LOBs, and Collections specifically Loading LOBs - Loading LOB Data from a Primary Data File therein.

           

          Regards

           

          Etbin

          • 2. Re: Reg: Extracting chars from flat file (SQL Loader) -
            ranit B

            Thanks for responding, Etbin.

             

            Using External Table, will it fix my issue/concern. And how is the performance as compared to SQL Loader?

             

            Do you have any other 'creative' idea which can extract the first 4000 chars from the field?

             

            I'm thinking about converting it to CLOB data and use DBMS_LOB.SUBSTR. Will that help?

             

            -- Ranit


            • 3. Re: Reg: Extracting chars from flat file (SQL Loader) -
              BluShadow

              ranitB wrote:

               

              Thanks for responding, Etbin.

               

              Using External Table, will it fix my issue/concern. And how is the performance as compared to SQL Loader?

              External Tables use the same technology as SQL Loader under the hood, so the performance is equivalent.

               

               

               

              Do you have any other 'creative' idea which can extract the first 4000 chars from the field?

               

              I'm thinking about converting it to CLOB data and use DBMS_LOB.SUBSTR. Will that help?

               

              -- Ranit

               

              Try it and see.

              Like Etbin, I've never had source data provided with so much data in a single text field.  Typically such data is provided split into seperate rows and recombined on the database.

              Of course the other option would be to read the whole file as a clob and parse it using PL code, though that won't necessarily be as performant.

              • 4. Re: Reg: Extracting chars from flat file (SQL Loader) -
                Etbin
                Using External Table, will it fix my issue/concern. And how is the performance as compared to SQL Loader?

                Being server side IMHO External Tables are the best:

                • you can select from as from a normal table
                • a single table may contain data from several locations/files having the same structure of course
                • once you have the files on the server it's pretty fast (not as a normal table though, but when not having much rows might be used in queries directly)

                SQL*Loader and External Tables seem to use basically the same code to do their job, but how do you compare client side to server side ...

                Check https://forums.oracle.com/message/10473831#10473831

                Do you have any other 'creative' idea which can extract the first 4000 chars from the field?

                Sorry, never thought about that. Once I have the entire clob ...

                 

                I'm thinking about converting it to CLOB data and use DBMS_LOB.SUBSTR. Will that help?

                 

                It can be done for sure, but don't expect all that isolating rows and then columns within rows to be fast even when Using Oracle SecureFiles LOBs

                 

                Regards

                 

                Etbin