Oracle - inserting cursor data into a custom type table

Question

I have a type like

CREATE OR REPLACE TYPE MY_TYPE AS OBJECT
(
  id               NUMBER(10, 0),
  name             VARCHAR2(4),
  lastName         VARCHAR2(13),
  address          VARCHAR2(30),
  previousAddress  VARCHAR2(80)
);

and a table of these as

CREATE OR REPLACE TYPE MY_TYPE_ROWS AS TABLE OF MY_TYPE

what I want to do is insert some rows that are returned from a cursor in this "MY_TYPE_ROWS" table. (and i want to do that in a pl/sql procedure)

The data that I want to insert into "MY_TYPE_ROWS" are in a cursor like:

cursor dataCursor IS 
select 
id,name, lastName,address,previousAddress
from table1;

(cursor returns more than 1 row)

I've tried something like:

 my_table_rows           MY_TYPE_ROWS := MY_TYPE_ROWS ();

  OPEN dataCursor ;
  FETCH dataCursor 
  INTO my_table_rows;
  CLOSE dataCursor ;

but i am getting an exception "Error: PLS-00386: type mismatch found at 'my_table_rows' between FETCH cursor and INTO variables"

thanks

smnbbrv · Accepted Answer · 2014-02-11 15:49:55Z

1

Can't you do like that?

declare
  my_table_rows           my_type_rows;
begin
  select my_type(id,name,lastname,address,previousaddress)
  bulk collect into my_table_rows
  from table1;

  -- do whatever you want
end;

The problem of your method is that you are trying to select one row by single into which even does not contain any object.

And by definition it should be an array of objects. So bulk collect implements array fetching, and my_type(id,name,lastname,address,previousaddress) implements creating the objects of the array.

edited Feb 11, 2014 at 15:49

answered Feb 11, 2014 at 15:44

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle - inserting cursor data into a custom type table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related