This discussion is archived
11 Replies Latest reply: Nov 30, 2012 12:13 PM by Jason_(A_Non) RSS

Retrieving data from different node in SQL

Mihai_72 Newbie
Currently Being Moderated
Hi friends,

First of all:
        BANNER
1     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2     PL/SQL Release 11.2.0.3.0 - Production
3     CORE     11.2.0.3.0     Production
4     TNS for Linux: Version 11.2.0.3.0 - Production
5     NLSRTL Version 11.2.0.3.0 - Production
I have a small but annoying problem. I have to build a bank application which must take data from an input file, and, based on those information, to execute some stored procedures (create a new customer, create a new account, create a transaction for an account).
For example: the input file contain few new customers (FirstName, LastName, SocialSecurityNumber, BirthDay, Address and PhoneNo), for each customer at least 1 account to be created, for each account 1 or more transactions (the amount of transaction).

Let me be more specific. This is an XML as a input file:
<?xml version="1.0" encoding="ISO-8859-1"?>
<doc>
  <PERS_INFO>
    <lastname>John</lastname>
    <firstname>Doe</firstname>
    <social_security_no>1800325171545</social_security_no>
    <birthday>25/03/1980</birthday>
    <address>Principal, 15</address>
    <phoneno>0040722222222</phoneno>
    <acc>
      <transaction>200</transaction>
      <transaction>150</transaction>
      <transaction>-23</transaction>
    </acc>
    <acc>
      <transaction>450</transaction>
    </acc>
    <acc>
      <transaction>800</transaction>
      <transaction>320</transaction>
      <transaction>-125</transaction>
    </acc>
  </PERS_INFO>
  <PERS_INFO>
    <lastname>Smith</lastname>
    <firstname>Pop</firstname>
    <social_security_no>2851211173377</social_security_no>
    <birthday>11/12/1985</birthday>
    <address>FirstAvenue, 20</address>
    <phoneno>0040744444444</phoneno>
    <acc>
      <transaction>444</transaction>
      <transaction>550</transaction>
    </acc>
    <acc>
      <transaction>113</transaction>
      <transaction>-50</transaction>
      <transaction>89</transaction>
    </acc>
    <acc>
      <transaction>300</transaction>
    </acc>
  </PERS_INFO>
</doc>
This input file should start the following:

- create 2 new customers (using a stored procedure Pr_Add_New_Cust):
1. John Doe / 1800325171545 / 25.03.1980 / Principal, 15 / 0040722222222
2. Smith Pop / 2851211173377 / 11.12.1985 / FirstAvenue, 20 / 0040744444444

- for John Doe I have to create 3 new account (using a stored procedure Pr_Add_New_Account):
- account 1 - for this account I have to create 3 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 200$, DEPOSIT 150$, WITHDRAW 23$
- account 2 - for this account I have to create 1 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 450$
- account 3 - for this account I have to create 3 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 800$, DEPOSIT 320$, WITHDRAW 125$

- for Smith Pop I have to create 3 new account (using a stored procedure Pr_Add_New_Account):
- account 1 - for this account I have to create 2 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 444$, DEPOSIT 550$
- account 2 - for this account I have to create 3 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 113$, WITHDRAW 50$, DEPOSIT 89$
- account 3 - for this account I have to create 1 new transactions (using a stored procedure Pr_Create_New_Trans): DEPOSIT 300$


Well, I'm thinking to do that by loading that XML into a pre-staging table - INS_STG1 - (the loading part is not an issue for the moment) and by parsing the XML in order to get my necessary information (inserting into the second staging table INS_STG2) to launch those procedures.
Something like this (for the moment I insert only the customer information since I don't know how to get to the second level):
DECLARE  

   TYPE ty_rec_1    IS RECORD (v_cust_firstname       VARCHAR2(32),
                               v_cust_lastname        VARCHAR2(32),
                               v_cust_persnumcode     VARCHAR2(32),
                               v_cust_birthday        VARCHAR2(32),
                               v_cust_address         VARCHAR2(32),
                               v_cust_phoneno         VARCHAR2(32));
   TYPE ty_cur_1       IS REF CURSOR RETURN ty_rec_1;
   TYPE ty_arr_rec_1   IS TABLE OF ty_rec_1 INDEX BY PLS_INTEGER;
   cur_1               ty_cur_1;
   arr_rec_1           ty_arr_rec_1;
   my_xml              xmltype;
   N_BULK_SIZE         NUMBER := 1000;
  
BEGIN 
  
  SELECT xml_column INTO my_xml FROM INS_STG1;
  
  OPEN cur_1 FOR
    SELECT extractvalue(column_value, '/PERS_INFO/firstname')          "v_cust_firstname",
           extractvalue(column_value, '/PERS_INFO/lastname')           "v_cust_lastname",
           extractvalue(column_value, '/PERS_INFO/social_security_no') "v_cust_persnumcode",
           extractvalue(column_value, '/PERS_INFO/birthday')           "v_cust_birthday",
           extractvalue(column_value, '/PERS_INFO/address')            "v_cust_address",
           extractvalue(column_value, '/PERS_INFO/phoneno')            "v_cust_phoneno"       
    FROM TABLE(XMLSequence(my_xml.extract('/doc/PERS_INFO'))) t;

    LOOP
       FETCH cur_1 BULK COLLECT INTO arr_rec_1 LIMIT N_BULK_SIZE;
       EXIT WHEN arr_rec_1.COUNT() = 0;
       FORALL n_idx1 IN 1..arr_rec_1.COUNT()
         INSERT INTO INS_STG2
           (cust_firstname, cust_lastname, cust_persnumcode, cust_birthday, cust_address, cust_phoneno)
          VALUES 
          (arr_rec_1(n_idx1).v_cust_firstname, arr_rec_1(n_idx1).v_cust_lastname, 
           arr_rec_1(n_idx1).v_cust_persnumcode, arr_rec_1(n_idx1).v_cust_birthday, 
           arr_rec_1(n_idx1).v_cust_address, arr_rec_1(n_idx1).v_cust_phoneno);
     END LOOP;
   CLOSE cur_1;  
  COMMIT;              

END;
That procedure is based on the SQL query that I start to ply with in order to understand how to get the information from XML using SQL (the XML is hard-coded)
SELECT extractvalue(column_value, '/PERS_INFO/firstname')    "First Name",
       extractvalue(column_value, '/PERS_INFO/lastname')     "Last Name",
       extractvalue(column_value, '/PERS_INFO/social_security_no')  "Social Security No",
       extractvalue(column_value, '/PERS_INFO/birthday')     "Birth-day",
       extractvalue(column_value, '/PERS_INFO/address')      "Address",
       extractvalue(column_value, '/PERS_INFO/phoneno')      "Phone No"
  FROM TABLE(XMLSequence(
             XMLTYPE(
             '<?xml version="1.0" encoding="ISO-8859-1"?>
              <doc>
                <PERS_INFO>
                  <lastname>John</lastname>
                  <firstname>Doe</firstname>
                  <social_security_no>1800325171545</social_security_no>
                  <birthday>25/03/1980</birthday>
                  <address>Principal, 15</address>
                  <phoneno>0040722222222</phoneno>
                  <acc>
                    <transaction>200</transaction>
                    <transaction>150</transaction>
                    <transaction>-23</transaction>
                  </acc>
                  <acc>
                    <transaction>450</transaction>
                  </acc>
                  <acc>
                    <transaction>800</transaction>
                    <transaction>320</transaction>
                    <transaction>-125</transaction>
                  </acc>
                </PERS_INFO>
                <PERS_INFO>
                  <lastname>Smith</lastname>
                  <firstname>Pop</firstname>
                  <social_security_no>2851211173377</social_security_no>
                  <birthday>11/12/1985</birthday>
                  <address>FirstAvenue, 20</address>
                  <phoneno>0040744444444</phoneno>
                  <acc>
                    <transaction>444</transaction>
                    <transaction>550</transaction>
                  </acc>
                  <acc>
                    <transaction>113</transaction>
                    <transaction>-50</transaction>
                    <transaction>89</transaction>
                  </acc>
                  <acc>
                    <transaction>300</transaction>
                  </acc>
                </PERS_INFO>
              </doc>').extract('/doc/PERS_INFO'))) t;
My problem is that query goes only at the first level (the customer level). I don't know how to get to the second/third level (account/transactions level).
Now, that query return:
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444
and I want it to return like this:
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  1   200
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  1   150
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  1  -23
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  2   450
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  3   800
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  3   320
Doe     John     1800325171545     25/03/1980     Principal, 15     0040722222222  3  -125 
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  1   444
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  1   550
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  2   113
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  2  -50
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  2   89
Pop     Smith     2851211173377     11/12/1985     FirstAvenue, 20     0040744444444  3   300
What I have to do to that SQL query to get the result at the transaction level (as in the second table)? That XML can be subject of improvement as well. If another structure serve better for my purpose, please let me know.

Thanks!
  • 1. Re: Retrieving data from different node in SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    As [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions061.htm#SQLRF06173]extractvalue is deprecated in your version, you need to switch to another method as the documentation suggests.

    Going forward with that, here is a starter SQL that gives you the desired output
    WITH INS_STG1 AS
    (SELECT XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?>
                  <doc>
                    <PERS_INFO>
                      <lastname>John</lastname>
                      <firstname>Doe</firstname>
                      <social_security_no>1800325171545</social_security_no>
                      <birthday>25/03/1980</birthday>
                      <address>Principal, 15</address>
                      <phoneno>0040722222222</phoneno>
                      <acc>
                        <transaction>200</transaction>
                        <transaction>150</transaction>
                        <transaction>-23</transaction>
                      </acc>
                      <acc>
                        <transaction>450</transaction>
                      </acc>
                      <acc>
                        <transaction>800</transaction>
                        <transaction>320</transaction>
                        <transaction>-125</transaction>
                      </acc>
                    </PERS_INFO>
                    <PERS_INFO>
                      <lastname>Smith</lastname>
                      <firstname>Pop</firstname>
                      <social_security_no>2851211173377</social_security_no>
                      <birthday>11/12/1985</birthday>
                      <address>FirstAvenue, 20</address>
                      <phoneno>0040744444444</phoneno>
                      <acc>
                        <transaction>444</transaction>
                        <transaction>550</transaction>
                      </acc>
                      <acc>
                        <transaction>113</transaction>
                        <transaction>-50</transaction>
                        <transaction>89</transaction>
                      </acc>
                      <acc>
                        <transaction>300</transaction>
                      </acc>
                    </PERS_INFO>
                  </doc>') xml_column FROM DUAL
    )
    -- The above simulates your table.  Only the below matters.
    SELECT xt.First_Name, xt.birth_day, xt2.acc_rn, xt3.trans
      FROM INS_STG1,
           XMLTable('/doc/PERS_INFO'
                    PASSING INS_STG1.xml_column
                    COLUMNS
                    First_Name   VARCHAR2(20) PATH 'firstname',
                    Birth_day    VARCHAR2(10) PATH 'birthday',
                    acc_xml      XMLType      PATH 'acc') xt,
           XMLTable('/acc'
                    PASSING xt.acc_xml
                    COLUMNS
                    acc_rn       FOR ORDINALITY,
                    tran_xml     XMLTYPE      PATH 'transaction') xt2,
           XMLTable('/transaction'
                    PASSING xt2.tran_xml
                    COLUMNS
                    trans        NUMBER       PATH '.') xt3;
    produces
    FIRST_NAME           BIRTH_DAY      ACC_RN      TRANS
    -------------------- ---------- ---------- ----------
    Doe                  25/03/1980          1        200
    Doe                  25/03/1980          1        150
    Doe                  25/03/1980          1        -23
    Doe                  25/03/1980          2        450
    Doe                  25/03/1980          3        800
    Doe                  25/03/1980          3        320
    Doe                  25/03/1980          3       -125
    Pop                  11/12/1985          1        444
    Pop                  11/12/1985          1        550
    Pop                  11/12/1985          2        113
    Pop                  11/12/1985          2        -50
    Pop                  11/12/1985          2         89
    Pop                  11/12/1985          3        300
    Now ... there is probably a better XQuery way to pull that off, but I'll save that example for someone better at XQuery than myself.

    I will also include that it is possible all this work could be done in a single SQL statement as shown by
    [url http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/]How To : Load XML data into multiple tables
  • 2. Re: Retrieving data from different node in SQL
    odie_63 Guru
    Currently Being Moderated
    Mihai,

    Thanks for the detailed scenario and test case. I wish we could mark questions as correct too ;)

    Along with Jason's suggestions, a few additional tips :

    Forget the CURSOR/FORALL approach.
    Since you insert the file into an XMLType column, you can perform the entire processing from there.
    So keep it simple, there's no added value in pulling data from SQL to PL/SQL then to SQL again.

    You can insert into the three tables in a single statement, while keeping the relationship between them.
    Three separate inserts may do the job too.
  • 3. Re: Retrieving data from different node in SQL
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Thanks for the detailed scenario and test case. I wish we could mark questions as correct too
    Ah come on, we love this stuff, don't we? Since when do we need points in reward?

    ;-)
  • 4. Re: Retrieving data from different node in SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    As Marc implied, I give the OP points for making such a clean test and formatted test case. It sure made my copy/paste effort easier.
  • 5. Re: Retrieving data from different node in SQL
    Mihai_72 Newbie
    Currently Being Moderated
    You are the man!
    Thanks a lot man, you save my day. This is exactly what I need for my job.
  • 6. Re: Retrieving data from different node in SQL
    Mihai_72 Newbie
    Currently Being Moderated
    @odie_63:
    you're absolutely right with your observation. The contextual switch is very time/resources consuming. But my task is in reality a little more complicated than I wrote in my test case. That input file must comply with several rules, meaning that must be able to do all this things:

    -     create at least 5 customers
    -     create at least 1 personal (i.e. not shared) account for each customer
    -     at least 2 shared accounts
    -     create at least 1 transaction per account
    -     at least 1 account should have more than 5 transactions
    -     at least 1 customer should have at least 3 accounts (of which one shared), with at least 2 transactions on each account

    Now, creating a new customer/new account/new transaction is something that can be done based on that SELECT. But performing all those the validations and, after being sure that all of them were OK, launch the procedures for creating new customer/account/transaction... well, I wasn't good enough to retrieve my data at the transaction level...
    Maybe there is a way to do all those from a single SELECT, but I'm not that good in XML! And, to be more evil, all those validations are part of a bigger picture. Are only the first from a list of six functionality for my application.

    I'm happy that I have my question answered anyway. Now I can continue my development.

    Thanks again my friends!

    Edited by: Mihai_72 on Nov 30, 2012 11:29 AM

    Edited by: Mihai_72 on Nov 30, 2012 11:30 AM
  • 7. Re: Retrieving data from different node in SQL
    Mihai_72 Newbie
    Currently Being Moderated
    Just to make you an idea about why I don't believe that is possible to do all the task from a single SQL, the creation of a new customer is not a simple INSERT. It's a procedure:
    /*===============================================================================================*/
    /*--  PROCEDURE pr_add_new_customer            --------------------------------------------------*/
    /*--  procedure for creating a new customer    --------------------------------------------------*/
    /*===============================================================================================*/
     PROCEDURE pr_add_new_customer(p_cust_firstname   IN CUSTOMERS.Cust_FirstName%TYPE,
                                   p_cust_lastname    IN CUSTOMERS.Cust_LastName%TYPE,
                                   p_cust_socsecno    IN CUSTOMERS.Cust_SocSecNo%TYPE,
                                   p_cust_birthday    IN CUSTOMERS.Cust_BirthDay%TYPE,
                                   p_cust_address     IN CUSTOMERS.Cust_Address%TYPE,
                                   p_cust_phoneno     IN CUSTOMERS.Cust_PhoneNo%TYPE) AS
          
       v_cust_id     CUSTOMERS.Cust_Id%TYPE;
    
     BEGIN
       pr_execution_log ('PKG_ACC_CUST_MANAGEMENT',
                         'pr_add_new_customer',
                         'Start execution',
                         'Social Security Number: '||to_char(p_cust_socsecno)
                         ||', FirstName - LastName: '||p_cust_firstname||'-'||p_cust_lastname||', Birth-Day: '||to_char(p_cust_birthday, 'dd.mm.yyyy')
                         ||', Address - PhoneNo: '||p_cust_address||'-'||to_char(p_cust_phoneno),
                         SYSDATE);
    
       -- check if the customer allready exists; if so, the execution will stop
       IF fn_customer_exist(p_cust_socsecno) THEN
         pr_execution_log ('PKG_ACC_CUST_MANAGEMENT',
                           'pr_add_new_account',
                           'Error: the customer allready exists!',
                           'Social Security Number: '||p_cust_socsecno,
                           SYSDATE);
         return;
       END IF;
    
       -- get the customer id
       select SQ_Cust_Id.NextVal into v_cust_id from dual;
    
       -- insert the new customer
       insert into CUSTOMERS values (v_cust_id, p_cust_socsecno, p_cust_firstname, p_cust_lastname, p_cust_birthday, p_cust_address, p_cust_phoneno);
       commit;
    
       pr_execution_log ('PKG_ACC_CUST_MANAGEMENT',
                         'pr_add_new_customer',
                         'End execution',
                         'Social Security Number: '||to_char(p_cust_socsecno)
                         ||', FirstName - LastName: '||p_cust_firstname||'-'||p_cust_lastname||', Birth-Day: '||to_char(p_cust_birthday, 'dd.mm.yyyy')
                         ||', Address - PhoneNo: '||p_cust_address||'-'||to_char(p_cust_phoneno)||', Customer_Id: '||to_char(v_cust_id),
                         SYSDATE);
     EXCEPTION
        WHEN OTHERS THEN
          v_errorMsg := 'Error in pr_add_new_customer : '||SQLERRM;
           raise_application_error(-20001,'An error occurs inside procedure pr_add_new_customer ' ||
                                   substr(v_errorMsg || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),1, 2000));
     END pr_add_new_customer;
  • 8. Re: Retrieving data from different node in SQL
    odie_63 Guru
    Currently Being Moderated
    Just to make you an idea about why I don't believe that is possible to do all the task from a single SQL, the creation of a new customer is not a simple INSERT.
    Of course, if you really need that much verbosity just to INSERT a single row, I must concur on your using a procedure for this.
  • 9. Re: Retrieving data from different node in SQL
    Mihai_72 Newbie
    Currently Being Moderated
    :-)
    Don't forget that we are talking about a bank application. LOG is mandatory, validations are mandatory. The application suppose to insert 100 clients/accounts/transactions in one step. I can not enforce the integrity with PK and do all from an single SQL (as you suggest) because if the 23 ^rd^ client has the same Social Security Number with the 15 ^th^, the applications must work further and continue with the rest of the clients, not to stop because of PK Violated at the 23 ^rd^ client. In the other hand, they must know where things goes wrong in order to clarify/solve the issue (maybe 1 digit from the 23 ^rd^ customer Soc_Sec_No is typo). The application must log all this.

    I assume we all sleep better knowing that our bank handles carefully our data/account/transactions, don't we? :)

    Edited by: Mihai_72 on Nov 30, 2012 3:47 PM

    Edited by: Mihai_72 on Nov 30, 2012 4:20 PM

    Edited by: Mihai_72 on Nov 30, 2012 4:20 PM
  • 10. Re: Retrieving data from different node in SQL
    odie_63 Guru
    Currently Being Moderated
    Don't forget that we are talking about a bank application. LOG is mandatory, validations are mandatory.
    Of course no problem with that.
    I'm just saying that a row-by-row approach is not the only option.

    I can not enforce the integrity with PK and do all from an single SQL (as you suggest) because if the 23 ^rd^ client has the same Social Security Number with the 15 ^th^, the applications must work further and continue with the rest of the clients, not to stop because of PK Violated at the 23 ^rd^ client. In the other hand, they must know where things goes wrong in order to clarify/solve the issue (maybe 1 digit from the 23 ^rd^ customer Soc_Sec_No is typo). The application must log all this.
    DML Error Logging can handle that kind of scenarios, i.e. tracing error along with faulty values, while preserving set-based (bulk) operation flow.

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm#ADMIN10261

    Edited by: odie_63 on 30 nov. 2012 16:59
  • 11. Re: Retrieving data from different node in SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    And your answer made me remember the
    RETURNING <columns> BULK COLLECT INTO <structure>
    concept that could also be used with set based processing as well to return column values from the affected rows.

    http://www.oracle-base.com/articles/misc/dml-returning-into-clause.php

    That can work in conjunction with the DML error handling.

Legend

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