Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Procedure to insert arrays ?

LuckyLuke82Aug 27 2019 — edited Aug 29 2019

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 !!

This post has been answered by Stew Ashton on Aug 27 2019
Jump to Answer
Comments
Post Details
Added on Aug 27 2019
32 comments
3,591 views