Skip to Main Content
  • Questions

  • Casting complex object IN parameter of a procedure multiple time for inserting into multiple tables VS Looping through the object and inserting into all the tables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, evance.

Asked: November 07, 2016 - 3:15 pm UTC

Last updated: November 08, 2016 - 12:59 pm UTC

Version: 11g, 12c

Viewed 1000+ times

You Asked

Hello Tom

First, thanks for all the help and advice you provide to the Oracle world.

I have a procedure which takes IN parameter as a complex object (Ex. Array Starts with Product Events which contains Engines(object) which in turn contains parts(object) and goes on with more levels). I started writing the procedure code as below.
MERGE INTO prod_evnt pe
USING (SELECT * FROM TABLE(CAST(pi_tbl_evnt AS t_tbl_evnt))) o
ON (pe.prod_evnt_id = o.evnt_id)
WHEN NOT MATCHED THEN
INSERT
 /* Columns are shuffled (not as per DB column order) and maintained as per the screen */
 (
   prod_evnt_dt,
   prod_evnt_stat_cd,                    
   prod_evnt_id... and so on
   


INSERT
INTO
  prod_evnt_doc
  (
 prod_evnt_id,
 doc_seq_num,
 data_source_nm,
 doc_txt
  )        
SELECT o.evnt_id, 
    1, 
    NULL, 
    NULL 
FROM TABLE(CAST(pi_tbl_evnt AS t_tbl_evnt)) o, 
  TABLE(o.evnt_rpt_doc) erd


As there are many tables say 40 to be inserted and updated, the same IN object has been CAST in the above fashion that many times. But I somewhere get a gut feel if this should be changed into a single loop which in turn loops the inside level arrays and make insert into tables. Could you please provide your valuable thoughts so that I would proceed in the right direction.

and Chris said...

Using a loop => row-by-row processing => slow code!

So your gut feel of changing this to a loop is probably wrong. The method you're using table(cast(...)) is valid.

That said, there may be specifics of your situation which change this. So don't guess. Measure!

Profile your code for the different approaches and see which comes out best.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Its not only on the performance, but on maintenance as well.

evance Heally, November 08, 2016 - 11:25 am UTC

Hello Chris

Thanks for the response. I understand that the performance decreases drastically on changing to a loop but what about maintainability? Also, is it correct to CAST same type for 40 times to do INSERT into different tables? In case of a loop, I just loop it once for every type in the object and do the insert. Profiling the code might be a little tedious as I might need to write code for so many table insert/ updates using both the CAST as well as LOOP approaches. Is there any suggestion/ other ways that you could possibly suggest considering the current situation? Thanks again for supporting us.

P.S
This procedures is used for transaction and would not be involving huge sets of data at a time.
Chris Saxon
November 08, 2016 - 12:59 pm UTC

I don't see why a loop would necessarily be easier to maintain. Insert ... select ... table() seems fine to me.

is it correct to CAST same type for 40 times to do INSERT into different tables?

If that's your application logic, it's the correct thing to do!

You could look into using insert all so you have fewer different statements. But this can get complicated if you have different numbers of rows into each table.

Thanks Chris!!!

evance Heally, November 08, 2016 - 1:19 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library