1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

This is a simpler version of my earlier thread

Discussion in 'SQL PL/SQL' started by Karan Gupta, May 19, 2017.

  1. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    Code (Text):

    CREATE TABLE "T1"
       (    
        "CID" NUMBER,
        "CNAM" VARCHAR2(10 BYTE),
        PRIMARY KEY ("CID")
       )
     CREATE TABLE "T2"
       (    "ORDID" NUMBER,
        "CUID" NUMBER,
       
         PRIMARY KEY ("ORDID")
     
        CONSTRAINT "TCID_FK" FOREIGN KEY ("CUID")
         REFERENCES "HD"."T1" ("CID")
       )
     
    The procedure:
    Code (Text):

    create or replace procedure ins_t2(
    p_cid t1.cid%type,
    p_name t1.cnam%type,
    p_ordid t2.ordid%type
    )
    is
    l_cust_id t1.cid%type;
      begin
            select cid
            into l_cust_id
            from t1
            where t1.cid=p_cid;
           
      exception
            when no_data_found then
            insert into t1
            values (p_cid, p_name)
            returning cid into l_cust_id;
            insert into t2 values (p_ordid,l_cust_id);
      end;
     
    Sample Data in t1:
    cid,cnam
    1,c1
    2,c2
    3,c3
    Sample data in t2:
    ordid,cuid
    1,1
    2,2
    3,3
    4,1
    5,3
    The last two rows (4,1) and (5,3) are not getting inserted in t2 table. Why is that? What is the mistake in my logic?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    When you work the line (4,1) and (5,3) not_data_found will Not be generated for CID (CUID){1,3}, because CID (CUID) {1,3} already exists...
    Try this simple scenario....

    Code (Text):

    create or replace procedure ins_t2(
    p_cid t1.cid%type,
    p_name t1.cnam%type,
    p_ordid t2.ordid%type
    )
    is
    l_cust_id t1.cid%type;
    l_ordid t2.ordid%type;
      begin
           
        begin
          select cid
          into l_cust_id
          from t1
          where t1.cid=p_cid;
          exception
          when no_data_found then
          insert into t1
          values (p_cid, p_name)
          returning cid into l_cust_id;
        end;
       
        begin
          select
            ordid
          into
             l_ordid
          from t2 where ordid = p_ordid;      
          exception
          when no_data_found then
          insert into t2 values (p_ordid,l_cust_id);
        end;        
      end;
     
     
    Last edited: May 19, 2017
  3. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    The 'no_data_found' exception is raised if the value(p_cid) coming in the procedure has no value matching to any CID already present in the t1 table. In that case new CID will be generated. If the value of p_cid matches any CID in the t1 table then that CID will be taken into l_cust_id and later inserted into t2.
    Note: Originally the CID is generated through a sequence as you must have seen in my main thread. If the value coming in matches any value in the table then that value is taken otherwise new value is generated by a sequence and then that new value is taken into l_cust_id.

    In the above example, (4,1) and (5,3) mean that 4th order is placed by 1st customer again and similarly the 5th order is placed by the 3rd customer. In the output these two rows are lost which should not be the case. One customer can place many orders.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    The better solution may be:1) create gtt tables 2) load the data and then process one operator INSERT ALL.
    I provided a simple example in the previous message, look at it...
     
    Last edited: May 19, 2017
  5. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    I cannot see the example. Also, what are gtt tables? I did not understand the solution you are giving. Can you tell me how do I achieve it? Its working without the procedure but I want it to work in a procedure. My other tables are also getting populated with the cust_id in the procedure.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Ok.
    Modification your procedure :
    Code (Text):

    create or replace procedure ins_t2(
    p_cid t1.cid%type,
    p_name t1.cnam%type,
    p_ordid t2.ordid%type
    )
    is
    l_cust_id t1.cid%type;
    l_ordid t2.ordid%type;
      begin
           
        begin
          select cid
          into l_cust_id
          from t1
          where t1.cid=p_cid;
          exception
          when no_data_found then
          insert into t1
          values (p_cid, p_name)
          returning cid into l_cust_id;
        end;
       
        begin
          select
            ordid
          into
             l_ordid
          from t2 where ordid = p_ordid;      
          exception
          when no_data_found then
          insert into t2 values (p_ordid,l_cust_id);
        end;        
      end;
     
     
  7. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    OK I'll try it.
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    If the task on loading files a permanent task , then better a solution to use the external table.This will effectively write the ETL processing in PL/Sql.
    Additional link : External tables concept
     
  9. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    Yeah I am using external tables for populating my other tables. I just wanted to use all the methods for loading so I used sqlldr. The modification in the procedure worked perfectly but I did not understand why. Why is it necessary to check for the order_id as the order_id will never match any other previous order_id and will always be new. I got the solution but did not understand why it worked. I think more it is more important to understand why a particular solution worked.
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Yes, it is very important to understand how it works.

    I will try to explain again , in more detail.

    How worked your logic on your procedure.....

    For example, load the data (cid,cname,order_id) in ( (1,'test1',1),(1,'test1',2),(2,'test2',2)) .
    When loading 1st pair record stored in the table T1,T2.
    When loaded 2nd pair - the entry in table T2 will not be added because when you check (select...from T1...) will not arise NO_DATA_FOUND --a record with CID=1 is already in the table T1 .

    If neccesary , then needed to change the logic of data processing.