I am using several package functions to return ref cursors to a Java front end. Each function returns a ref cursor. The data structure is the same in each function.
Say the business requirement changes so that the data should be displayed in a different order. We would change the Java so that instead of calling function1, function2 function3 we call function3, function2, function1.
Now, because we place all business logic in the database where possible, we want the database to decide how the data is displayed, not the Java.
Is it possible to write a single function that concatenates the ref cursors returned by the three functions? The Java could then call this single function and not care about how the data is ordered.
I know that the three select statements could be unioned as follows...
SELECT somedata FROM table1
UNION
SELECT somedata FROM table2
UNION
SELECT somedata FROM table3
ORDER BY 1;
...but this would probably be less efficient. Please assume that for the fastest possible response time we need to keep the three select statements separate but concatenate the output.
No, a ref cursor represents a single query.
I will say I disagree with this statement:
...
Please assume that for the fastest possible response time we need to keep the three select statements separate but concatenate the output.
......
a lot. If you take (query1) UNION ALL (query2) UNION ALL (query3) - that'll give you the same response times (or better even! less work done between client and server) than would running query1, query2 and query3.
Perhaps you are gun shy of using UNION - because q1 union q2 is really DISTINCT(q1+q2) whereas q1 UNION ALL q2 is (q1+q2)
that is, maybe you have been using the wrong set operation in the past!!
That said - you can achieve this via a pipelined function perhaps. Consider:
ops$tkyte%ORA10GR2> create or replace type myScalarType as object
2 ( str varchar2(30), dt date, num number )
3 /
Type created.
ops$tkyte%ORA10GR2> create or replace type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte%ORA10GR2> create or replace package foo
2 as
3 function f1( p_f1 in number ) return sys_refcursor;
4 function f2( p_f2 in number ) return sys_refcursor;
5 function f3( p_f3 in number ) return sys_refcursor;
6
7 function all_together_now
8 ( p_f1 in number, p_f2 in number, p_f3 in number,
9 p_first in number, p_second in number, p_third in number )
10 return myTableType
11 pipelined;
12 end;
13 /
Package created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body foo
2 as
3
4 function f1( p_f1 in number ) return sys_refcursor
5 is
6 l_cursor sys_refcursor;
7 begin
8 open l_cursor for
9 select 'f1', sysdate-1, rownum
10 from all_objects
11 where rownum <= p_f1;
12 return l_cursor;
13 end;
14 function f2( p_f2 in number ) return sys_refcursor
15 is
16 l_cursor sys_refcursor;
17 begin
18 open l_cursor for
19 select 'f2', sysdate-1, rownum
20 from all_objects
21 where rownum <= p_f2;
22 return l_cursor;
23 end;
24 function f3( p_f3 in number ) return sys_refcursor
25 is
26 l_cursor sys_refcursor;
27 begin
28 open l_cursor for
29 select 'f3', sysdate-1, rownum
30 from all_objects
31 where rownum <= p_f3;
32 return l_cursor;
33 end;
34
35 function all_together_now
36 ( p_f1 in number, p_f2 in number, p_f3 in number,
37 p_first in number, p_second in number, p_third in number )
38 return myTableType
39 pipelined
40 is
41 type array is table of number;
42 l_data array := array( p_first, p_second, p_third );
43 l_cursor sys_refcursor;
44 l_datum myScalarType := myScalarType( null, null, null );
45 begin
46 for i in 1 .. l_data.count
47 loop
48 if ( l_data(i) = 1 ) then l_cursor := f1( p_f1 );
49 elsif ( l_data(i) = 2 ) then l_cursor := f2( p_f2 );
50 elsif ( l_data(i) = 3 ) then l_cursor := f3( p_f3 );
51 end if;
52 loop
53 fetch l_cursor into l_datum.str, l_datum.dt, l_datum.num;
54 exit when l_cursor%notfound;
55 pipe row( l_datum );
56 end loop;
57 close l_cursor;
58 end loop;
59 return;
60 end;
61 end;
62 /
Package body created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from TABLE( foo.all_together_now( 1, 2, 3, 3, 2, 1 ) );
STR DT NUM
------------------------------ --------- ----------
f3 20-AUG-07 1
f3 20-AUG-07 2
f3 20-AUG-07 3
f2 20-AUG-07 1
f2 20-AUG-07 2
f1 20-AUG-07 1
6 rows selected.
ops$tkyte%ORA10GR2> select * from TABLE( foo.all_together_now( 1, 1, 1, 2, 3, 1 ) );
STR DT NUM
------------------------------ --------- ----------
f2 20-AUG-07 1
f3 20-AUG-07 1
f1 20-AUG-07 1
I'm not saying that java would control the order of invocation as I have done - I just did that so as to show we can control it anyway you like...