Re: ROWTYPE

From: Björn Wächter <Bjoern.Waechter_at_web.de>
Date: Wed, 22 Jun 2011 00:32:02 -0700 (PDT)
Message-ID: <f1b3e266-c038-4fdc-813f-43c5f0c91404_at_j31g2000yqe.googlegroups.com>

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!
Received on Wed Jun 22 2011 - 02:32:02 CDT