SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

handle tab delimited CLOB data with new line character

user11340233Feb 8 2016 — edited Feb 8 2016

Hi All,

I have a table with CLOB datatype column where my data is tab delimited. For the new line record i have CHR(10) as separator between the lines. Does anybody knows how to handle this character in order to select this data as separate lines.

Here is the example:

create table xx_test1(col1 clob);

insert into xx_test1 values (TO_CLOB('1'||chr(9)||'5467'||chr(9)||'41773'||chr(9)||'5467'||chr(9)||'169407'||chr(9)||'GBP'||chr(9)||'08-Feb-2016'||chr(9)||'08-Feb-2016'||chr(9)||'UK Accrual Invoice'||chr(9)||'UK Accrual - import'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'RDWHMP.03.00025.IND'||chr(9)||''||chr(9)||'300'||chr(9)||'15'||chr(9)||'Each'||chr(9)||'ZZU'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'PL Imported Invoices'||chr(9)||'119'||chr(9)||'229'||chr(9)||'340'||chr(9)||'450'||chr(9)||'560'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'Franchise COGS'||chr(9)||'12'||chr(9)||'13'||chr(9)||'14'||chr(9)||'15'||chr(9)||'Y'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'10'||chr(9)||'0'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''|| chr(10) || '2'||chr(9)||'5467'||chr(9)||'41773'||chr(9)||'5467'||chr(9)||'169407'||chr(9)||'GBP'||chr(9)||'08-Feb-2016'||chr(9)||'08-Feb-2016'||chr(9)||'UK Accrual Invoice'||chr(9)||'UK Accrual - import'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'RDWHMP.03.00025.IND'||chr(9)||''||chr(9)||'10'||chr(9)||'15'||chr(9)||'Each'||chr(9)||'ZZU'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'PL Imported Invoices'||chr(9)||'120'||chr(9)||'230'||chr(9)||'341'||chr(9)||'451'||chr(9)||'561'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'Franchise COGS'||chr(9)||'12'||chr(9)||'13'||chr(9)||'14'||chr(9)||'15'||chr(9)||'Y'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||'10'||chr(9)||'0'||chr(9)||''||chr(9)||''||chr(9)||''||chr(9)||''));

I'm trying this one but it doesn't work. The last column is not correct and it is picking up the value from the next line and also doesn't continue to the end(i presume it's due to hardcoded character position in regexp_substr). Any ideas how to handle it?

  WITH c_file_imp_data
         
          AS
          (SELECT dbms_lob.substr(col1, 32767, 1) src
             FROM xx_test1)
         
         SELECT
         --regexp_subsr is finding the position of the x occcurrance of a tab delimitter
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 1), chr(9))), --RECORD_ID
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 2), chr(9))), --BILL_TO_CUSTOMER_NUMBER,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 3), chr(9))), -- BILL_TO_LOCATION,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 4), chr(9))), -- SHIP_TO_CUSTOMER_NUMBER,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 5), chr(9))), -- SHIP_TO_LOCATION,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 6), chr(9)), -- CURRENCY,
          to_date(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 7), chr(9)),
                  'DD-MON-YYYY'), -- GL_DATE,
          to_date(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 8), chr(9)),
                  'DD-MON-YYYY'), -- TRANSACTION_DATE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 9), chr(9)), -- TRANSACTION_TYPE_NAME,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 10), chr(9)), -- TRANSACTION_SOURCE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 11), chr(9)), -- TERMS,
          to_date(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 12), chr(9)),
                  'DD-MON-YYYY'), -- DUE_DATE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 13), chr(9)), -- PAYMENT_METHOD,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 14), chr(9))), -- SALESREP_NUMBER,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 15), chr(9)), -- ITEM,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 16), chr(9)), -- DESCRIPTION,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 17), chr(9))), -- QUANTITY,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 18), chr(9))), -- UNIT_SELLING_PRICE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 19), chr(9)), -- UNIT_OF_MEASURE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 20), chr(9)), -- WAREHOUSE,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 21), chr(9))), -- TAX_RATE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 22), chr(9)), -- TAX_CODE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 23), chr(9)), -- GL_ACCOUNT_STRING,
          to_number(rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 24), chr(9))), -- CURRENCY_EXCHANGE_RATE,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 25), chr(9)), -- LINE_TRX_DFF_CONTEXT_VAL,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 26), chr(9)), -- LINE_TRANSACTION_FIELD1,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 27), chr(9)), -- LINE_TRANSACTION_FIELD2,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 28), chr(9)), -- LINE_TRANSACTION_FIELD3,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 29), chr(9)), -- LINE_TRANSACTION_FIELD4,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 30), chr(9)), -- LINE_TRANSACTION_FIELD5,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 31), chr(9)), -- LINE_TRANSACTION_FIELD6,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 32), chr(9)), -- LINE_TRANSACTION_FIELD7,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 33), chr(9)), -- LINE_TRANSACTION_FIELD8,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 34), chr(9)), -- LINE_TRANSACTION_FIELD9,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 35), chr(9)), -- LINE_TRANSACTION_FIELD10,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 36), chr(9)), -- LINE_TRANSACTION_FIELD11,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 37), chr(9)), -- LINE_TRANSACTION_FIELD12,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 38), chr(9)), -- LINE_TRANSACTION_FIELD13,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 39), chr(9)), -- LINE_TRANSACTION_FIELD14,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 40), chr(9)), -- LINE_TRANSACTION_FIELD15,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 41), chr(9)), -- INV_LINE_INFO_DFF_CONT_VAL,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 42), chr(9)), -- NO_ANIMALS,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 43), chr(9)), -- MX_WEIGHT,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 44), chr(9)), -- MX_SLAUGHTER,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 45), chr(9)), -- REBILLED,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 46), chr(9)), -- NON_STAT,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 47), chr(9)), -- ATTRIBUTE12,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 48), chr(9)), -- ATTRIBUTE13,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 49), chr(9)), -- ATTRIBUTE14,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 50), chr(9)), -- ATTRIBUTE15,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 51), chr(9)), -- ATTRIBUTE8,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 52), chr(9)), -- ATTRIBUTE11,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 53), chr(9)), -- ATTRIBUTE2,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 54), chr(9)), -- ATTRIBUTE3,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 55), chr(9)), -- ATTRIBUTE4,
          rtrim(regexp_substr(src, '[^' || chr(9) || ']*' || chr(9) || '', 1, 56), chr(9)) -- ATTRIBUTE9
        
           FROM c_file_imp_data

Oracle DB version: 12c

Thanks in advance,

Alex

This post has been answered by Etbin on Feb 8 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 7 2016

Added on Feb 8 2016

3 comments

1,158 views