ROWTYPE

From: The Magnet <art_at_unsu.com>
Date: Mon, 20 Jun 2011 12:23:02 -0700 (PDT)
Message-ID: <9c0cbd40-690a-45dc-b638-40c4bac8cb94_at_28g2000yqu.googlegroups.com>

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. Received on Mon Jun 20 2011 - 14:23:02 CDT