Re: ROWTYPE
Date: Wed, 22 Jun 2011 06:48:05 -0700 (PDT)
Message-ID: <05f71bba-0c20-416c-8678-b9bc2f8e85a1_at_m22g2000yqh.googlegroups.com>
On Jun 22, 2:32 am, Björn Wächter <Bjoern.Waech..._at_web.de> wrote:
> Hi,
>
> you can fill it this way:
>
> DECLARE
>
> CURSOR ticker_detail IS
> SELECT
> ticker,
> EST_DATE_LIST(SYSDATE, SYSDATE, SYSDATE) ESTIMATE_DATES
> FROM
> STOCK_INFO sto;
>
> TYPE v_ticker_table IS TABLE OF ticker_detail%ROWTYPE INDEX BY
> BINARY_INTEGER;
> v_read_record v_ticker_table;
>
> BEGIN
>
> OPEN ticker_detail;
> FETCH ticker_detail BULK COLLECT INTO v_read_record;
> CLOSE ticker_detail;
>
> FORALL y IN v_read_record.FIRST .. v_read_record.LAST
> INSERT INTO STOCK_INFO VALUES v_read_record(y);
> END;
>
> or this way:
>
> DECLARE
>
> CURSOR ticker_detail IS
> SELECT
> ticker,
> CAST(COLLECT(tim.TS) AS EST_DATE_LIST) ESTIMATE_DATES
> FROM
> STOCK_INFO sto,
> (
> SELECT SYSDATE TS
> FROM DUAL
> UNION ALL
> SELECT SYSDATE+1 TS
> FROM DUAL
> ) tim
> GROUP BY ticker;
>
> TYPE v_ticker_table IS TABLE OF ticker_detail%ROWTYPE INDEX BY
> BINARY_INTEGER;
> v_read_record v_ticker_table;
>
> BEGIN
>
> OPEN ticker_detail;
> FETCH ticker_detail BULK COLLECT INTO v_read_record;
> CLOSE ticker_detail;
>
> FORALL y IN v_read_record.FIRST .. v_read_record.LAST
> INSERT INTO STOCK_INFO VALUES v_read_record(y);
> END;
>
> On Jun 21, 3:21 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
>
>
> > On Jun 21, 2:28 am, Björn Wächter <Bjoern.Waech..._at_web.de> wrote:
>
> > > Hi,
>
> > > I think the NULL is the Problem. If I define the cursor like this:
>
> > > CURSOR ticker_detail IS
> > > SELECT mt.ticker, . . . . EST_DATE_LIST() est_date_list
> > > FROM stock_data sd,
> > > master_table mt,
> > > ......
>
> > > It works for me.
>
> > Very nice! I'm not sure exactly how Oracle is reading that, because
> > EST_DATE_LIST() is a TYPE, not a column. But it works. Now here is
> > the kicker, is there any way for me to select data from a table
> > directly into that type?
>
> > So, for example I have this from above:
>
> > BEGIN
> > OPEN ticker_detail;
> > FETCH ticker_detail BULK COLLECT INTO v_read_record;
> > CLOSE ticker_detail;
>
> > Now, v_read_record is a collection, with EST_DATE_LIST() as one of the
> > elements. I'd like to select data (4 values) from another table and
> > place them into EST_DATE_LIST() in the collection, or am I stuck
> > selecting the data into variables, and then assigning the variables
> > like so:
>
> > v_dates_tab := estimate_date_type(v_qr1_end_date, v_qr2_end_date,
> > v_fr1_end_date, v_fr2_end_date);
> > v_read_record(x).estimate_dates := v_dates_tab;
>
> > Is there a way to select the values directly into v_read_record(x)?
>
> > Thanks again!
I wound up doing it with more PL/SQL code than I wanted to. What I am really trying to do is to select certain columns from TABLE A to be inserted into TABLE B. However 4 of the columns selected from TABLE A should be put into this nested table in TABLE B.
So I was looking for a way to select all the individual columns, hence the cursor, AND combine the columns that are to become the nested table, placing it all into 1 SELECT...FROM and INSERT INTO statement. I do not think this can be easily done.
So, I simply had to populate the array with the BULK COLLECT, then had to go through the array and update the array, something like this:
OPEN ticker_detail;
FETCH ticker_detail BULK COLLECT INTO v_read_record;
CLOSE ticker_detail;
FOR x IN v_read_record.FIRST .. v_read_record.LAST LOOP
SELECT qr1_end_date, qr2_end_date, fr1_end_date, fr2_end_date
INTO _qr1_end_date, v_qr2_end_date, v_fr1_end_date, v_fr2_end_date;
FROM stock_data
WHERE m_ticker = v_read_record(x).m_ticker;
v_dates_tab := estimate_date_type(v_qr1_end_date, v_qr2_end_date,
v_fr1_end_date, v_fr2_end_date);
v_read_record(x).estimate_dates := v_dates_tab;
END LOOP;
Received on Wed Jun 22 2011 - 08:48:05 CDT