Re: performance question with function returning object type

From: <bung_ho_at_hotmail.com>
Date: 16 Dec 2004 09:23:17 -0800
Message-ID: <1103217797.166955.185830@f14g2000cwb.googlegroups.com>

well, i don't know if there is a better way to do this, but this is how i got your code to work without calling the function 4x:

---

SQL> create or replace type res_util_typ 2 as object (pct_util number(4),

3  morn_avail number(4),
4  aftn_avail number(4),
5  eve_avail number(4))

6 /

Type created.

SQL> create or replace type blah as table of res_util_typ; 2 /

Type created.

SQL> CREATE OR REPLACE PACKAGE ALLAN_PKG AS 2 function get_utilization return res_util_typ; 3 function get_u return blah;
4 end;
5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY ALLAN_PKG AS 2 function get_u return blah is
3 rtu res_util_typ;
4 ret blah;
5 begin
6 rtu := get_utilization;
7 ret := blah();
8 ret.extend;
9 ret(1) := rtu;
10 return ret;
11 end;
12 function get_utilization return res_util_typ is

13  retVal          res_util_typ;
14  pct_util        number(4) := 0;
15  tot_used        pls_integer := 0;
16  tot_avail_m     pls_integer := 0;
17  tot_avail_a     pls_integer := 0;
18  tot_avail_e     pls_integer := 0;

19 begin
20 select least(count(*), 9999)
21 into pct_util
22 from all_objects;
23 select least(count(*), 9999)
24 into tot_avail_m
25 from all_objects;
26 select least(count(*), 9999)
27 into tot_avail_a
28 from all_objects;
29 select least(count(*), 9999)
30 into tot_avail_e
31 from all_objects;
32 retVal := res_util_typ(pct_util, tot_avail_m, tot_avail_a, 33 tot_avail_e);
34 return retVal;
35 end;
36 END ALLAN_PKG;
37 /

Package body created.

SQL> set autot on
SQL> set timing on
SQL> select pct_util, morn_avail, aftn_avail, eve_avail from

table(cast(allan_pkg.get_u as blah));

PCT_UTIL MORN_AVAIL AFTN_AVAIL EVE_AVAIL
---------- ---------- ---------- ----------
9999 9999 9999 9999

Elapsed: 00:00:08.92

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COLLECTION ITERATOR (PICKLER FETCH) Statistics

---

16 recursive calls
16 db block gets
229598 consistent gets
0 physical reads
0 redo size
308 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

---

(i did this in 8i, so i had to change your CASE statements a little bit.)

basically all i did was to create another type ("blah") that is a table of your type, wrote a wrapper function that created a "blah" with a single res_util_typ in it. then, from sql i called the wrapper function with the table(cast( ...)) bit so that it can be accessed as a table in sql.

shrug. there's probably a better workaround to your problem, but until then ... Received on Thu Dec 16 2004 - 11:23:17 CST