How to populate nested table (with nested objects) by using cursor and bulk collect

Question

maybe you know where the problem is.

I have 3 types:

create or replace type t_money as object (
  val number(14,2)
 ,cur varchar2(3 CHAR)
);
/

create or replace type t_wallet as object (
  name   varchar2(50 CHAR)
 ,amount t_money
);
/

create or replace type t_wallets is table of t_wallet;
/

I need to populate nested table from cursor using bulk collect:

declare
  walletCollection t_wallets;
  cursor walletCursor is 
    select 'some name'          as name
          ,t_money(99, 'EUR')   as amount
      from dual;
begin
  open walletCursor;
  fetch walletCursor bulk collect into walletCollection;
  close walletCursor;
end;

Aaaaaaand ... it doesn't work. I get this error:

ORA-06550: line 9, column 40: PLS-00386: type mismatch found at 'WALLETCOLLECTION' between FETCH cursor and INTO variables

I knew that i can use:

type walletRecords is table of walletCursor%ROWTYPE;
walletCollection walletRecords;

But in this case i can't do that and walletCollection must be the nested table of t_wallets.

How to do that ? Where is the mismatch ?

Oracle Live Script https://livesql.oracle.com/apex/livesql/s/hr22zxdw7842um41u9ylnraz1

Answer 1

The mismatch is obvious: your cursor is on a set of rows with two columns, of type VARCHAR2 and T_MONEY respectively, but the nested table expects objects of type T_WALLET. Somewhere, somehow, you must construct objects of type T_WALLET from the data in the cursor.

Assuming the SELECT statement in your cursor definition simulates an actual table with two columns, you can wrap that within an outer query where you use the constructor. (Or else the table, or the SELECT statement, must already store, or create, T_WALLETs.)

declare
  walletCollection t_wallets;
  cursor walletCursor is
    select t_wallet(name, amount)   -- THIS outer select, using the constructor
    from   (
             select 'some name'          as name
                  , t_money(99, 'EUR')   as amount
             from   dual
           );
begin
  open walletCursor;
  fetch walletCursor bulk collect into walletCollection;
  close walletCursor;
end;
/

Here is a brief demo to show that the nested table is populated correctly. Notice the call to dbms_output.put_line in the procedure body; normally you would only do such thing for development and debugging purposes (and for illustration, as in this case). Make sure you run set serveroutput on to see the output.

declare
  walletCollection t_wallets;
  cursor walletCursor is
    select t_wallet(name, amount) 
    from   (
             select 'some name'          as name
                  , t_money(99, 'EUR')   as amount
             from   dual
           );
begin
  open walletCursor;
  fetch walletCursor bulk collect into walletCollection;
  close walletCursor;

  for i in 1 .. walletCollection.Count loop
    dbms_output.put_line( 'Name: '       || walletCollection(i).name       || 
                          ', amount: '   || walletCollection(i).amount.val ||
                          ', currency: ' || walletCollection(i).amount.cur );
  end loop;
end;
/

Name: some name, amount: 99, currency: EUR


PL/SQL procedure successfully completed.