Hi,

I'm taking another shot at this. I'm trying to create a nested table,
then use a BULK COLLECT & FORALL insert. Here is my code, but no
matter what I do, I cannot get the procedure to compile. Please note,
the CURSOR selects data from multiple tables. The nested table will
be in the RESULT table, it does not come from any of the source
tables. I'll have PL/SQL code to populate that nested table.

No matter what I do, I mostly get this error:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR

CREATE TYPE est_date_list AS TABLE OF DATE;

CREATE TABLE STOCK_INFO (
ticker VARCHAR2(10),
..
..
estimate_dates est_date_list);

CURSOR ticker_detail IS
SELECT mt.ticker, . . . . NULL est_date_list
FROM stock_data sd,
master_table mt,
daily_rank r,
trg_price_est_cur tp,
comp_descr cd,
zr_recom_cur zrc
WHERE mt.ticker = sd.m_ticker
AND mt.ticker = cd.ticker(+)
AND mt.ticker = r.ticker(+)
AND mt.ticker = tp.master(+)
AND mt.ticker = zrc.ticker(+);


### Note here the nested table comes into play for the STOCK_INFO
table, so I am trying to use use a NULL value to create the column in
the cursor.


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_snapshot VALUES v_read_record(y);


Procedure will not compile with the above declarations.

Anyone with the answer is a hero.

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.

On Jun 21, 2:28*am, Björn Wächter <Bjoern.Waech... (AT) web (DOT) de> wrote:
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!

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 (DOT) com> wrote:

On Jun 22, 2:32*am, Björn Wächter <Bjoern.Waech... (AT) web (DOT) de> wrote:
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;