Can't select data from table of object type oracle

Question

I created types: create or replace type t_rec_debts as table of t_rec_debt; and

create or replace type t_rec_debt as Object(
       ID_CON               number(10),
       V_NUMBER_CONTRACT    varchar2(150),
       V_LONG_TITLE         nvarchar2(2000),
       V_PRODUCT            varchar2(32),
       DT_BLOCK             date ,
       N_DAYS_TO_BLOCK      number ,
       N_PAY_SUM            number ,
       V_TEL_HOME           varchar2(255) ,
       V_TEL_WORK           varchar2(255) ,
       V_TEL_MOB            varchar2(255) ,
       V_EMAIL              varchar2(255) ,
       V_ADDRESS            varchar2(255) ,
       N_OBK                varchar2(200)
);

Then I created the collection: t_data_forecast_debts t_rec_debts;

and filled it (checked that it is filled with a trace).

Problem: how to return the cursor over this collection. In procedure I must return pdwr (sys_refcursor). I tried to use something like:

open pdwr for 
      select MT.dt_block
      from table(t_data_forecast_debts) MT;

but without success.

(Oracle version 11.2.0.4.0).

Thanks.

Answer 1

This is How I would do:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 

SQL> set SERVEROUT ON;
SQL> 
SQL> create or replace type t_rec_debt as Object(
  2         ID_CON               number(10),
  3         V_NUMBER_CONTRACT    varchar2(150),
  4         V_LONG_TITLE         nvarchar2(2000),
  5         V_PRODUCT            varchar2(32),
  6         DT_BLOCK             date ,
  7         N_DAYS_TO_BLOCK      number ,
  8         N_PAY_SUM            number ,
  9         V_TEL_HOME           varchar2(255) ,
 10         V_TEL_WORK           varchar2(255) ,
 11         V_TEL_MOB            varchar2(255) ,
 12         V_EMAIL              varchar2(255) ,
 13         V_ADDRESS            varchar2(255) ,
 14         N_OBK                varchar2(200)
 15  
 16  );
 17  /

Type created

SQL> create or replace type t_rec_debts as table of t_rec_debt;
  2  /

Type created

SQL> 
SQL> CREATE OR REPLACE PROCEDURE test_ref_cursor (p_ref_cur OUT SYS_REFCURSOR) AS
  2    t_data_forecast_debts t_rec_debts := t_rec_debts();
  3  BEGIN
  4      t_data_forecast_debts.extend(1);
  5      t_data_forecast_debts(1) :=  t_rec_debt (
  6                                             1,
  7                                             'TEST',
  8                                             'V_LONG_TITLE',
  9                                             'V_PRODUCT',
 10                                             SYSDATE,
 11                                             100,
 12                                             200,
 13                                             'V_TEL_HOME',
 14                                             'V_TEL_WORK',
 15                                             'V_TEL_MOB',
 16                                             'V_EMAIL',
 17                                             'V_ADDRESS' ,
 18                                             'N_OBK');
 19      OPEN p_ref_cur FOR SELECT * FROM TABLE(CAST(t_data_forecast_debts AS t_rec_debts));
 20  
 21  END;
 22  /

Procedure created

SQL> 
    SQL> DECLARE
  2      l_ref_cur         SYS_REFCURSOR;
  3      id_con            NUMBER(10);
  4      v_number_contract VARCHAR2(150);
  5      v_long_title      NVARCHAR2(2000);
  6      v_product         VARCHAR2(32);
  7      dt_block          DATE;
  8      n_days_to_block   NUMBER;
  9      n_pay_sum         NUMBER;
 10      v_tel_home        VARCHAR2(255);
 11      v_tel_work        VARCHAR2(255);
 12      v_tel_mob         VARCHAR2(255);
 13      v_email           VARCHAR2(255);
 14      v_address         VARCHAR2(255);
 15      n_obk             VARCHAR2(200);
 16  
 17  BEGIN
 18      -- Call the procedure
 19      test_ref_cursor(l_ref_cur);
 20      FETCH l_ref_cur
 21          INTO id_con,
 22               v_number_contract,
 23               v_long_title,
 24               v_product,
 25               dt_block,
 26               n_days_to_block,
 27               n_pay_sum,
 28               v_tel_home,
 29               v_tel_work,
 30               v_tel_mob,
 31               v_email,
 32               v_address,
 33               n_obk;
 34  
 35      dbms_output.put_line(id_con);
 36      dbms_output.put_line(v_number_contract);
 37      dbms_output.put_line(v_long_title);
 38      dbms_output.put_line(v_product);
 39      dbms_output.put_line(dt_block);
 40  
 41  END;
 42  /

1
TEST
V_LONG_TITLE
V_PRODUCT
06-OCT-15

PL/SQL procedure successfully completed

See the output printed is 1 that is value of id_con