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

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?

CodePudding user response：

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;

CodePudding user response：

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