How can I use Oracle (PL/SQL) dynamic sql to query data into a %rowtype variable

Question

The problem:

I have a table that contains a CLOB which is a fixed length record from an external source. Where positions 1-5 = fieldA, positions 6-12 = fieldB, etc.

I have another table (LayoutDefinition)that defines that record layout, where

FieldName = 'fieldA'
FieldStart = 1
FieldLength = 5
FieldName = 'fieldB'
FieldStart = 6
FieldLength = 6

I need to retrieve the data from the CLOB and put it into a %rowtype variable such as "tableA_rec tableA%rowtype."

I have implemented a routine that uses a massive case statement, and a loop for each row in the LayoutDefiniton table moves the area of the CLOB into the proper variable in the tablea_rec like;

CASE LayoutDefiniton.FieldName
WHEN 'fieldA' THEN tablea_rec.fieldA:= SUBSTR(inputClob,LayoutDefiniton.FieldStart,LayoutDefiniton.FieldLength);
WHEN 'fieldB' THEN tablea_rec.fieldB:= SUBSTR(inputClob,LayoutDefiniton.FieldStart,LayoutDefiniton.FieldLength);

This of course is very inefficient, as I need to loop through my layout for each record picking apart my data.

What I would like to do is to create a dynamic sql select statement once that will retrieve the data from the table into the proper variables. For example if it were not dynamic it might look like;

select substr(inputCLOB,1,5), substr(inputCLOB,6,6) into FieldA, fieldB from CLOBTable;

Can this be done using Dynamic SQL?

If so what would the syntax look like?

How did you get the data in the first place? Was it delivered in a file? If so you should not put it in a CLOB use external tables or SQL*Loader instead. If it's received from another database look into DBMS_FILE_TRANSFER and DBMS_DATAPUMP... — Ben
It comes from another system, which uses Oracle. I could have the other system write the data to a file, and then import the data using sql*loader, but that requires manual intervention as well as a performance hit. The CLOB is over 20,000 bytes and contains hundreds of fields. — Paul Stearns
If it's in another Oracle database then I said you should consider DBMS_DATAPUMP... not SQL*Loader. 20,000 bytes really isn't that big, you're already reading and transferring the data; I'm just suggesting you do it in a manner which will give you a nice table at the end of it. Alternatively, you could just do a direct insert over a database link? — Ben
Sorry, It is in another schema within the same database. The other application builds a CLOB, because in most cases it outputs the data to be used in other systems. I found the CLOB by searching their schema. Rather than export the data and reimport it, I would like to take the data from the CLOB and put into the proper columns in a table in my schema. — Paul Stearns
The other application never persists the data in any other format other than the CLOB. — Paul Stearns

foobarbaz foobarbaz · Accepted Answer · 2013-08-26T16:47:45

The following code will extract the data from CLOB column and insert into the destination table

DECLARE
  l_sql_str VARCHAR2(4000);
BEGIN
  WITH src_meta_agg AS(
    SELECT LISTAGG(field_name, ',') WITHIN GROUP(ORDER BY field_start) AS field_list
           ,LISTAGG('substr(lob,' || field_start || ', ' || field_length || ')', ',') WITHIN GROUP( ORDER BY field_start) AS field_source
      FROM src_meta)
    SELECT 'INSERT INTO dest(' || field_list || ') SELECT ' || field_source ||
           ' FROM src' INTO l_sql_str
      FROM src_meta_agg;

  EXECUTE IMMEDIATE l_sql_str;
END;

LISTAGG is 11g function that orders the data and concatenates values. For previous versions you can use wm_concat or any other approach. In l_sql_str you will have the following statement

INSERT INTO dest(field1,field2) SELECT substr(lob,1, 5),substr(lob,6, 6) FROM src

If you need to process the data in PL/SQL this code will do the trick

DECLARE
  l_sql_str VARCHAR2(4000);
  TYPE t_dest_tbl IS TABLE OF dest%ROWTYPE;
  l_dest_rows t_dest_tbl;
  l_cursor SYS_REFCURSOR;
BEGIN

  WITH src_meta_agg AS(
    SELECT LISTAGG(field_name, ',') WITHIN GROUP(ORDER BY field_start) AS field_list
           ,LISTAGG('substr(lob,' || field_start || ', ' || field_length || ')', ',') WITHIN GROUP( ORDER BY field_start) AS field_source
      FROM src_meta)
    SELECT 'SELECT ' || field_source ||
           ' FROM src' INTO l_sql_str
      FROM src_meta_agg;

  OPEN l_cursor FOR l_sql_str;
  FETCH l_cursor
  BULK COLLECT INTO l_dest_rows;
  CLOSE l_cursor; 

  FOR i IN 1..l_dest_rows.COUNT LOOP
    dbms_output.put_line(l_dest_rows(i).field1 || ', ' || l_dest_rows(i).field2);
  END LOOP;
END;

Here is the schema I used

create table src(lob clob)
/
insert into src values ('12345ABCDEF')
/
insert into src values ('78901GHIJKL')
/

create table src_meta(field_name varchar2(100), field_start number, field_length number)
/

insert into src_meta values ('field1', 1, 5)
/
insert into src_meta values ('field2', 6, 6)
/

create table dest(field1 varchar2(5), field2 varchar2(6))
/

How can I use Oracle (PL/SQL) dynamic sql to query data into a %rowtype variable

1 Answers