Creating SQL on the fly

[barlowr70]

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;

[skhanal]

You can use EXECUTE IMMEDIATE command, or use DBMS_SQL package.

http://download.oracle.com/docs/cd/B...9dyn.htm#26799