6 Replies Latest reply: Jan 11, 2014 6:42 PM by rp0428 RSS

Dynamic Insert - Record Type Object

Lokesh Kumar C Newbie
Currently Being Moderated

Hi Friends,

 

I need your help in creating dynamic insert statements.

 

Let me explain my scenario.

 

I am trying to load a feed into stage and finally into target table in our data warehouse system.

 

We have a table which maps stage columns to fact table columns based on the feed.


Say emp_stage is stage table and emp_fact is fact/target table name. flex_field1 in emp_stage stage table will map to emp_name in emp_fact . Also we load the data from stage table to a record type stage_rec. Then we cleanse data as per business rule and finally we move data from stage_rec to a record type prod_rec. So final step is to load data from prod_rec to the fact table emp_fact.


Now I have the below data

1. String which has the skeleton - 'insert into emp_fact(emp_name) values(<prod_rec.flex_field1>)'

2. Record Type Object - PROD_REC

 

How to generate below insert dynamically ? Say emp_rec.flex_field1 value is 'lokesh' then my insert should look like below

 

insert into emp_fact(emp_name) values('lokesh')

 

Thanks,

Lokesh

  • 1. Re: Dynamic Insert - Record Type Object
    Lokesh Kumar C Newbie
    Currently Being Moderated

    Hi Friends,

     

    Just an update to my above questions.

     

    I would basically need a replace statement

     

    replace('insert into emp_fact(emp_name) values(<prod_rec.flex_field1>)',<prod_rec.flex_field1>,'lokesh');

     

    The replace value is what I need to dynamically create .

     

    Thanks,

    Lokeh

  • 2. Re: Dynamic Insert - Record Type Object
    Stew Ashton Expert
    Currently Being Moderated

    I really think you do not want to do that.

     

    First of all you seem to be loading data one record at a time. This is hundreds of times slower than loading many records at a time.

     

    Second, if you put the value "lokesh" in the SQL statement itself, the SQL text changes and the optimizer has to reparse the statement. This will slow things down even more.

  • 3. Re: Dynamic Insert - Record Type Object
    Stew Ashton Expert
    Currently Being Moderated

    Here are some examples of what to do or not to do. First, test data:

     

    drop table t_stage;
    create table t_stage as
    select owner, object_name, object_id, object_type, created, namespace
    from all_objects;
    drop table t_target;
    create table t_target as
    select owner dept, object_name ename, object_id empno, object_type job, created hiredate, namespace sal
    from t_stage
    where 1=0;
    

     

    I created a little over 90,000 rows.

     

    Now I'm going to update t_target using your idea: row-by-row processing with values concatenated into the SQL:

     

    declare
      rec_t t_target%rowtype;
      c_sql constant varchar2(256) := q'<insert into t_target values('#1','#2',#3,'#4',to_date('#5'),#6)>';
      l_sql varchar2(4000);
    begin
      for rec in (select * from t_stage) loop
        -- do your processing here
        l_sql := replace(c_sql,'#1', rec.owner);
        l_sql := replace(l_sql,'#2', rec.object_name);
        l_sql := replace(l_sql,'#3', rec.object_id);
        l_sql := replace(l_sql,'#4', rec.object_type);
        l_sql := replace(l_sql,'#5', to_char(rec.created));
        l_sql := replace(l_sql,'#6', rec.namespace);
        execute immediate l_sql;   
      end loop;
    end;
    /
    

     

    This took about 70 seconds to execute.

     

    Now here's a slightly better example: I am still doing row-by-row processing, but I am using bind variables so there is no excessive parsing.

     

    declare
      rec_t t_target%rowtype;
      c_sql constant varchar2(256) := q'<insert into t_target values(:a,:b,:c,:d,:e,:f)>';
    begin
      for rec in (select * from t_stage) loop
        -- do your processing here
        execute immediate c_sql using
         rec.owner,
         rec.object_name,
         rec.object_id,
         rec.object_type,
         rec.created,
         rec.namespace;
      end loop;
    end;
    /
    

     

    This took about 6 seconds.

     

    Now here's what you should be doing, provided your "processing" can be done using SQL:

     

    insert into t_target
    select * from t_stage;
    

     

    This took less than a second.

     

    If you really need to do some kind of processing, then use a pipelined function.

     

    create or replace package body stage_to_target as
      function transform return tt_target pipelined is
        rec_targ t_target%rowtype;
      begin
        for rec in (select * from t_stage) loop
        -- do your processing here
          rec_targ.dept := rec.owner;
          rec_targ.ename := rec.object_name;
          rec_targ.empno := rec.object_id;
          rec_targ.job := rec.object_type;
          rec_targ.hiredate := rec.created;
          rec_targ.sal := rec.namespace;
          pipe row(rec_targ);
        end loop;
      return;
      end transform;
    end stage_to_target;
    /
    insert into t_target
    select * from table(stage_to_target.transform);
    

     

    This also took less than a second.

     

    Please note that I took some shortcuts (like using "*") so this code does not follow all best practices. Also, you could use the /*+ append */ hint in the last two inserts to go even faster, and you could integrate parallelism.

  • 4. Re: Dynamic Insert - Record Type Object
    rp0428 Guru
    Currently Being Moderated
    I would basically need a replace statement

     

    replace('insert into emp_fact(emp_name) values(<prod_rec.flex_field1>)',<prod_rec.flex_field1>,'lokesh');

     

    The replace value is what I need to dynamically create .

    No - you do NOT need or want to do that dynamically. The core part of what Stew is saying is to get rid of the dynamic part of the query and use a row-level object to capture the data.

     

    Here is another way of showing that (the code is for EXAMPLE only):

    create or replace package body stage_to_target as

      procedure transform is

          rec_targ t_target%rowtype;

          t_targ tt_target;

      begin

          for rec in (select * from t_stage) loop

              -- do your processing here

              rec_targ.dept := rec.owner;

              rec_targ.ename := rec.object_name;

              rec_targ.empno := rec.object_id;

              rec_targ.job := rec.object_type;

              rec_targ.hiredate := rec.created;

              rec_targ.sal := rec.namespace;

              t_targ.extend();

              t_targ(t_targ.count) := rec_targ;

          end loop;

          forall i in 1..t_targ.count

             insert into t_target values t_targ(i);

      end transform;

    end stage_to_target;

    /

    See the INSERT statement at the end? It is NOT dynamic; it uses a row-image everytime time but a different one each time through the loop.

     

    You would also NOT use that type of loop and do everything in one complex piece of code.

     

    A modular architecture separates the components of the process into smaller building blocks:

    1. a data source

    2. one or more transform functions

    3. a data sink

     

    A simplistic example would modify the above to something more like:

          rec_targ t_target%rowtype;

          tt_stage is table of t_stage%rowtype;

          t_stage tt_stage;

          t_targ tt_target;

      begin

          select * bulk collect into tt_stage; -- in practice this would use a LIMIT clause

          -- call transform #1

          t_targ := transform1(t_stage, t_targ); -- this would transform an entire array at one time - you could do row by row

              -- do your processing here

          forall i in 1..t_targ.count 

             insert into t_target values t_targ(i);

    So you would have a series of procedures/functions:

     

    1. ones that would 'get' one source row or an array of source rows from 'somewhere'

    2. ones that would perform 'audits' or 'cleansing' or those source rows

    3. ones that would then perform 'transforms' on the 'clean' remaining source rows

    4. ones that would 'put' the target data 'somewhere'.

     

    You want to use distinct procedures/functions for the 'command-and-control' processing and the actual 'data' processing.

     

    That allows you to easily plug-and-play with the code:

     

    1. change the source of the data - might come from tables, views, a pipelined function, a stream, read from a file, etc

    2. add new transforms without having to modify existing ones

    3. add a new or additional data sink without having to modify an existing one.

    4. add new tests for new functionality without having to touch/modify, or accidentally break, the existing functionality.

    5. fix bugs in one module without accidentally affecting other modules.

     

    The first choice for any work should be SQL. But once you have to use PL/SQL (e.g. for complex transforms) you simply MUST use a modular approach so that you reap the benefits listed above.

  • 5. Re: Dynamic Insert - Record Type Object
    Lokesh Kumar C Newbie
    Currently Being Moderated

    Hi Stew and RP,

     

    Thanks for your responses. Sorry I couldn't acknowledge to your response much earlier.

     

    I definitely see the improvement while doing row level processing as you have suggested.

     

    In my system, they have defined record type object to load record from stage table and then to load record to fact table.

     

    TYPE PROD_STAGE_RECORD IS RECORD (

    FIELD DATATYPE)

     

    TYPE PROD_FACT_RECORD IS RECORD (

    FIELD DATATYPE)

     

    1. Load Record From Stage Table to PROD_STAGE_RECORD .

    2. Apply business rule and assign values from PROD_STAGE_RECORD to PROD_FACT_RECORD .

     

     

    PROD_STAGE_RECORD  and PROD_FACT_RECORD  have already defined fields. We don't want to insert all the the field value. Only the field which is defined in fact table has to be inserted.

     

    Now based on your suggestion

     

    I have defined collection of table type. How do I assign record type object to a collection ? Do I need to assign each field value in record type to a collection type.

     

     

    TYPE PROD_FACT_COLL IS TABLE OF FACT_TABLE;

    PROD_FACT_COLL_T PROD_FACT_COLL ;

     

    Can I assign record to a collection this way PROD_FACT_COLL_T:=PROD_FACT_RECORD   ?

     

    In my case number of fields in record type object is different and in fact table is different. Say Only 3 field value from record type should be inserted as value in fact table ?

     

    I haven't done extensively programming in PL/SQL, please guide me.

     

    Thanks,

    Lokesh

  • 6. Re: Dynamic Insert - Record Type Object
    rp0428 Guru
    Currently Being Moderated
    I definitely see the improvement while doing row level processing as you have suggested.

    I never suggested any such thing. The example I provided, as I stated, was ONLY to show how to use a row-image object instead of the dynamic processing that you were doing. The example used row-by-row because that is what your code was doing.

     

    You need to instrument your code to properly 'see' improvement. See my reply from a hour ago in this thread for the approach to take on doing that:

    https://community.oracle.com/thread/2619411

    Now based on your suggestion

     

    I have defined collection of table type. How do I assign record type object to a collection ? Do I need to assign each field value in record type to a collection type.

    NOT! I never made any such suggestion. My example was illustrating the use of a row-image object.

     

    Your code has the same architectural flaw that was in that other thread. You need to fix that.

     

    1. use SQL

    2. use PL/SQL with embedded SQL (not dynamic SQL) only if you can't use SQL

    3. use PL/SQL with collections only if you can't use either of the above

      a. use PIPELINED functions if you are doing row-by-row multi-stage transforms

      b. use COLLECTIONs if can do bulk processing of each stage one stage at a time

    4. use DYNAMIC sql ONLY as a last resort

    In my system, they have defined record type object to load record from stage table and then to load record to fact table.

    That's doing things backwards. And just because someone created those objects doesn't mean you need to, or should, use them.

     

    Focus on the PROBLEM. First you have to design the proper architecture based on the requirements.

     

    Then you can start looking at solutions in the order shown above.

     

    Assuming you actually need to use PL/SQL and/or Collections the PL/SQL language doc has examples of how to do that:

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm

     

    And if PIPELINED or parallel table functions are needed the Data Cartridge Dev Guide has examples of that:

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm

Legend

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