Home » SQL & PL/SQL » SQL & PL/SQL » Need to remove logic for inserting multiple rows (oracle 9i)
Need to remove logic for inserting multiple rows [message #327759] Tue, 17 June 2008 08:35 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have created a procedure. Requirement is that Remove logic to insert multiple rows based on p_PRODUCT_SEQUENCE_NBR parameter. Only One row with seq 1 should be inserted.
Do I need to remove line "l_item_seq_nbr := l_item_seq_nbr+1;"? Need help!

Create Procedure X ( p_PRODUCT_SEQUENCE_NBR IN CHAR)
Begin
...

LOOP
           INSERT INTO P_ITEM(
           ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           LAST_UPD_TMS)
        VALUES(
           l_item_seq_nbr,
           p_PRODUCT_ID,
           SYSTIMESTAMP);
       COMMIT;
      
        INSERT INTO ITEM_ACTIVITY(
           COMM_ITEM_SEQUENCE_NBR,
           LAST_UPD_TMS,
           STATUS_CDE)
        VALUES(
           l_item_seq_nbr,
           SYSTIMESTAMP,
           p_STATUS_CDE,
           );
        COMMIT;
        -- l_item_seq_nbr := l_item_seq_nbr+1;

        --EXIT WHEN l_item_seq_nbr > p_PRODUCT_SEQUENCE_NBR;
   --END LOOP;
   ..
   End;
   

[Updated on: Tue, 17 June 2008 08:39]

Report message to a moderator

Re: Need to remove logic for inserting multiple rows [message #327761 is a reply to message #327759] Tue, 17 June 2008 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow the Posting Guidelines as presented in URL above

It is NOT clear what the final results should be.
Re: Need to remove logic for inserting multiple rows [message #327762 is a reply to message #327761] Tue, 17 June 2008 08:47 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
The below procedure was initially usedd to insert bulk of rows.Now requirement is that Remove logic to insert multiple rows based on p_PRODUCT_SEQUENCE_NBR.
Create Procedure X ( p_PRODUCT_SEQUENCE_NBR IN CHAR)
Begin
...

LOOP
           INSERT INTO P_ITEM(
           ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           LAST_UPD_TMS)
        VALUES(
           l_item_seq_nbr,
           p_PRODUCT_ID,
           SYSTIMESTAMP);
       COMMIT;
      
        INSERT INTO ITEM_ACTIVITY(
           COMM_ITEM_SEQUENCE_NBR,
           LAST_UPD_TMS,
           STATUS_CDE)
        VALUES(
           l_item_seq_nbr,
           SYSTIMESTAMP,
           p_STATUS_CDE,
           );
        COMMIT;
        l_item_seq_nbr := l_item_seq_nbr+1;

        EXIT WHEN l_item_seq_nbr > p_PRODUCT_SEQUENCE_NBR;
   END LOOP;
   ..
   End;


Re: Need to remove logic for inserting multiple rows [message #327764 is a reply to message #327759] Tue, 17 June 2008 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Now requirement is that Remove logic to insert multiple rows
That requirement can be met by
1) remove INSERT
2) remove COMMIT
3) remove LOOP

The choice is yours since posted requirements are still subject to interpretation.
Re: Need to remove logic for inserting multiple rows [message #327769 is a reply to message #327764] Tue, 17 June 2008 09:02 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Only One row with seq 1 should be inserted
Re: Need to remove logic for inserting multiple rows [message #327772 is a reply to message #327769] Tue, 17 June 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Olivia wrote on Tue, 17 June 2008 07:02
Only One row with seq 1 should be inserted


Procedure shows TWO INSERT statements; which is required & which is eliminated?

ONE ROW is 1 INSERT.
Re: Need to remove logic for inserting multiple rows [message #327774 is a reply to message #327769] Tue, 17 June 2008 09:18 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the response. Here is the code below. I need to understand ..Do I need to remove loop as well as increment of
l_ORDER_ITEM_SEQ?
CREATE OR REPLACE PROCEDURE  TEST(
 p_ORDER_ID              	 in  number,
 p_PRODUCT_ID                IN  NUMBER ,
 p_ORDER_ITEM_SEQUENCE_NBR    IN  NUMBER,
 RC1                         OUT Pkg1.RT
)
 AS
 l_ERRMSG                    VARCHAR2(1000);
 l_ORDER_ITEM_ID             NUMBER(11);
 l_ORDER_ID          NUMBER(11):= 0;
 l_ORDER_ITEM_SEQ             NUMBER := 1;
 
    /* GET ORDER_ITEM_ID */
    SELECT  pkg1.itm
    INTO    l_ORDER_ITEM_ID
    FROM    dual;
   
    /* GET ORDER_ID */
    SELECT pkg1.itm
    INTO l_ORDER_ID
    FROM DUAL;
   
   LOOP
        INSERT INTO ORDER_ITEM(
           ORDER_ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           ORDER_ID,
           TMS_DT)
        VALUES(
           l_ORDER_ITEM_SEQ,
           p_PRODUCT_ID,
           l_ORDER_id,
           SYSTIMESTAMP);
        COMMIT;
        INSERT INTO ORDER(
           ORDER_ID,
           ORDER_ITEM_SEQUENCE_NBR,
           ORDER_ITEM_ID,
           TMS_DT)
        VALUES(
           l_ORDER_ID,
           l_ORDER_ITEM_SEQ,
           l_ORDER_ITEM_ID,
           SYSTIMESTAMP);
        COMMIT;
        INSERT INTO ORDER_ITEM_ACTIVITY(
           ORDER_ID,
           ORDER_ITEM_SEQUENCE_NBR,
           TMS_DT
           )
        VALUES(
           l_ORDER_ID,
           l_ORDER_ITEM_SEQ,
           SYSTIMESTAMP
           );
        COMMIT;
        l_ORDER_ITEM_SEQ := l_ORDER_ITEM_SEQ+1;

        EXIT WHEN l_ORDER_ITEM_SEQ > p_ORDER_ITEM_SEQUENCE_NBR;
   END LOOP;
    OPEN RC1 FOR
   
    ....
	...
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN

            ROLLBACK;
            dbms_output.put_line('Unable to insert : '||SQLERRM);
            l_ERRMSG := 'Unable to insert : '||SQLERRM;
            RAISE;
    END TEST;



Remove logic to insert multiple rows based on p_ORDER_ITEM_SEQUENCE_NBR parameter. Only One row with seq 1 should be inserted.
Re: Need to remove logic for inserting multiple rows [message #327776 is a reply to message #327759] Tue, 17 June 2008 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Only One row with seq 1 should be inserted.
See previously posted response.
Re: Need to remove logic for inserting multiple rows [message #327778 is a reply to message #327776] Tue, 17 June 2008 09:39 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Thanks anacedent for giving your time.
Yah, requirement is that multiple row should not be inserted.
What I wanna do is I wanna remove multiple insert based on p_ORDER_ITEM_SEQUENCE_NBR parameter. Only single row with seq 1 should be inserted.

My question was do I need to remove the incrementation logic for sequence?
Re: Need to remove logic for inserting multiple rows [message #327785 is a reply to message #327759] Tue, 17 June 2008 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>My question was do I need to remove the incrementation logic for sequence?
If the "incrementation logic" is removed, what terminates the LOOP?
Re: Need to remove logic for inserting multiple rows [message #327820 is a reply to message #327785] Wed, 18 June 2008 02:06 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks again for the reply.
I am herewith again pasting a sample procedure (code modified!).Requirement is the same.The LOOP is being used to insert multiple rows.Requiement was to Remove logic to insert multiple rows based on p_COMM_ITEM_SEQ_NO parameter.Only One row with seq 1 should be inserted.Need your help!


CREATE OR REPLACE PROCEDURE      TEST(
 p_COM_CD                           in  number,
 p_PROD_CD                        IN  NUMBER ,
 p_COMM_ITEM_SEQ_NO                IN  NUMBER,
 RC1                             OUT Pkg1.RC1
)
 AS
 l_ERRMSG                    VARCHAR2(1000);
 l_ORDER_ITEM_CD             NUMBER(10);
 l_COMM_CD                   NUMBER(9):= 0;
 l_COMM_ITEM_SEQ             NUMBER := 1;

 BEGIN
   
     -- Get Order item code
    SELECT  pkg1.id
    INTO    l_ORDER_ITEM_CD
    FROM    dual;
   
     -- GET COMM_CD
    SELECT pkg1.id
    INTO l_COMM_CD
    FROM DUAL;
 
    set Remarks = 'TN_' || TO_CHAR(l_COMM_CD) ||  '.xml'
    where COMM_CD = l_COMM_CD;

        LOOP
         INSERT INTO COMMUNICATION_ITEM_TBL(
           COMM_ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           COMM_CD,
           LST_DT)
        VALUES(
           l_COMM_ITEM_SEQ,
           p_PROD_CD,
           l_COMM_CD,
           SYSTIMESTAMP);
       COMMIT;
        INSERT INTO TRNS_TBL (
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           ORDER_ITEM_CD,
           LAST_DT )
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           l_ORDER_ITEM_CD,
           SYSTIMESTAMP
           );
        COMMIT;
        INSERT INTO COMM_ITEM_TBL(
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           LAST_DT)
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           SYSTIMESTAMP,
           );
        COMMIT;
        l_COMM_ITEM_SEQ := l_COMM_ITEM_SEQ+1;

        EXIT WHEN l_COMM_ITEM_SEQ > p_COMM_ITEM_SEQ_NO;
   END LOOP;
  
   END ;

Re: Need to remove logic for inserting multiple rows [message #327837 is a reply to message #327820] Wed, 18 June 2008 02:51 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

[Updated on: Wed, 18 June 2008 04:16]

Report message to a moderator

Re: Need to remove logic for inserting multiple rows [message #327873 is a reply to message #327820] Wed, 18 June 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reported Messages

Reported By: Olivia On: Wed, 18 June 2008 11:17 In: SQL & PL/SQL SQL & PL/SQL Need to remove logic for inserting multiple rows
Reason Please kindly remove all the messages for the subject line "Need to remove logic for inserting multiple rows "

We don't remove messages and topics unless they are offending or duplicates.

In addition, if you finally got a solution, please post it, it is fair for all that helped you.

Regards
Michel
Re: Need to remove logic for inserting multiple rows [message #327882 is a reply to message #327820] Wed, 18 June 2008 04:40 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
get rid of your loop all together


   
     -- Get Order item code
    SELECT  pkg1.id
    INTO    l_ORDER_ITEM_CD
    FROM    dual;
   
     -- GET COMM_CD
    SELECT pkg1.id
    INTO l_COMM_CD
    FROM DUAL;


also replace the above lines with
      l_ORDER_ITEM_CD := pkg1.id;
      l_COMM_CD := pkg1.id;

no need to use dual Smile
Re: Need to remove logic for inserting multiple rows [message #327904 is a reply to message #327882] Wed, 18 June 2008 06:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@Michel
I asked to remove the posts as I was not getting response for this.
And even the requirement that I was getting was a bit confusing!!
Only One row with seq 1 should be inserted:If this is the case I would remove the LOOP and Increment Logic. Am I right?
Need your suggesion. I found that in those three tables ORDER_ITEM_SEQUENCE_NBR has duplicate values.

If not the case then I would go like this...


CREATE OR REPLACE PROCEDURE  TEST(
 p_COM_CD                          IN  number,
 p_PROD_CD                         IN  NUMBER ,
 p_COMM_ITEM_SEQ_NO                IN  NUMBER,
 RC1                               OUT Pkg1.RC1
)
 AS
 l_ERRMSG                    VARCHAR2(1000);
 l_ORDER_ITEM_CD             NUMBER(10):= pkg1.id;
 l_COMM_CD                   NUMBER(9):= pkg1.id;
 l_COMM_ITEM_SEQ             NUMBER := 1;

 BEGIN
   
   /* Removed as suggested by Martin 
     -- Get Order item code
    SELECT  pkg1.id
    INTO    l_ORDER_ITEM_CD
    FROM    dual;
   
     -- GET COMM_CD
    SELECT pkg1.id
    INTO l_COMM_CD
    FROM DUAL;
    */


    set Remarks = 'TN_' || TO_CHAR(l_COMM_CD) ||  '.xml'
    where COMM_CD = l_COMM_CD;
   
   /* Remove multiple row insertion logic from here 
        LOOP
         INSERT INTO COMMUNICATION_ITEM_TBL(
           COMM_ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           COMM_CD,
           LST_DT)
        VALUES(
           l_COMM_ITEM_SEQ,
           p_PROD_CD,
           l_COMM_CD,
           SYSTIMESTAMP);
       COMMIT;
        INSERT INTO TRNS_TBL (
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           ORDER_ITEM_CD,
           LAST_DT )
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           l_ORDER_ITEM_CD,
           SYSTIMESTAMP
           );
        COMMIT;
        INSERT INTO COMM_ITEM_TBL(
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           LAST_DT)
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           SYSTIMESTAMP,
           );
        COMMIT;
        l_COMM_ITEM_SEQ := l_COMM_ITEM_SEQ+1;
         
        EXIT WHEN l_COMM_ITEM_SEQ > p_COMM_ITEM_SEQ_NO;
   END LOOP;
   */

   /* Modified for inserting single row with seq */
     	--Added to increment Community Item sequence number
	SELECT MAX(COMM_ITEM_SEQUENCE_NBR)+1 INTO    l_COMM_ITEM_SEQ FROM COMM_ITEM_TBL;

         INSERT INTO COMMUNICATION_ITEM_TBL(
           COMM_ITEM_SEQUENCE_NBR,
           PRODUCT_ID,
           COMM_CD,
           LST_DT)
        VALUES(
           l_COMM_ITEM_SEQ,
           p_PROD_CD,
           l_COMM_CD,
           SYSTIMESTAMP);
       COMMIT;
        INSERT INTO TRNS_TBL (
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           ORDER_ITEM_CD,
           LAST_DT )
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           l_ORDER_ITEM_CD,
           SYSTIMESTAMP
           );
        COMMIT;
        INSERT INTO COMM_ITEM_TBL(
           COMM_CD,
           COMM_ITEM_SEQUENCE_NBR,
           LAST_DT)
        VALUES(
           l_COMM_CD,
           l_COMM_ITEM_SEQ,
           SYSTIMESTAMP,
           );
        COMMIT;
        
   END LOOP; 
  
   END ;



Am I wrong here? Need your valuable suggestion.

@Martin
Thank for pointing out the correct way of writing code.I ahve updated the PL/SQL.Please suggests if I am wrong!

Regards,
Oli

Re: Need to remove logic for inserting multiple rows [message #327985 is a reply to message #327759] Wed, 18 June 2008 09:13 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I found that in those three tables ORDER_ITEM_SEQUENCE_NBR has duplicate values.
If the goal is to avoid having duplicate value, why not adding UNIQUE constraint on that column?
Previous Topic: Suggest for Error
Next Topic: allocating grades
Goto Forum:
  


Current Time: Sat Dec 03 15:53:16 CST 2016

Total time taken to generate the page: 0.22884 seconds