Forum Stats

  • 3,735,128 Users
  • 2,247,116 Discussions
  • 7,857,698 Comments

Discussions

Select and load CLOB column data into another table

carmac
carmac Member Posts: 117
edited Aug 19, 2020 5:55AM in SQL & PL/SQL

Hi All,

please suggest me how to extract and load below type of data into another table if the column data type is CLOB. some of the rows length is more than 12000 bytes or characters.

with

t (clob_column) as (

select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

select 'CUST_NAME|value2|CUST_DEPT|value3|' from dual union all

select 'CUST_ID|value1|ITEM_ID|value4|' from dual union all

select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

select 'ITEM_ID|value4|' from dual union all

select 'CUST_DEPT|value3|' from dual union all

select 'CUST_ID|value1|CUST_DEPT|value3|' from dual union all

select 'CUST_NAME|value2|' from dual union all

select 'CUST_DEPT|value3|CUST_ID0|value10|' from dual

)

select * from t;

Thanks,

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,005 Red Diamond
    edited Aug 19, 2020 4:24AM

    What's wrong with just insert ... select ... statement? or create table as select ... (if you're "another table" doesn't already exist).

    What have you tried, and what's not working for you?

    (and p.s. the datatype of your example data is VARCHAR2, not CLOB - you've done nothing to provide the data as CLOB datatype in that)

  • BluShadow
    BluShadow Member, Moderator Posts: 41,005 Red Diamond
    edited Aug 19, 2020 4:27AM

    Here we go, took me less than 1 minute to test...

    SQL> create table mytable1 (clob_column clob);Table created.SQL> ed
    Wrote file afiedt.buf  1  insert into mytable1
      2  select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      3  select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      4  select 'CUST_NAME|value2|CUST_DEPT|value3|' from dual union all
      5  select 'CUST_ID|value1|ITEM_ID|value4|' from dual union all
      6  select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      7  select 'ITEM_ID|value4|' from dual union all
      8  select 'CUST_DEPT|value3|' from dual union all
      9  select 'CUST_ID|value1|CUST_DEPT|value3|' from dual union all
    10  select 'CUST_NAME|value2|' from dual union all
    11* select 'CUST_DEPT|value3|CUST_ID0|value10|' from dual
    SQL> /10 rows created.SQL> create table mytable2 (clob_column clob);Table created.SQL> insert into mytable2 select * from mytable1;10 rows created.
  • carmac
    carmac Member Posts: 117
    edited Aug 19, 2020 4:45AM

    Sorry, I missed my required output format. I want to retrieve data like below and load this data into another table with below four columns.

    please suggest me how to extract and load below type of data into another table if the column data type is CLOB. some of the rows length is more than 12000 bytes or characters.

    with

    t (clob_column) as (

    select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

    select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

    select 'CUST_NAME|value2|CUST_DEPT|value3|' from dual union all

    select 'CUST_ID|value1|ITEM_ID|value4|' from dual union all

    select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

    select 'ITEM_ID|value4|' from dual union all

    select 'CUST_DEPT|value3|' from dual union all

    select 'CUST_ID|value1|CUST_DEPT|value3|' from dual union all

    select 'CUST_NAME|value2|' from dual union all

    select 'CUST_DEPT|value3|CUST_ID0|value10|' from dual

    )

    select * from t;

    CUST_ID       CUST_NAME          CUST_ DEPT  ITEM_ID

    --------      --------         --------      --------

    value1        value2             value3        value4

    value1                            value3        value4

                   value2             value3

    value1                                         value4

                   value2             value3        value4

                                                     value4

                                        value3     

    value1                            value3  

  • BluShadow
    BluShadow Member, Moderator Posts: 41,005 Red Diamond
    edited Aug 19, 2020 5:55AM

    So, something like this?

    SQL> with t (clob_column) as (
      2  select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      3  select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      4  select 'CUST_NAME|value2|CUST_DEPT|value3|' from dual union all
      5  select 'CUST_ID|value1|ITEM_ID|value4|' from dual union all
      6  select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
      7  select 'ITEM_ID|value4|' from dual union all
      8  select 'CUST_DEPT|value3|' from dual union all
      9  select 'CUST_ID|value1|CUST_DEPT|value3|' from dual union all
    10  select 'CUST_NAME|value2|' from dual union all
    11  select 'CUST_DEPT|value3|CUST_ID0|value10|' from dual
    12  )
    13  select regexp_replace(clob_column, '.*(^|\|)CUST_ID\|([^|]+)(\||$).*|.*', '\2') as cust_id
    14        ,regexp_replace(clob_column, '.*(^|\|)CUST_NAME\|([^|]+)(\||$).*|.*', '\2') as cust_name
    15        ,regexp_replace(clob_column, '.*(^|\|)CUST_DEPT\|([^|]+)(\||$).*|.*', '\2') as cust_dept
    16        ,regexp_replace(clob_column, '.*(^|\|)ITEM_ID\|([^|]+)(\||$).*|.*', '\2') as item_id
    17  from t;CUST_ID    CUST_NAME  CUST_DEPT  ITEM_ID
    ---------- ---------- ---------- ----------
    value1     value2     value3     value4
    value1                value3     value4
               value2     value3
    value1                           value4
               value2     value3     value4
                                     value4
                          value3
    value1                value3
               value2
                          value310 rows selected.

    (Note sure I've got it completely optimised, but I'm rushing as I've work to get on with)

Sign In or Register to comment.