Changing FOR/LOOP INSERT INTO to INSERT INTO .... SELECT with TYPES/COLLECTIONS
Posted on 2007-08-01
The following code works like a charm,..
CREATE OR REPLACE PROCEDURE InvDates(p_invoice_rec invoice_typ,
FOR rec IN (SELECT tbl.exp_date, tbl.inv_date, tbl.pay_date
FROM TABLE (CAST (p_date_rec AS date_typ_tbl)) tbl) LOOP
INSERT INTO invoice_dates
The data in the table would then look something like this:
INVOICE_DATE_ID INV_DESC INV_NBR EXP_DATE INV_DATE PAY_DATE
00000000001234 Mountain Dew 1349871 07/07/2008 07/07/2007 07/15/2007
00000000001235 Mountain Dew 1349871 07/07/2009 07/07/2008 07/15/2008
00000000001236 Mountain Dew 1349871 07/07/2010 07/07/2009 07/15/2009
Instead of looping through the collection I now need to use INSERT INTO .... SELECT. I understand the basic concept of INSERT INTO tbl_name (val1, val2, val3) SELECT nextval('DATE_SEQ') as val1, val2, val3 FROM tbl_name2;
However, I've just started working with collections and I'm not sure how to get this all in one SELECT statement for the values. As I need a unique id for each row in the date_typ_tbl and the "constant" values of the invoice_typ that would be passed in. Thanks in advance for your help.