Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Changing FOR/LOOP INSERT INTO to INSERT INTO .... SELECT with TYPES/COLLECTIONS

Posted on 2007-08-01
2
Medium Priority
?
1,739 Views
Last Modified: 2008-01-09
The following code works like a charm,..
CREATE OR REPLACE PROCEDURE InvDates(p_invoice_rec   invoice_typ,
                                                                          p_date_rec      date_typ_tbl)
AS
l_dist_id       NUMBER;

BEGIN

  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

           SELECT date_seq.NEXTVAL
                INTO l_dist_id
                FROM dual;

           INSERT INTO invoice_dates
                                (invoice_date_id,
                                 inv_desc,
                                 invoice_nbr,
                                 exp_date,
                                 inv_date,
                                 pay_date)
                  VALUES
                               (l_dist_id,
                                 p_invoice_rec.desc,
                                 p_invoice_rec.inv_nbr,
                                 rec.exp_date,
                                 rec.inv_date,
                                 rec.pay_date);
   END LOOP;
   COMMIT;
END;

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.

I
0
Comment
Question by:roschera
2 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 2000 total points
ID: 19613288
See if this works
CREATE OR REPLACE PROCEDURE InvDates(p_invoice_rec   invoice_typ,p_date_rec      date_typ_tbl)
AS
l_dist_id       NUMBER;

BEGIN

INSERT INTO invoice_dates
(invoice_date_id,
inv_desc,
invoice_nbr,
exp_date,
inv_date,
pay_date)
(select date_seq.nextval,p_invoice_rec.desc, p_invoice_rec.inv_nbr,tbl.exp_date,tbl.inv_date, tbl.pay_date from
 TABLE (CAST (p_date_rec AS date_typ_tbl)) tbl))

   COMMIT;
END;

0
 

Author Comment

by:roschera
ID: 19619774
Thanks!  Your answer worked like a charm.  I was trying to make it a lot more complicated than it needed to be. :)

Thank you for answering so quickly!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question