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!

Pl/sql procedure not functioning properly

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
    The tables with respect to my problem are:
    Code (Text):
     
      CREATE TABLE "CUSTOMER"
       (    "CUST_ID" NUMBER,
        "CUST_NAME" VARCHAR2(50 BYTE),
        "CUST_PHONE" NUMBER(10,0) NOT NULL ENABLE,
         
    CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUST_ID")
     
       )

    CREATE TABLE "CUSTOMER_ORDER"
       (    "ORDER_ID" NUMBER NOT NULL ENABLE,
        "CUST_ID" NUMBER,
        "ORDER_DATE" DATE DEFAULT (sysdate-1),
     
      CONSTRAINT "CO_PK" PRIMARY KEY ("ORDER_ID")
     
        CONSTRAINT "CO_FK" FOREIGN KEY ("CUST_ID")
         REFERENCES "CUSTOMER" ("CUST_ID") ENABLE
       )
    CREATE TABLE "ORDER_LINE"
       (    "ORDER_ID" NUMBER,
        "PRODUCT_ID" NUMBER,
        "QUANTITY" NUMBER,
        "PRICE" NUMBER,
        "PROD_ALIAS" VARCHAR2(10 BYTE),
     
    CONSTRAINT "OL_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")    
     
    CONSTRAINT "OL_FK" FOREIGN KEY ("ORDER_ID")
         REFERENCES "HD"."CUSTOMER_ORDER" ("ORDER_ID") ENABLE,
       
     CONSTRAINT "OL_FK1" FOREIGN KEY ("PRODUCT_ID")
         REFERENCES "HD"."PRODUCT" ("PROD_CODE") ENABLE
       )
       
    There are other tables as well but not related to the problem. There is a 'temp' in which data is loaded through sqlldr and then after insert trigger is calls the following procedure to add data into the tables:

    Code (Text):

    create or replace procedure allinone(
    p_cust_phone customer.cust_phone%type,
    p_address address.address%type,
    p_area address.area%type,
    p_ord_id customer_order.order_id%type
    )
    is
    l_cust_id customer.cust_id%type;
    l_address_id address.address_id%type;
    begin
      begin
            select cust_id
            into l_cust_id
            from customer c
            where c.cust_phone=p_cust_phone;
      exception
            when no_data_found then
            insert into customer
            values (cust_id_seq.nextval, 'No Name', p_cust_phone)
            returning cust_id into l_cust_id;
      end;
     
              select address_id
              into l_address_id
              from address a
              where a.address=p_address;
      exception
              when no_data_found then
              insert into address
              values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
              returning address_id into l_address_id;
     
      insert into customer_address
      values (l_cust_id, l_address_id);
     
      insert into customer_order (order_id, cust_id)
      values (p_ord_id,l_cust_id);
     
    end;
     
    The trigger is :
    Code (Text):

    create or replace TRIGGER callproc_tg
    AFTER INSERT ON temp
    FOR EACH ROW
     BEGIN
      allinone(:new.phno, :new.addr, :new.area, :new.ord_id);
     END;
     

    The problem is that data in the CUSTOMER_ORDER table is not getting inserted as desired. Multiple values of order_id are not getting entered in the table.

    Example:
    If I have cust_id=1 placing 3 orders then only the first order is reflected in the CUSTOMER_ORDER table. The rest of the 2 orders are lost. What is the mistake I am making in the procedure? Unable to figure it out.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Might ,check your logic your procedure?
     
    Last edited: May 19, 2017
  3. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    My procedure is working, its not showing any error. The data is the thing which is not getting inserted properly. Let me make things easier. I am posting a simplified version of this.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    OK.
    Can you provide here DDL for table TMP and DML-script(insert statements) for filling table TMP?
     
  5. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    The temp table is populated through sqlldr. and then the after insert trigger on temp. The control file of sqlldr is:
    load data
    infile 'D:\ext_table\cload.csv'
    into table hd.temp
    fields terminated by ","
    (phno,addr,area,ord_id)
    I have made another thread which is easier to understand. care to have a look.
    Code (Text):

    REATE TABLE "TEMP"
       (    "PHNO" NUMBER,
        "ADDR" VARCHAR2(500 BYTE),
        "AREA" VARCHAR2(30 BYTE),
        "ORD_ID" NUMBER
       )
     
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's working for me:

    Code (SQL):
    SQL> CREATE TABLE address(
      2  address_id NUMBER,
      3  address            varchar2(80),
      4  area               varchar2(80))
      5  /

    TABLE created.

    SQL>
    SQL> CREATE SEQUENCE address_id_seq;

    SEQUENCE created.

    SQL> CREATE SEQUENCE cust_id_seq;

    SEQUENCE created.

    SQL>
    SQL>  CREATE TABLE "CUSTOMER"
      2     (    "CUST_ID" NUMBER,
      3      "CUST_NAME" VARCHAR2(50 BYTE),
      4      "CUST_PHONE" NUMBER(10,0) NOT NULL ENABLE,
      5     CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUST_ID")
      6      )
      7  /

    TABLE created.

    SQL>
    SQL> CREATE TABLE "CUSTOMER_ORDER"
      2     (    "ORDER_ID" NUMBER NOT NULL ENABLE,
      3      "CUST_ID" NUMBER,
      4      "ORDER_DATE" DATE DEFAULT (sysdate-1),
      5     CONSTRAINT "CO_PK" PRIMARY KEY ("ORDER_ID"),
      6       CONSTRAINT "CO_FK" FOREIGN KEY ("CUST_ID")
      7       REFERENCES "CUSTOMER" ("CUST_ID") ENABLE
      8     )
      9  /

    TABLE created.

    SQL>
    SQL> CREATE TABLE product(
      2  prod_code NUMBER,
      3  CONSTRAINT product_pk PRIMARY KEY (prod_code)
      4  )
      5  /

    TABLE created.

    SQL>
    SQL> CREATE TABLE "ORDER_LINE"
      2     (    "ORDER_ID" NUMBER,
      3      "PRODUCT_ID" NUMBER,
      4      "QUANTITY" NUMBER,
      5      "PRICE" NUMBER,
      6      "PROD_ALIAS" VARCHAR2(10 BYTE),
      7   CONSTRAINT "OL_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID"),
      8   CONSTRAINT "OL_FK" FOREIGN KEY ("ORDER_ID")
      9       REFERENCES "CUSTOMER_ORDER" ("ORDER_ID") ENABLE,
    10    CONSTRAINT "OL_FK1" FOREIGN KEY ("PRODUCT_ID")
    11       REFERENCES "PRODUCT" ("PROD_CODE") ENABLE
    12     )
    13  /

    TABLE created.

    SQL>
    SQL> CREATE TABLE "TEMP"
      2     (    "PHNO" NUMBER,
      3      "ADDR" VARCHAR2(500 BYTE),
      4      "AREA" VARCHAR2(30 BYTE),
      5      "ORD_ID" NUMBER
      6     )
      7   /

    TABLE created.

    SQL>
    SQL> CREATE TABLE customer_address(
      2  customer_id        NUMBER,
      3  address_id NUMBER)
      4  /

    TABLE created.

    SQL>
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE allinone(
      2  p_cust_phone customer.cust_phone%TYPE,
      3  p_address address.address%TYPE,
      4  p_area address.area%TYPE,
      5  p_ord_id customer_order.order_id%TYPE
      6  )
      7  IS
      8  l_cust_id customer.cust_id%TYPE;
      9  l_address_id address.address_id%TYPE;
    10  BEGIN
    11    BEGIN
    12          SELECT cust_id
    13          INTO l_cust_id
    14          FROM customer c
    15          WHERE c.cust_phone=p_cust_phone;
    16    exception
    17          WHEN no_data_found THEN
    18          INSERT INTO customer
    19          VALUES (cust_id_seq.NEXTVAL, 'No Name', p_cust_phone)
    20          returning cust_id INTO l_cust_id;
    21    END;
    22
    23            SELECT address_id
    24            INTO l_address_id
    25            FROM address a
    26            WHERE a.address=p_address;
    27    exception
    28            WHEN no_data_found THEN
    29            INSERT INTO address
    30            VALUES (address_id_seq.NEXTVAL, UPPER(p_address), UPPER(p_area))
    31            returning address_id INTO l_address_id;
    32
    33    INSERT INTO customer_address
    34    VALUES (l_cust_id, l_address_id);
    35
    36    INSERT INTO customer_order (order_id, cust_id)
    37    VALUES (p_ord_id,l_cust_id);
    38
    39  END;
    40  /

    PROCEDURE created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> CREATE OR REPLACE TRIGGER callproc_tg
      2  AFTER INSERT ON temp
      3  FOR EACH ROW
      4   BEGIN
      5    allinone(:NEW.phno, :NEW.addr, :NEW.area, :NEW.ord_id);
      6   END;
      7   /

    TRIGGER created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> INSERT INTO temp
      2  VALUES(9999999999,'99 Borscht Place, Flat 777, Bingleton, Hubnebia','Outside',1);

    1 ROW created.

    SQL> INSERT INTO temp
      2  VALUES(9999999999,'99 Borscht Place, Flat 777, Bingleton, Hubnebia','Outside',2);

    1 ROW created.

    SQL> INSERT INTO temp
      2  VALUES(9999999999,'99 Borscht Place, Flat 777, Bingleton, Hubnebia','Outside',3);

    1 ROW created.

    SQL> INSERT INTO temp
      2  VALUES(9999999999,'99 Borscht Place, Flat 777, Bingleton, Hubnebia','Outside',4);

    1 ROW created.

    SQL>
    SQL> SELECT * FROM customer_order;

      ORDER_ID    CUST_ID ORDER_DAT
    ---------- ---------- ---------
             1          1 18-MAY-17
             2          1 18-MAY-17
             3          1 18-MAY-17
             4          1 18-MAY-17

    SQL>
     
  7. Karan Gupta

    Karan Gupta Starter

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    I don't get it. Why is it not working for me. :S
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Do what I did -- run the procedure and capture the output then post it here. If we see what you are doing we can possibly find your error.