Not able to access the object type returned by a plsql function

user_6637206 0
Viewed 44

I have created an object type as:

Create or replace type t1 as object
 (
  Account_id varchar2(20),
  Account_date date);

And wrote a function inside package body p1 that returns this object:

Function account_details(ac_id in account_table.account_id%type)
return t1 is

v_t1 t1;

Begin
 Select t1(account_id,
           Account_date)
        Into v_t1
        From account_table
        Where account_id=ac_id;
Return v_t1;

End account_details;

Package compiles successfully but when I try to execute the function and get values returned by it:

Select p1.account_details(1234) from dual;

I get the output as:

[P1.t1]

How do I get the values of this object returned.This object will always have only one record returned from the function always.

Will I be able to get some help here?

plsql oracle
2 Answers

You are getting the values back, it is just that SQL Developer displays the object in that way. You can get the attribute values like this:

Select account_details(1234).account_id
     , account_details(1234).account_date 
 from dual;
user_187470

Different tools will have different ways of displaying complex types, for example in SQL*Plus:

SQL> select account_details(1) acc from dual;

ACC(ACCOUNT_ID, ACCOUNT_DATE)
-----------------------------
T1('1', '23-SEP-21')

1 row selected.

To avoid relying on defaults, you need to specify the object attributes explicitly.

You can avoid re-executing the function for each attribute by calling it within an inline view:

select t.acc.account_id
     , t.acc.account_date
from   ( select account_details(1) acc from dual ) t
user_2304710
Related