This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 11, 2013 4:44 AM by ascheffer RSS

Need help to query flat_file data from oracle table from clobdata type.

977256 Newbie
Currently Being Moderated
Hi Sir,

I need help to query flat-file data from oracle table having clob data type.
Oracle Version:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
"CORE     10.2.0.1.0     Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



Source table

  CREATE TABLE order_details 
   (     QUEUE_SEQNUM NUMBER(10,0) NOT NULL ENABLE, 
     LINE_SEQNUM NUMBER(10,0) NOT NULL ENABLE, 
     CHAR_DATA CLOB, 
     OPTIMISTIC_LOCK_KEY NUMBER(20,0)
   ) 
FLAT_FILE FOR CHAR_DATA COLUMN
EU,6067AT,AT10,000000402004,NexiumGERDManagementProject,Z435,,ZZ29,NIS-GOLD,AT
EU,6067AT,AT10,000000402038,NIS-OEU-ARI-2007/1,Z450,,ZZ29,NIS-OEU-ARI-2007/1,AT
EU,6067AT,AT10,000000402039,SymbicortNISinCOPD,Z450,,ZZ29,NIS-REU-DUM-2007/1,AT
EU,6067AT,AT10,000000402040,D1443L00044SeroquelXRRuby,Z450,,ZZ29,D1443L00044,AT
EU,6067AT,AT10,000000402041,NIS-GEU-DUM-2008/1,Z450,,ZZ29,NIS-GEU-DUM-2008/1,AT
EU,6067AT,AT10,000000402042,SonstigeAktivitätenLCM,Z450,,ZZ29,.,AT
EU,6067AT,AT10,000000402134,D1680L00002Saxagliptin,Z450,,ZZ29,D1680L00002,AT
EU,6067AT,AT10,000000402199,SeroquelWaveNIS,Z450,,ZZ29,NIS-NEU-DUM-2009/1,AT
EU,6067AT,AT10,000000402313,SeroquelExtra(D1443L00082),Z450,,ZZ29,D1443L00082,AT
EU,6067AT,AT10,000000402517,AtlanticD5130L00006(AZD6140),Z450,,ZZ29,D5130L00006,AT
EU,6067AT,AT10,000000554494,ArimidexSt.Gallen(13+2),Z142,,ZZ09,,AT
EU,6067AT,AT10,000000554495,ArimidexASCO(5delegates),Z142,,ZZ09,,AT
EU,6067AT,AT10,000000554496,ArimidexSanAntonio6delegates,Z142,,ZZ09,,AT
EU,6067AT,AT10,000000554497,ArimidexBreastCancerSummit(13+2),Z130,,ZZ09,,AT
EU,6067AT,AT10,000000554498,ArimidexEIH(15delegates),Z130,,ZZ09,,AT
EU,6067AT,AT10,000000554499,ArimidexNIFA(200delegates),Z135,,ZZ09,,AT
EU,6067AT,AT10,000000554500,ArimidexNIFAworkshops(8x25),Z135,,ZZ09,,AT
EU,6067AT,AT10,000000554501,ArimidexPraktischeGyn.Fortbildung,Z147,,ZZ09,,AT
EU,6067AT,AT10,000000554502,ArimidexAGO,Z147,,ZZ09,,AT
EU,6067AT,AT10,000000554503,ArimidexHämato/OnkologieKongress,Z147,,ZZ09,,AT
EU,6067AT,AT10,000000554504,ARIMIDEXGYNäKOLOGENKONGRESS,Z147,,ZZ09,,AT
EU,6067AT,AT10,000000554505,ArimidexChirurgenkongress,Z147,,ZZ09,,AT
EXPECTED OUTPUT:
AFFIRM_CODE COMPANY_CODE INTERNAL_ORDER_CODE INTERNAL_ORDER_DESC ENIGMA_ACTIVITY             SUB_ACTIVITY_CODE IN_AFF_IND ORDER_TYPE EXTERNAL_ORDER COUNTRY        
EU          6067AT       AT10                 000000402004       NEXIUMGERDMANAGEMENTPROJECT     Z435           NULL        ZZ29       NIS-GOLD        AT             
EU          6068AT       AT11                 000000402005       NEXIUMGERDMANAGEMENTPROJECT     Z435           NULL        ZZ29       NIS-GOLD        AT             
  • 1. Re: Need help to query flat_file data from oracle table from clobdata type.
    sb92075 Guru
    Currently Being Moderated
    http://www.oracle.com/pls/db112/search?word=external+table&partno=
  • 2. Re: Need help to query flat_file data from oracle table from clobdata type.
    Etbin Guru
    Currently Being Moderated
    Take a look at [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lob.htm#ARPLS66598]dbms_lob package - contains loadclobfromfile procedure

    Regards

    Etbin

    Edited by: Etbin on 2.2.2013 16:28
    Rereading your post and the one above seems that querying a clob would not be very efficient.
  • 3. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Hi ,

    Thanks...For the link, I have inserted the flat file into oracle column as clob datatype already. Link is related to external table which you provided..:(

    But now I am bound fetch data in SQL query as above given format to match with another target table for data-validation point of view.

    Please help
  • 4. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Etbin,

    Please help me to get data with SQL query..
  • 5. Re: Need help to query flat_file data from oracle table from clobdata type.
    Etbin Guru
    Currently Being Moderated
    In your situation, I'd follow sb92075 suggestion
    - check {message:id=10731799} on how to write the clob back to flat file (a directory object must exist pointing to an OS directory on the server hosting the Database ...)
    - create an External Table (see the link sb92075 provided) over that flat file and you'll achieve what you're aiming at.
    - if the use for validation purposes turns out to be too slow you could insert the External Table data into a normal table (or a GTT) as those can be indexed

    the alternative would be to set up a sql that breaks the clob into lines first (+newline+ characters seem to be there) and then every line into columns using some string-to-columns techniques {message:id=10698577} in both cases to end up with a volatile cursor yet to be used in some validation process.

    Regards

    Etbin
  • 6. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Actually I can not create external tables.
    I tell you once again my problem ,I have a client table "Order_details" in which all client orders in a flat-files will come with clob data-type.

    so at a time there might be many flat-file in Order-details and hundreds of records in them collectively.

    Now these flat files will be loaded into some other tables say target_table with respective columns with informatica tool.


    My business requirement is to test these table for data validation as below.

    select a,b,c,d from Order_details
    Minus
    select a,b,c,d from Target_table;


    Impediments in this process:

    1- I am not allowed to write any PL-SQL packages for simple Testing.
    2- I don't have privileges to create DIR for external table.


    So expected help:
    1-Need a way to fetch flat-files first from table
    2-then break them into lines one by one
    line1
    line2
    line3
    and so on;
    3- then I need to break lines into columns as
    line1- col1,col2,col3,col4 .... colN
    line2- col1,col2,col3,col4 .... colN

    Now once I achieve
    select col1,col2,col3,col4 .... colN from Order_details;
    from
    select char_data from Order_details;
    then I can do my testing for these table.
  • 7. Re: Need help to query flat_file data from oracle table from clobdata type.
    Etbin Guru
    Currently Being Moderated
    NOT TESTED! No Database at hand
    Start playing with something like
    /* windows */
    select level row_no,regexp_substr(char_data,'[^' || chr(13) || chr(10) || ']+',1,level) the_line
      from order_details
     connect by level <= (length(char_data) - length(replace(char_data,chr(13) || chr(10)))) / 2
    
    /* unix, linux */
    select level row_no,regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) the_line
      from order_details
     connect by level <= length(char_data) - length(replace(char_data,chr(10)))
    to find out if that returns all the lines from your clob.
    If something goes wrong try
    select level row_no,regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) the_line
      from order_details
     connect by regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) is not null 
         and prior sys_guid() is not null 
    the last condition might not be necessary as you'll be dealing with a single clob
    Once you make it work do the same with the_line using comma as a delimiter of course

    Regards

    Etbin
  • 8. Re: Need help to query flat_file data from oracle table from clobdata type.
    rp0428 Guru
    Currently Being Moderated
    >
    Thanks...For the link, I have inserted the flat file into oracle column as clob datatype already. Link is related to external table which you provided..:(

    But now I am bound fetch data in SQL query as above given format to match with another target table for data-validation point of view.
    . . .
    I tell you once again my problem ,I have a client table "Order_details" in which all client orders in a flat-files will come with clob data-type.

    so at a time there might be many flat-file in Order-details and hundreds of records in them collectively.

    Now these flat files will be loaded into some other tables say target_table with respective columns with informatica tool.

    My business requirement is to test these table for data validation as below.

    select a,b,c,d from Order_details
    Minus
    select a,b,c,d from Target_table;


    Impediments in this process:

    1- I am not allowed to write any PL-SQL packages for simple Testing.
    2- I don't have privileges to create DIR for external table.


    So expected help:
    1-Need a way to fetch flat-files first from table
    2-then break them into lines one by one
    line1
    line2
    line3
    and so on;
    3- then I need to break lines into columns as
    line1- col1,col2,col3,col4 .... colN
    line2- col1,col2,col3,col4 .... colN
    >
    I have to agree with the others that you need to rethink the architecture of this.

    You are violating the most fundamental rule of ETL processing: NEVER throw information away until or unless it is no longer needed.

    Based on what you posted it appears that the process starts with flat delimited files where the records are delimited by LF or CR/LF pairs and the fields are separated by commas (','). You don't indicate whether any of the data might have embedded separators or delimiters that need to be dealt with. Can a name field contain a LF or CR/LF? Can it contain a comma (e.g. "Tiger, Scott").

    By storing the data for the entire file in a single CLOB you are throwing away the information about the record delimiters.

    File processing is the SLOWEST part of an ETL process (other than sorting). You generally want to perform as much parsing and processing as possible while the file is being read.

    Thus your load process should be parsing the file into indidual records and further processing those records into individual fields. The process code should then perform basic validation on those individual records and fields, load the good data into a DB table and save the bad data in to an error table while creating log records that identify exactly what errors occur in the data and what record they occur in.

    That basic validation is what would verify that DATE data actually contains dates and not garbage and that numeric data really is numeric.

    If absolutely necessary your parser/loader could create a number of VARCHAR2(500) columns (depending on the max number of data fields in a record) and parse the rows and columns into a generic table that has a file_name column and a record_number column.

    That loaded table is where further processing would be done.

    Simply doing a bulk load of a file into a CLOB is wasting the best opportunity you have for getting the best performance from your ETL process. All of you parsing can be done on-the-fly while the data is being loaded with no performance penalty at all.

    Since you said
    >
    I have inserted the flat file into oracle column as clob datatype already
    >
    then it is not too late to fix your architecture problem and parse and insert the data into a more normalilzed form of a separate row for each data file row and a separate column for each field in each record.

    Java already has simple functionality for reading text file. You could

    1. Use the LineNumberReader class to read your flat files.
    >
    readLine
    public String readLine()
    throws IOExceptionRead a line of text. A line is considered to be terminated by any one of a line feed ('\n'), a carriage return ('\r'), or a carriage return followed immediately by a linefeed.
    >
    2. Use the String.split method to split each record by a regular expression or just simple commas
    >
    split
    public String[] split(String regex)Splits this string around matches of the given regular expression.
    This method works as if by invoking the two-argument split method with the given expression and a limit argument of zero. Trailing empty strings are therefore not included in the resulting array.

    The string "boo:and:foo", for example, yields the following results with these expressions:

    Regex Result
    : { "boo", "and", "foo" }
    o { "b", "", ":and:f" }
    >
    3. Bind each of the fields to a bind variable in a PreparedStatement and JDBC batches to insert the data into a common DB table with each field in its own column.
  • 9. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Hi Etbin thanks for the queries...I appreciate the efforts you are doing...

    I have tried them these are working great when tables has one clob. but in actual case there may be many clob at a time in Order_Details table.

    So If I am running below query ..then it is giving only first line from a flat-flle . if 3 flat files are there then I am getting 3 first lines of those. :(
    but If one clob is there then I am getting all records from that lat file+
     select level row_no,regexp_substr(char_data,'[^' || chr(13) || chr(10) || ']+',1,level) the_line
      from order_details
     connect by level <= (length(char_data) - length(replace(char_data,chr(13) || chr(10)))) / 2
    I know this might not be the good practice by ETL point of view...but I m bound to do the things as per instructions, I can't do anything about it.

    Edited by: 974253 on Feb 2, 2013 11:00 PM
  • 10. Re: Need help to query flat_file data from oracle table from clobdata type.
    Etbin Guru
    Currently Being Moderated
    Sorry, my bad. With no Database at hand I'm trying "baby steps" (borrowed from Frank) in order to not confuse you with errors I might add (it happens way too often already but at least you'll not be "spoon fed" as many forum members think is defeating one of it's principal purposes - help to learn - not just providing the proverbial fish.
    Search the forum - your problem is one of it's bestsellers. Looking at {message:id=10694602} ("split string into" was the keyword used in Search) you can try something as
    select table_row,
           level clob_row,
           regexp_substr(char_data,'[^' || chr(13) || chr(10) || ']+',1,level) the_line
      from (select to_char(queue_seqnum)||':'||to_char(line_seqnum) table_row,
                   char_data
              from order_details
           )
     connect by regexp_substr(char_data,'[^' || chr(13) || chr(10) || ']+',1,level) is not null
            and prior char_data = char_data
            and prior table_row = table_row
            and prior sys_guid() is not null
    to obtain all <tt>the_line</tt>s from all clobs and after that using the same example to get your columns from each <tt>the_line</tt>.

    Regards

    Etbin

    Edited by: Etbin on 3.2.2013 9:01
    ...but I m bound to do the things as per instructions, I can't do anything about it.
    Used to happen to me too and I did it as being told to but only after explaining all possible drawbacks I was aware of at the time. The last sentence was usually: "O.K. now be fair and don't come back with this stuff when it turns out it's not the right thing".
    rp0428's post - something to be remembered.
  • 11. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Great Etbin,

    Many thanks for your precious efforts.

    I have modified your earlier query ..I got all the lines from various flat-files in below format, Now I can go for step2 splitting rows into columns which is simple now.

    but without your help it wasn't possible.
    select level row_no,regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) the_line
      from (select rtrim(xmlagg(xmlelement(e, char_data || ''||chr(10)||'')).extract('//text()').extract('//text()') ,',') char_data
     from AD_IN_MESSAGEDETAIL)
     connect by level <= length(char_data) - length(replace(char_data,chr(10)))
    
    Output for 3 flat_files present.
    
    ROW_NO THE_LINE                                                                               
    ------ ----------------------------------------------------------------------------
         1 EU,6067AT,AT10,000000554506,ArimidexFrauengesundheitstag,Z147,,ZZ09,,AT                
         2 EU,6067AT,AT10,000000554507,ArimidexChirurgentag,Z147,,ZZ09,,AT                        
         3 EU,6067AT,AT10,000000554508,ArimidexSenologiekongress,Z147,,ZZ09,,AT                   
         4 EU,6067AT,AT10,000000554509,ArimidexABCSGStudiensitzung,Z147,,ZZ09,,AT                 
         5 EU,6067AT,AT10,000000554510,ArimidexKonsensus(adjuvanttherapy),Z180,,ZZ09,,AT          
         6 EU,6067AT,AT10,000000554511,Arimidexregionalactivities,Z235,,ZZ09,,AT                  
         7 EU,6067AT,AT10,000000554512,ArimidexKOLsupport,Z120,,ZZ09,,AT                          
         8 EU,6067AT,AT10,000000554513,ArimidexATAC5ySpringer(withGermany),Z155,,ZZ09,,AT         
         9 EU,6067AT,AT10,000000554514,ArimidexATAC5yKrebshilfeYellowpage,Z155,,ZZ09,,AT          
        10 EU,6067AT,AT10,000000554515,ArimidexATAC5xresultsKrebshilfe,Z155,,ZZ09,,AT             
        11 EU,6067AT,AT10,000000554516,ArimidexInserat(ASCO)Universum,Z155,,ZZ09,,AT              
        12 EU,6067AT,AT10,000000554517,ArimidexPressconference(St.Gallen),Z155,,ZZ09,,AT          
        13 EU,6067AT,AT10,000000554518,ArimidexGynSpektrum(4/year),Z150,,ZZ09,,AT                 
        14 EU,6067AT,AT10,000000554519,ArimidexEndocrinerelatedcancer,Z150,,ZZ09,,AT              
        15 EU,6067AT,AT10,000000554520,ArimidexStehkalender,Z150,,ZZ09,,AT                        
        16 EU,6067AT,AT10,000000554521,ArimidexpublicationsATACABCSG8,Z150,,ZZ09,,AT              
        17 EU,6067AT,AT10,000000554522,ArimidexATACABCSG8Exhibstand,Z150,,ZZ09,,AT                
        18 EU,6067AT,AT10,000000554523,ArimidexMaritimerworkshop,Z142,,ZZ09,,AT                   
        19 EU,6067AT,AT10,000000554501,ArimidexPraktischeGyn.Fortbildung,Z147,,ZZ09,,AT           
        20 EU,6067AT,AT10,000000402004,NexiumGERDManagementProject,Z435,,ZZ29,NIS-GOLD,AT         
        21 EU,6067AT,AT10,000000402038,NIS-OEU-ARI-2007/1,Z450,,ZZ29,NIS-OEU-ARI-2007/1,AT        
        22 EU,6067AT,AT10,000000402039,SymbicortNISinCOPD,Z450,,ZZ29,NIS-REU-DUM-2007/1,AT        
        23 EU,6067AT,AT10,000000402040,D1443L00044SeroquelXRRuby,Z450,,ZZ29,D1443L00044,AT        
        24 EU,6067AT,AT10,000000402041,NIS-GEU-DUM-2008/1,Z450,,ZZ29,NIS-GEU-DUM-2008/1,AT        
        25 EU,6067AT,AT10,000000402042,SonstigeAktivitätenLCM,Z450,,ZZ29,.,AT                    
        26 EU,6067AT,AT10,000000402134,D1680L00002Saxagliptin,Z450,,ZZ29,D1680L00002,AT           
        27 EU,6067AT,AT10,000000402199,SeroquelWaveNIS,Z450,,ZZ29,NIS-NEU-DUM-2009/1,AT           
        28 EU,6067AT,AT10,000000402313,SeroquelExtra(D1443L00082),Z450,,ZZ29,D1443L00082,AT       
        29 EU,6067AT,AT10,000000402517,AtlanticD5130L00006(AZD6140),Z450,,ZZ29,D5130L00006,AT     
        30 EU,6067AT,AT10,000000554494,ArimidexSt.Gallen(13+2),Z142,,ZZ09,,AT                     
        31 EU,6067AT,AT10,000000554495,ArimidexASCO(5delegates),Z142,,ZZ09,,AT                    
        32 EU,6067AT,AT10,000000554496,ArimidexSanAntonio6delegates,Z142,,ZZ09,,AT                
        33 EU,6067AT,AT10,000000554497,ArimidexBreastCancerSummit(13+2),Z130,,ZZ09,,AT            
        34 EU,6067AT,AT10,000000554498,ArimidexEIH(15delegates),Z130,,ZZ09,,AT                    
        35 EU,6067AT,AT10,000000554499,ArimidexNIFA(200delegates),Z135,,ZZ09,,AT                  
        36 EU,6067AT,AT10,000000554500,ArimidexNIFAworkshops(8x25),Z135,,ZZ09,,AT                 
        37 EU,6067AT,AT10,000000554501,ArimidexPraktischeGyn.Fortbildung,Z147,,ZZ09,,AT           
        38 EU,6067AT,AT10,000000554502,ArimidexAGO,Z147,,ZZ09,,AT                                 
        39 EU,6067AT,AT10,000000554503,ArimidexHämato/OnkologieKongress,Z147,,ZZ09,,AT           
        40 EU,6067AT,AT10,000000554504,ArimidexGynäkologenkongress,Z147,,ZZ09,,AT                
        41 EU,6067AT,AT10,000000554505,ArimidexChirurgenkongress,Z147,,ZZ09,,AT                   
    
     41 rows selected 
  • 12. Re: Need help to query flat_file data from oracle table from clobdata type.
    odie_63 Guru
    Currently Being Moderated
    974253 wrote:
    I have modified your earlier query ..I got all the lines from various flat-files in below format, Now I can go for step2 splitting rows into columns which is simple now.
    This will fail as soon as you cross the VARCHAR2 boundary, i.e. 4000 bytes of aggregated data :
    SQL> select sum(length(char_data)) from order_details;
    
    SUM(LENGTH(CHAR_DATA))
    ----------------------
                      4880
    
    SQL> select level row_no,regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) the_line
      2    from (select rtrim(xmlagg(xmlelement(e, char_data || ''||chr(10)||'')).extract('//text()').extract('//text()') ,',') char_data
      3   from order_details)
      4   connect by level <= length(char_data) - length(replace(char_data,chr(10)));
    select level row_no,regexp_substr(char_data,'[^' || chr(10) || ']+',1,level) the_line
    *
    ERROR at line 1:
    ORA-19011: Character string buffer too small
    All that XML stuff should only be used by people understanding it.
    Had it been the case here, you would have removed the unnecessary comma-TRIMming part, the double EXTRACT call and use the proper getter function to handle CLOB correctly :
    select level row_no
         , regexp_substr(char_data, '[^' || chr(10) || ']+', 1, level) the_line
    from (
      select extract(
               xmlagg(
                 xmlelement(e, char_data || chr(10))
               )
             , '//text()'
             ).getclobval() as char_data
      from order_details
    )
    connect by level <= length(char_data) - length(replace(char_data, chr(10)))
  • 13. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks Sir you always ..go beyond the limitations :)
    FOR Lcntr IN 1..1000
    LOOP
    
    *Thank you!!! Odie*
    
    END LOOP;
    ;) :D
  • 14. Re: Need help to query flat_file data from oracle table from clobdata type.
    977256 Newbie
    Currently Being Moderated
    Hi Odie,

    I have tried above clob query which you have corrected, on my client table Order_details..and seems it has more clob data(flat-files) as I was thinking initially.

    Now I am getting ORA -31167 , please suggest further how to tackle this situation.
    Error report:
    SQL Error: ORA-31167: XML nodes over 64K in size cannot be inserted
    31167. 00000 -  "XML nodes over 64K in size cannot be inserted"
    *Cause:    An attempt was made to insert an XML Text Node with a size
               greater than 64K.  This is not supported.
    *Action:   Create text nodes under 64K.
    Regards
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points