how to insert records

Posted on 2007-10-16
Last Modified: 2013-12-19
hi i need to do some massive inserts by extracting data from an xml stored as a clob in in a table column. the no of records are 40,000. means i extract a particular node by substr etc and then insert. i tried and i am successful for some 10 records. however i am not sure how to work for such massive inserts .... also
any help...? i tried creating a pl/sql...for 500 records and i wastn successful.

what is the right approacj
Question by:samir25
    LVL 73

    Expert Comment

    you can extract the xml data from the nodes directly using xpath queries.

    Something like this perhaps....

    insert into my_table (a, b, c)
    select extractvalue(value(x),'/xquery/path/@a) as a,
              extractvalue(value(x),'/xquery/path/@b) as b,
              extractvalue(value(x),'/xquery/path@c) as c
    from my_xml_table mxt,
            table(xmlsequence(extract(mxt.my_xml_column,'/xqueryparent/xquery'))) x

    LVL 34

    Expert Comment

    "What is the "right approach"?  I'm not a master of working with xml data in Oracle, but we have an approach that works for us.  We also save the *.xml file into an Oracle table, but we don't use a CLOB column, we use a SYS.XMLTYPE column.  Then we use a view like this to present the xml data in a format that we are used to working with (I removed many of the columns to save space):

    create or replace view xml_icaras_orders_view
              SUBSTR (EXTRACTVALUE (VALUE (htable),
              SUBSTR (EXTRACTVALUE (VALUE (htable),
              SUBSTR (EXTRACTVALUE (VALUE (htable),
              SUBSTR (EXTRACTVALUE (VALUE (htable),
              -- line data starts here
              SUBSTR (EXTRACTVALUE (VALUE (ltable),
              SUBSTR (EXTRACTVALUE (VALUE (ltable),
              SUBSTR (EXTRACTVALUE (VALUE (ltable),
              to_number (EXTRACTVALUE (VALUE (ltable),
    FROM icaras_orders_xml x,
    -- extract headers from XML
      '/requirementsexport/requirements_export_header'))) htable,
    -- extract lines from HTABLE to get correlated join
      '/requirements_export_header/requirements_export_detail'))) ltable;

    This works well for an interface we run regularly that processes between a few dozen and a few hundred records and occasionally a thousand or more.  This allows use to use "standard" SQL queries and/or PL\SQL procedures to process this data into Oracle by querying the view.
    LVL 73

    Expert Comment

    whew!  I'm glad I'm not the only one to slap a view on top of a collection of EXCTRACTVALUE's in order to expose xml nicely.
    LVL 1

    Author Comment

    my xml is huge ;.and extractvalue doesnt work. we store in clob ... i wouldnt like to justify that one now..
    but i know how to exctract the node all i want to know is the consequence of inserting so many records at oen time. or should i insert like this from rownum 1 - 100
    i know how to do it. but i am not sure on the impact on following one process
    LVL 34

    Expert Comment

    No, I do not believe that this is the real problem: " the consequence of inserting so many records".  I think the real problem is the performance issue of extracting the data to be inserted into relational tables from the xml data.  That is usually the bottleneck with xml data processing in my experience.

    How long does it take to extract the data from one node?  How long would it take to extract the data from *ALL* nodes/records in the xml data (without doing any inserts)?  If it takes almost as long to extract the data from one node as for all nodes, then that indicates the performance problem you face.  I suspect the problems is on the extraction side, not the actual insert.
    LVL 73

    Expert Comment

    Definitely insert as many as you can (all of them) in one step, don't try to break it into a loop and insert small chunks.  That consumes more resources and takes longer.  And, like markgeer said, most of your work will be coming from the data extraction, not the insert itself.

    You can turn your clob data into xmltype and use extractvalue if you want.  But if you already have a way to pull all the data from your nodes that's fine too.  
    LVL 1

    Author Comment

    extractvalue dont work... it generates the 64k nodes are big..
    How long would it take to extract the data from *ALL* nodes/records in the xml data (without doing any inserts)?   probaly an hr?
    LVL 34

    Expert Comment

    OK, it should take less than a second to insert an individual record into an Oracle table, but if the extract of the data from xml takes an hour, that clearly indicates where the problem is (in the extract from xml).

    How can you optimize that?  Great question!  Like I said, I don't consider myself an expert in this area.  We found a way (using EXTRACTVALUE) that works for us.  But it may have limitations. You may need to keep searching, or studying the Oracle documentation (and examples, if you can find them) for ways of extracting information from xml data in Oracle.

    Or, you may have to post a new question with a titlle more like: "How to extract xml data for Oracle to use?"
    LVL 73

    Accepted Solution

    Agreed, if you're already extracting ok and it's taking that long, it probably doesn't matter much how you do the insert.

    However, to answer your question, depending on how you're doing the extraction, I'd either do a direct insert like ...

    insert into table (select <your-extraction-query here>)  -- most recommended

    -- next recommended
    or, if you're using pl/sql to iterate through the xml then
    as you pull the pieces out into variables, use a table of records with the data you want to insert.
    Then do a single bulk insert at the end.

    last choice (least recommended) is to pull the data out into variables in your pl/sql
    and insert them one row at a time as your extract them.

    Only option I absolutely do not recommend is building dynamic sql of concatenated values.
    Do any of the above three, but don't insert literals.
    LVL 1

    Author Comment

    ref ur sugg..
    as you pull the pieces out into variables, use a table of records with the data you want to insert.
    Then do a single bulk insert at the end.

    can u show one example..thanks for that in advance
    LVL 73

    Assisted Solution

    I'm assuming you have some pl/sql block that looks something like this.

    v_a := dbms_lob.substr(big_xml, dbms_lob.instr(.....), dbms_lob.instr(.....));
    v_b :=  dbms_lob.substr(big_xml, dbms_lob.instr(.....), dbms_lob.instr(.....));

    insert into your_table (a,b,) values (v_a,v_b);
    end loop;

    so something like the following is what I'm imagining.
    I'm not using xml in this example just to keep my parsing simple.
    But where I do my simple substr, you'd put whatever xml parsing you already have.

    As already noted in previous posts, the code below is an efficient way to do mass inserts,
    your parsing time is very likely to be your biggest performance sink.

    Hope this helps!

    CREATE TABLE my_table (a VARCHAR2(10), b VARCHAR2(10), c VARCHAR2(10));

        v_a_tab    DBMS_SQL.varchar2s;
        v_b_tab    DBMS_SQL.varchar2s;
        v_c_tab    DBMS_SQL.varchar2s;
        v_string   VARCHAR2(100)      := 'a1,b1,c1,a2,b2,c2,a3,b3,c3';
        v_index    INTEGER;
        FOR i IN 1 .. 3
            v_a_tab(i)    := SUBSTR(v_string,(i - 1) * 9 + 1, 2);
            v_b_tab(i)    := SUBSTR(v_string,(i - 1) * 9 + 4, 2);
            v_c_tab(i)    := SUBSTR(v_string,(i - 1) * 9 + 7, 2);
        END LOOP;

        FORALL i IN 1 .. 3
            INSERT INTO my_table
                        (a, b, c
                 VALUES (v_a_tab(i), v_b_tab(i), v_c_tab(i)

    SELECT *
      FROM my_table;


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to recover a database from a user managed backup

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now