Have successfully returned rows from a function locally.

Tried calling function across a dblink and got error PLS-00453 'remote operations not permitted on object tables or user-defined type columns'.

Metalink Note 136352.1 provides a workaround which involves referencing columns as individual components.

Are there any other (more elegant) workarounds of which you are aware?

In this example, if I just want to assign a value to one element of the record:

instead of

some_data(i) := myRecordType( i, sysdate+i, 'record ' || i );

do

some_data(i).y := sysdate + i

When I execute the function, I got error:

ORA-06530: Reference to uninitialized composite

Here is why I want to do that:

I need to return a cursor result to an object that is a table of a user defined record.

The structure of my object (just use emp as an example, my real code is more complicated than this):

create or replace type emp as object (
empno number,
ename varchar2(20),
total_sick_days number,
total_vacation_days number,
total_salary number,
total_commission number)
/

create or replace type t_emp as table of emp
/

I am thinking about filling in the object with empno, ename first, and then adding in other values through query other tables and do some calculations. The reason I chose object is because the result need to be returned to java program. I've spent days on this and really don't know if my approach is possible.

I would appreciate it very much if you can give me some advice.

that error:

ORA-06530: Reference to uninitialized composite

simply means "some_data(i) is atomically null, it has no value, there is no .y yet"

You need to assign at least an empty record to it, either of:

ops$tkyte@ORA817DEV> declare
  2          some_data myTableType := myTableType();
  3  begin
  4          some_data.extend;
  5          some_data(1) := myRecordType(null,null,null);
  6          some_data(1).b := sysdate;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> declare
  2          some_data myTableType := myTableType();
  3  begin
  4          some_data.extend;
  5          some_data(1) := myRecordType(null,sysdate,null);
  6  end;
  7  /

PL/SQL procedure successfully completed.


it what you are looking for. 

Your response answered part of my question.
The other part of the question is I'm trying to solve the following problem, but I don't even know if my plan is feasible or if there is a better way of solving it.

The problem is I need to create something like the following and pass it as an out parameter to Java.

(I'll use emp to illustrate this)

empno ename sick_days total_salary total_commission
1 David 5 $55000 $3000
2 Mike 3 $60000
3 Mark 2 $40000 $4000
4 Smith 0 $70000 $1000

The above data can't be obtained by one single query (at least for my real problem). Otherwise, I would just return a ref cursor.
A query can get empno and ename, but other data have to be obtained and calculated one by one. My plan was to use Oracle object of nested table type. Fill in empno and ename first, then add other value one by one.

Your previous answer helped me on filling in empno, ename part. But how can I fill in other data?
Again, any suggestion/advice will be greatly appreciated!

Well, I actually find it almost unbelievable that you cannot do that in a query -but..  here you go:


ops$tkyte@DEV816> create or replace function my_function return myTableType
  2  as
  3      some_data myTableType := myTabletype();
  4  begin
  5      for x in ( select empno, ename from .... )
  6          some_data.extend;
  7          some_data(some_data.count) :=
  8              myRecordType( x.empno, x.ename, null, null, null, null );
             some_data(some_data.count).sick_days := function();
             some_data(some_data.count).total_salary := function();
             ....
  9      end loop;
 10  
 11      return some_data;
 12  end;
 13  /


and there you go. 

The answer was very inspiring and helpful! I used emp to illustrate my problem. The real problem is to deal with some product inventory report between 2 dates (BTW, this is one of the most complicated output of out project!). I would love it if it can be done with one query. I'll list the real problem below, which may be a little too detail and too specific for a forum like this. But I would love to hear from you for suggestions, even a brief one.

My second question is: I read that you can do SELECT, UPDATE, and DELETE operation for nested table, but I have never seen a real life example. I wonder if I could make use of them here?

Here is the raw data:

db1:dev> select a.product_id, c.tx_type_name, sum(a.final_quantity)
2 from transaction_item a, transaction b, transaction_type c
3 where a.tx_id = b.tx_id
4 and b.tx_type_id = c.tx_type_id
5 group by a.product_id, c.tx_type_name;

PRODUCT_ID TX_TYPE_NAME SUM(A.FINAL_QUANTITY)
---------- ------------------------------ ---------------------
1 Return 102
1 Shipment 1000
1 Transfer In 96
1 Distribution 115
1 Transfer Out 50
2 Return 103
2 Shipment 1005
2 Transfer In 80
2 Distribution 135
2 Transfer Out 60
3 Return 104

PRODUCT_ID TX_TYPE_NAME SUM(A.FINAL_QUANTITY)
---------- ------------------------------ ---------------------
3 Shipment 900
3 Transfer In 306
3 Distribution 246
3 Transfer Out 70

15 rows selected.

select a.product_id, a.final_quantity as begin_inv_qty
from inventory_item a, inventory b
where a.inventory_id = b.inventory_id
and b.inv_name_id = 1
order by product_id;

PRODUCT_ID BEGIN_INV_QTY
---------- -------------
1 354
2 503
4 150
5 12

select a.product_id, a.final_quantity as end_inv_qty
from inventory_item a, inventory b
where a.inventory_id = b.inventory_id
and b.inv_name_id = 2
order by product_id;

PRODUCT_ID END_QTY
---------- ----------
1 612
2 445
3 228

The desired output should be:

Product_id begin_inv_qty Return Shipment ... end_inv_qty
1 354 102 1000 ... 612
2 445 103 1005 ... 445
3 0 104 900 ... 228
4 150 0 0 ... 0
5 12 0 0 ... 0
Grand total 961 309 2905 ... 1957

Tom,

your web site is very good. However, if the answer
sesction is embedded in

<table width=100% cellpadding="0" cellspacing="0" order="0">
<tr valign=top>
<td valign="top"> forum discussion </td>
</tr>
</table>

would keep it within screen width. Due to my eye weakness I
have to increase font slightly and that ruins the format. It scrolls horizontally so much, I lose track of what I am
reading.
Is it doable?

Brilliant, and I can think of several places where ref_cursors could be very handy. My Question though relates to how SQL Plus manages to print out the sql results set.

If my reckoning is right, all ":C" is is a bind variable pointer the the SQL query. SQL Plus pulls the results back with a call to print.

My question is how does SQL Plus know what the result set looks like (i.e column names and types)

I assume that there are some OCI calls going on that are similar in operation to DBMS_SQL.DESCRIBE_COLUMNS??

Or is it just a miracle from SQL +??

Your query runs beautifully! I never dreamed this could be done with a single query. I guess there's so much to learn for me, and your website is a great place for that. I have to be really patient to wait for the opportunity to submit my question. Keep up with the great work.

BTW, do you have any example of how to use SELECT, UPDATE, DELETE for nested table object type in PL/SQL?

I wanted to create a function which will return multiple records. For example:
create or replace function getename(v_deptno in number ) return varchar2 is
cursor getename(v_deptno in number ) is
select ename
from emp where deptno = v_deptno;
v_ename VARCHAR2(2000);
Begin
open getename( v_deptno);
Fetch getename into v_ename;
return v_ename;
close getename;
End getename;
/
but this is displaying only first record. Please help me.

is it possible with out using ref cursors and nested tables as parameter.

Thanks in advance

If i want to write the same code using a function,Please let me know how to do using refcursors with out using packages.