CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlstored-functions
Premanand
Premanand

Reputation: 13

Cursor on record table type in Oracle

I am having a declaration as shown below 

type respond_field
IS
  record
  (
    provider_id          VARCHAR2(100),
    provider_name        VARCHAR2(100));

type respond_field_group
IS
  TABLE OF respond_field INDEX BY BINARY_INTEGER;

I have created an variable for respond_field_group and i have populated values for this virtual table. My prblem is how to i can convert/transfer this virtual table to a cursor.

What i have tried is show below respond_values is variable of respond_field_group

OPEN v_result_cursor for SELECT provider_id FROM TABLE (Cast(respond_values AS respond_field));

But i am getting invalid datatype

Please help me out to find a solution for this problem

Upvotes: 1

Views: 7679

Answers (1)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

You cannot use pl/sql types like this, only sql types (i.e. types defined at schema level not at package or procedure level). Record and associative array (index by table) are pl/sql types. Instead you have to create appropriate object types and use them in your code, for example:

SQL> create type respond_field as object
  2  (
  3    provider_id   varchar2(100),
  4    provider_name varchar2(100)
  5  )
  6  /

Type created.

SQL> create type respond_field_group as table of respond_field
  2  /

Type created.

SQL> create or replace function test_fun return respond_field_group as
  2    l_rfgroup respond_field_group := respond_field_group();
  3  begin
  4    l_rfgroup.extend(2);
  5    l_rfgroup(1) := respond_field('abc', '123');
  6    l_rfgroup(2) := respond_field('def', '456');
  7    return l_rfgroup;
  8  end;
  9  /

Function created.

SQL> select * from table(test_fun);

PROVIDER_ID     PROVIDER_NAME
--------------- ---------------
abc             123
def             456

Upvotes: 4

Related Questions