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  |
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 #327762 is a reply to message #327761] |
Tue, 17 June 2008 08:47  |
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 #327774 is a reply to message #327769] |
Tue, 17 June 2008 09:18 |
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 #327778 is a reply to message #327776] |
Tue, 17 June 2008 09:39 |
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 #327820 is a reply to message #327785] |
Wed, 18 June 2008 02:06 |
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 #327904 is a reply to message #327882] |
Wed, 18 June 2008 06:15 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 09:58:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
