Sunday, March 11, 2012

Caching results of Stored Procedure

I was wondering if there is anyway to cache the results of a stored procedure in order to gain speed ?

Or do I need to do something else clever ?

Here is my SQL

procedure spGetProcedureParams(v_procname in varchar2,
v_packagename in varchar2,
CurRef out typeRefCur,
v_errcode out integer,
v_errmesg out varchar2)

is
begin
v_errcode :=0;
open CurRef for
select argument_name,
data_type,
NVL(pls_type,'REF'),
in_out
from user_arguments
where upper(object_name) = upper(v_procname)
and upper(package_name) = upper(v_packagename)
order by sequence;
exception
when others then
v_errcode :=SQLCODE;
v_errmesg :=SQLERRM;
end spGetProcedureParams;

end pkgUtils;
/I'm not really sure what you are looking for. You could fetch the data into a collection like an index-by table. Whether this will improve performance depends on what you are going to do with it next.

No comments:

Post a Comment