CODEWITHSUNDEEP
oracle-databaseplsql
Blood-HaZaRd
Blood-HaZaRd

Reputation: 2124

retrieve content of a function

I created a function in oracle, which returns one single record, as follow :

create or replace function exportFile_TC return COMPENSE_TC_TABLE AS 
     retval COMPENSE_TC_TABLE := COMPENSE_TC_TABLE();
begin
   retval.extend;
   retval(retval.count) := COMPENSE_TC('A', 0, '788', '000000002582055', '0778153', '10209077105333978893', '08025011091000160486', 'TRANS TRAVEL', 'HTL EDEN', 1, null, null, '00000000000000000155', null, '00000000', '20150209', '08', null, null, null);
   return(retval);
end exportFile_TC;

Now when I do a query :

select exportFile_TC from dual;

all i get is :

EXPORTFILE_TC
-------------
<Object>

what I need is how to be able to retreive the content of the return value of my function. ? it is my first time using oracle functions, so may be I missed sthing.

Upvotes: 1

Views: 50

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use a table collection expression:

The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.

So in your case as your function is returning a nested table type you can do:

select * from table(exportFile_TC);

Each field in your object type will be shown as a column in the result set.

Upvotes: 4

Related Questions