Hi,
I read .txt files and parse them. Once done I need to Insert values into 2 tables this way:
1.) Insert into 1st table, with generating new sequence number;
2.) Insert into 2nd table, where row record must contain sequence number from Table1 (SEQ_ID.CURRVAL). And there can be multiple Inserts into this table, so I need to do Insert with arrays.
Here is a short example:
CREATE OR REPLACE PROCEDURE TEST_INSERT(
NAME_IN IN Myschema.Table1.NAME%TYPE,
DATE_IN IN Myschema.Table1.DATE%TYPE, -- 1st Table parameters
LOW_NO_IN IN Myschema.Table2.LOW_NO%TYPE,
MID_NO_IN IN Myschema.Table2.MID_NO%TYPE) -- 2nd table parameters that are arrays
IS
BEGIN
--First insert into Table 1 - here we get sequence number
INSERT INTO Myschema.Table1 (ID, NAME, DATE)
VALUES (SEQ_ID.NEXTVAL, NAME_IN, DATE_IN);
--Then insert into 2nd Table - here values can be multiple, so I need to do Insert with arrays, but how ?
INSERT INTO Myschema.Table2 (MY_ID, ID_FK, LOW_NO, MID_NO)
VALUES (SEQ_MY_ID.NEXTVAL, SEQ_ID.CURRVAL, LOW_NO_IN, MID_NO_IN);
END TEST_INSERT;
/
Can somebody show me how to write a procedure that could do all this - I would prefer an example on procedure I wrote. Thanks in advance !!