-
Creating SQL on the fly
I've got the procedure below that appends a parent record, returns the CURRVAL which is used when appending child records. The number of child record that can be appended is 2 maximum. To append the child records I've created a FOR Loop but I need to create the values on the fly, depending on the loop counter value, but I'm not sure how. Any ideas.
Thanks in Advance
Roy
create or replace procedure EMP_BENEFIT_INSERT
(--parent variables
lname in varchar2,
fname in varchar2,
sDate in varchar2,
eDate in varchar2,
bDate in varchar2,
eComments in varchar2,
Bcntr in number,
--child variables
--BENEFIT 1
benID_1 in number,
benStart_1 in varchar2,
benEnd_1 in varchar2,
benComment_1 in varchar2,
--BENEFIT 2
benID_2 in number,
benStart_2 in varchar2,
benEnd_2 in varchar2,
benComment_2 in varchar2)
is
parent_ID number;
Lcntr number;
strAppReg varchar2(30);
strBen varchar2(30);
begin
--1. Insert Parent Rec
INSERT INTO
EMP_Details (LastName, FirstName,START_DATE, END_DATE, D_o_B, eCOMMENT)
VALUES
(LName, fName, TO_DATE(sDate,'dd-mon-yyyy'), TO_DATE(eDate,'dd-mon-yyyy'), TO_DATE(bDate,'dd-mon-yyyy'), eComments));
--2. Return CurrVal for new parent record
SELECT SEQ_EMP.currval INTO parent_ID FROM dual;
--3. Insert Benefits
FOR Lcntr IN 1..Bcntr
LOOP
INSERT INTO
EMP_Benefits (BENEFIT_ID, BEN_START, BEN_END, BEN_COMMENT)
VALUES
--I need to construct this on the fly?
(benID_1, benStart_1, benEnd_1, benComment_1);
END LOOP;
end EMP_BENEFIT_INSERT;
-
You can use EXECUTE IMMEDIATE command, or use DBMS_SQL package.
http://download.oracle.com/docs/cd/B...9dyn.htm#26799
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|