Tom, I got the point. I would be listing the column names explicitly. Thanks for your advise.

Your site is amazing and extremely useful.

Tom,
Can you tell me what could be the best approach to get the following:
I want to send around 80 OUT REF Cursors from a procedure (one each for a table) to client (.NET). Currently i have 2 input parameters and using those values i've to select from 80 tables and pass it out onto .NET using 80 REF Cursors (it'll be cached in middle tier , rather than hitting Database ...for performance issues)

declaring 70-80 cursor as OUT parameters is the only way? or can it be done in a differnet way(better way)?

We are currently using Oracle 10g version

Thanks,

<quote>
it'll be cached in middle tier , rather than hitting Database ...for performance issues
</quote>

Performance reasons, huh?

My .NET developers tried that on me. I made them figure out what questions they were asking, created views based on those questions then created several procedures in a package that asked all the questions they needed for each particular task.

It works much nicer that way. If a view was unable to work the way we wanted, then we just put the SQL in the packaged procedure. As Tom pointed out in his followup, let the database do the work. It's much better at it.

Thanks, Tom and djb, That was my intial advice to just send a query (which was not a big one..just a Select), but my .NET developer and others decided to cache it for theri own reasons. I cannot argue with that, except give them what they need.
Anyways, since you do not have any special way to handle this, let me ask you a different one .
I am thinking of using
Select cursor(select * from a),cursor(select * from b)..
and send that to Middle Tier,as those selects are very simple and no manipulation need to be done.
My question now is , is there a way to add cursor in select dynamically, if number of tables increased in future, or we just have to hardcode the cursors?

I am going ot point them to this thread and let them decide what's the best way to do this.
Thanks for your help,

Tom, Thanks for follow up..
I have written a procedure ,something like this.
--Procedure Start

table_name_array dbms_sql.varchar2_table;
v_sql varchar2(2000):='';

begin
Get all table_names, bulk collect into table_name_array;

v_sql:='Select ';
for each array element
loop
v_sql:=v_sql||' Cursor (select * from '||table_name_array(index)||' ) ,';
end loop;

v_sql:=rtrim(v_sql,',')||' from dual';

open ref_cursor for v_sql;

--End of procedure

This way if they add a newtable, it's taken care of..

My question is Could this effect query performance than writing 80 or so select statements and passing them to Middle Tier?
I'm sorry if i am asking the same thing over and over. I just want to be sure when i tell them.. do it my way.

If you have any tips for the above, please do comment on them.

Thanks,

Maverick,

One of the considerations that you need to take into account is that the current version of ODP.NET does not support nested cursors. Used appropriately Ref Cursors can make your code more scalable and performant; however, like many things, it can be an inverted parabola -- you eventually reach a point of diminishing returns. This approach seems like a cursor maintenance nightmare (I wonder what value open_cursors will eventually be set to). Heck, I can't even imagine typing in the signature for a class method that has 80 parameters.

- Mark

... that you are using ODP.NET which you may not be. Virtually all of my posts of late have been on the ODP.NET forums, so I have that on the brain.

- Mark

Hi Maverick,

Seriously, don't do this. It is a really, really bad architecture.

I joined a project 2 years ago where developers had done just that - turned the middle-tier into an "object layer".

It was a disaster. We have spent the last 2 years "getting it right", moving data processing back to the database.

This has actually improved the scalability of the application through all tiers, including the database.

The philosophy of getting all the data up front just in case we need it produces "lumpy" database performance, especially around 8:30am in the morning when everyone starts up.

Aside from performance, what happens when someone updates? Is the whole cache refreshed?

What happens when one of the tables has a few thousand rows in it, and the .Net developers have to do a lookup by ID in their middle-tier code? I assume they'll be using .Net DataSets. They'll still be doing the equivalent of full scans, or creating indexes on the fly.

There is nothing good about this approach.

Wow, Looks like i was going into a complete wrong direction with this cursor thing. I'll take your experience into count and try to convince them not to have all the data in middle tier. If they still want to go that way, i'll give them what they want.
Thanks for all your follow ups.

Tom,
Is it possible to send Array of Refcursors ? if so, how to declare pl/sql Table?

If this is not right thread, please direct me to one. I could not find a particular thread for posting this

Thanks

ops$tkyte@ORA10G> declare type array is table of sys_refcursor index by binary_integer; begin null; end;
  2  /
declare type array is table of sys_refcursor index by binary_integer; begin null; end;
                      *
ERROR at line 1:
ORA-06550: line 1, column 23:
PLS-00990: Index Tables of Cursor Variables are disallowed
ORA-06550: line 1, column 9:
PL/SQL: Item ignored
 
 

I was looking at maverick's sample code using nested cursors, in which he mentioned dynamic sql in a loop.
I guess it creates a statement like
select cursor(select * from table1),cursor(select * from table2) from dual;

the output is a result set in each column

My question is, is there a way to make the output in such a way that the resut set comes in each row rather than each column?
I tried to seperate SQLs by using union and looks like we cannot use unions with nested curosor.

Any suggestions?
Thanks

Thanks for your response tom. If Array of Ref cursors is not possible, then how about ref cursor of arrays(other way around)?
I need to send OUT, several REF CURSORS to Java program, and was thinking if i could bundle them together in one array or ref cursor.

Any help is greatly appreciated,
Thanks

Thanks for taking time to respond. What happens if we have dynamic ref cursors and might send different no. of cursors depending on information from database? I thought of handling dynamic issue at procedure level and send only one cursor out to client.
Any ideas on this one?

I have a procedure like this:
PROCEDURE myproc(
i_rte_one_dlr_id IN contract_package.rte_one_dlr_id%type,
i_aplct_lname IN individual_applicant.fam_name%type,
i_aplct_fname IN individual_applicant.given_name%type,
i_aplct_ssn IN individual_applicant.ntl_id%type,
i_dealid IN contract_package.deal_num%type,
i_cntrtid IN credit_contract.doc_id%type,
i_st_date IN contract_package.last_actvty_date%TYPE DEFAULT NULL,
i_end_date IN contract_package.last_actvty_date%TYPE DEFAULT NULL,
i_fin_src IN dlr_fs_asc_view.fnc_src_abrvtd_name%type,
i_cntrt_stat IN contract_package.cntrt_status_code%type,
i_last_mod_by IN contract_package.last_mod_user_id%type,
o_recordset OUT CntrtMgr_tab,
o_rowcount OUT NUMBER,
o_error_cd OUT pkg_ec_error.err_code_typ,
o_error_desc OUT pkg_ec_error.err_msg_dscr_typ,
o_error_msg OUT pkg_ec_error.err_msg_dtl_typ,
o_error_type OUT pkg_ec_error.err_type_typ
)
AS

--Variable Declarations.

l_query VARCHAR2 (4000)
default 'SELECT fam_name,given_name,cntrt_status_code,collation_seq_num, ' ||
' cp.cntrt_wksht_oid,''APP_STATUS_CODE'',5 AS APP_SEQ,1 AS APP_OID, ' ||
'fnc_src_abrvtd_name,doc_id,deal_num,cntrt_date, ROUND(sysdate-last_actvty_date,3) Date_Diff, ' ||
'case when dn.CNTRT_WKSHT_OID is null ' ||
'then ''N'' '||
'else ''Y'' '||
'end Flag '||
'FROM contract_package cp, '||
'credit_contract cc, '||
'deal_type dt, '||
'individual_applicant ia, '||
'ec_code_value ec, '||
'dlr_fs_asc_view dv, '||
'dealer_notes dn ' ||
'WHERE cc.CNTRT_WKSHT_OID = cp.CNTRT_WKSHT_OID '||
'AND dt.DEAL_TYPE_OID = cp.DEAL_TYPE_OID '||
'AND cc.CNTRT_OID = ia.CNTRT_OID '||
'AND ec.ec_code_val = cp.cntrt_status_code '||
'AND dt.RTE_ONE_FNC_SRC_ID = dv.RTE_ONE_FNC_SRC_ID '||
'AND cp.RTE_ONE_DLR_ID = dv.RTE_ONE_DLR_ID '||
'AND cp.CNTRT_WKSHT_OID = dn.CNTRT_WKSHT_OID(+) '||
'AND ROWNUM < 102';


CURSOR l_template
IS
SELECT fam_name,given_name,aplcnt_oid,cntrt_status_code,collation_seq_num,
cp.cntrt_wksht_oid,fnc_src_abrvtd_name,doc_id,deal_num,cntrt_date
FROM contract_package cp,ec_code_value ecv,individual_applicant ia,
credit_contract cc,
dlr_fs_asc_view dv,
deal_type dt
WHERE ecv.ec_code_val = cp.cntrt_status_code
AND cc.CNTRT_OID = ia.CNTRT_OID
AND cc.CNTRT_WKSHT_OID = cp.CNTRT_WKSHT_OID
AND dt.DEAL_TYPE_OID = cp.DEAL_TYPE_OID
AND dt.RTE_ONE_FNC_SRC_ID = dv.RTE_ONE_FNC_SRC_ID
AND ROWNUM < 102;


i_start_date TIMESTAMP;
p_end_date TIMESTAMP;
pc_proc_name CONSTANT VARCHAR2 (30) := 'find_contracts_ssp';
v_select_ref t_ref_cur;


--Array Declarations.
ta_fam_name fam_name_arrayType;
ta_given_name last_name_arrayType;
ta_cnt_stat_code cnt_stat_code_arrayType;
ta_coll_seq_num coll_seq_num_arrayType;
ta_wrk_oid cntrt_wrk_oid_arrayType;
ta_app_stat_code app_stat_code_arrayType;
ta_app_seq_num app_seq_num_arrayType;
ta_app_oid app_oid_arrayType;
ta_fs_abrvt_name fs_abrvtd_name_arrayType;
ta_doc_id doc_id_arrayType;
ta_deal_num deal_num_arrayType;
ta_cntr_date cntr_date_arrayType;
ta_date_diff date_diff_type;
ta_flag_type flag_type;

--Variable Declaration Section.
v_replaced_str VARCHAR2(4000);

BEGIN

-- Initialize Output Parms
pkg_ec_error.init_err_flds_sp (o_error_cd,
o_error_desc,
o_error_msg,
o_error_type
);

--DealerId.
IF i_rte_one_dlr_id IS NOT NULL
THEN
DBMS_SESSION.set_context ('dctx1', 'DLRID', UPPER (i_rte_one_dlr_id));
l_query := l_query || ' and cp.rte_one_dlr_id = sys_context( ''dctx1'', ''DLRID'' ) ';
ELSE
RAISE px_rte_one_dlr_id_null;
END IF;

--Default Screen
IF (i_aplct_lname IS NULL AND i_aplct_fname IS NULL AND i_aplct_ssn IS NULL AND i_dealid IS NULL AND
i_cntrtid IS NULL AND i_st_date IS NULL AND i_end_date IS NULL AND i_fin_src IS NULL AND
i_cntrt_stat IS NULL AND i_last_mod_by IS NULL )
THEN

i_start_date :=
pkg_ec_utility.get_utc_date (TO_CHAR (TRUNC (SYSDATE) - 5,
'MM-DD-YYYY HH24:MI:SS'
),
'America/New_York'
);

p ('START DATE: ' || TO_CHAR (i_start_date, 'MM-DD-YYYY HH24:MI:SS'));
DBMS_SESSION.set_context ('dctx1',
'STARTDATE',
TO_CHAR (i_start_date, 'yyyymmddhh24miss')
);
l_query :=
l_query
|| ' and last_actvty_date >=
to_date(
sys_context( ''dctx1'',
''STARTDATE'' ),
''yyyymmddhh24miss'') ';
END IF;

--Contract Status.
IF (i_cntrt_stat IS NOT NULL AND is_asterisk_present(i_cntrt_stat) = 'Y')
THEN
SELECT REPLACE(i_cntrt_stat,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'STATUSCD', v_replaced_str || '%');
l_query := l_query || ' and cntrt_status_code LIKE sys_context( ''dctx1'', ''STATUSCD'' ) ';

ELSIF (i_cntrt_stat IS NOT NULL AND is_asterisk_present(i_cntrt_stat) = 'N')
THEN
DBMS_SESSION.set_context ('dctx1', 'STATUSCD', i_cntrt_stat);
l_query := l_query || ' and cntrt_status_code = sys_context( ''dctx1'', ''STATUSCD'' ) ';

END IF;

--Last Modified By.
IF (i_last_mod_by IS NOT NULL AND is_asterisk_present(i_last_mod_by) ='Y' )
THEN
SELECT REPLACE(i_last_mod_by,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'lastmodby', v_replaced_str || '%');
l_query := l_query || ' and last_mod_user_id LIKE sys_context( ''dctx1'', ''lastmodby'' ) ';

ELSIF (i_last_mod_by IS NOT NULL AND is_asterisk_present(i_last_mod_by) ='N' )
THEN
DBMS_SESSION.set_context ('dctx1', 'lastmodby', i_last_mod_by);
l_query := l_query || ' and last_mod_user_id = sys_context( ''dctx1'', ''lastmodby'' ) ';

END IF;

--Last Name.
IF (i_aplct_lname IS NOT NULL AND is_asterisk_present(i_aplct_lname) = 'Y' )
THEN
SELECT REPLACE(i_aplct_lname,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'lname', v_replaced_str || '%');
l_query := l_query || ' and fam_name like sys_context( ''dctx1'', ''lname'' ) ';

ELSIF (i_aplct_lname IS NOT NULL AND is_asterisk_present(i_aplct_lname) = 'N' )
THEN
DBMS_SESSION.set_context ('dctx1', 'lname', i_aplct_lname);
l_query := l_query || ' and fam_name = sys_context( ''dctx1'', ''lname'' ) ';

END IF;

--Finance Source.
IF (i_fin_src IS NOT NULL AND is_asterisk_present(i_fin_src) = 'Y')
THEN
SELECT REPLACE(i_fin_src,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'fin_src', v_replaced_str || '%');
l_query := l_query || ' and fnc_src_abrvtd_name LIKE sys_context( ''dctx1'', ''fin_src'' ) ';

ELSIF (i_fin_src IS NOT NULL AND is_asterisk_present(i_fin_src) = 'N')
THEN
DBMS_SESSION.set_context ('dctx1', 'fin_src', i_fin_src);
l_query := l_query || ' and fnc_src_abrvtd_name = sys_context( ''dctx1'', ''fin_src'' ) ';

END IF;

--ContractId.
IF (i_cntrtid IS NOT NULL AND is_asterisk_present(i_cntrtid) ='Y')
THEN
SELECT REPLACE(i_cntrtid,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'doc_id', v_replaced_str || '%');
l_query := l_query || ' and doc_id LIKE sys_context( ''dctx1'', ''doc_id'' ) ';

ELSIF (i_cntrtid IS NOT NULL AND is_asterisk_present(i_cntrtid) ='N')
THEN
DBMS_SESSION.set_context ('dctx1', 'doc_id', i_cntrtid);
l_query := l_query || ' and doc_id = sys_context( ''dctx1'', ''doc_id'' ) ';

END IF;

--First Name.
IF (i_aplct_fname IS NOT NULL AND is_asterisk_present(i_aplct_fname) ='Y' )
THEN
SELECT REPLACE(i_aplct_fname,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'fname', v_replaced_str || '%');
l_query := l_query || ' and given_name LIKE sys_context( ''dctx1'', ''fname'' ) ';

ELSIF (i_aplct_fname IS NOT NULL AND is_asterisk_present(i_aplct_fname) ='N')
THEN

DBMS_SESSION.set_context ('dctx1', 'fname', i_aplct_fname);
l_query := l_query || ' and given_name = sys_context( ''dctx1'', ''fname'' ) ';

END IF;

--SSN.
IF (i_aplct_ssn IS NOT NULL AND is_asterisk_present(i_aplct_ssn) = 'Y')
THEN
SELECT REPLACE(i_aplct_ssn,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'ssn', v_replaced_str || '%');
l_query := l_query || ' and ntl_id LIKE sys_context( ''dctx1'', ''ssn'' ) ';

ELSIF (i_aplct_ssn IS NOT NULL AND is_asterisk_present(i_aplct_ssn) = 'N')
THEN
DBMS_SESSION.set_context ('dctx1', 'ssn', i_aplct_ssn);
l_query := l_query || ' and ntl_id = sys_context( ''dctx1'', ''ssn'' ) ';

END IF;

--Deal Id.
IF (i_dealid IS NOT NULL AND is_asterisk_present(i_dealid) = 'Y')
THEN
SELECT REPLACE(i_dealid,'*','') INTO v_replaced_str FROM dual;

DBMS_SESSION.set_context ('dctx1', 'dealid', v_replaced_str || '%');
l_query := l_query || ' and deal_num LIKE sys_context( ''dctx1'', ''dealid'' ) ';

ELSIF (i_dealid IS NOT NULL AND is_asterisk_present(i_dealid) = 'N')
THEN
DBMS_SESSION.set_context ('dctx1', 'dealid', i_dealid);
l_query := l_query || ' and deal_num = sys_context( ''dctx1'', ''dealid'' ) ';

END IF;

i_start_date := NULL;
i_start_date :=
pkg_ec_utility.get_utc_date (TO_CHAR (TRUNC (i_st_date),
'MM-DD-YYYY HH24:MI:SS'
),
'America/New_York'
);
--Start Date.
IF (i_st_date IS NOT NULL)
THEN
i_start_date :=
pkg_ec_utility.get_utc_date (TO_CHAR (TRUNC (i_st_date),
'MM-DD-YYYY HH24:MI:SS'
),
'America/New_York'
);

p ('START DATE: ' || TO_CHAR (i_start_date, 'MM-DD-YYYY HH24:MI:SS'));
DBMS_SESSION.set_context ('dctx1',
'STARTDATE',
TO_CHAR (i_start_date, 'yyyymmddhh24miss')
);
l_query :=
l_query
|| ' and last_actvty_date >=
to_date(
sys_context( ''dctx1'',
''STARTDATE'' ),
''yyyymmddhh24miss'') ';
END IF;

--End Date.
IF (i_end_date IS NOT NULL)
THEN
p_end_date :=
pkg_ec_utility.get_utc_date (TO_CHAR (TRUNC (i_end_date) + 1,
'MM-DD-YYYY HH24:MI:SS'
),
'America/New_York'
);

p ('END DATE: ' || TO_CHAR (p_end_date, 'MM-DD-YYYY HH24:MI:SS'));
DBMS_SESSION.set_context ('dctx1',
'ENDDATE',
TO_CHAR (p_end_date, 'yyyymmddhh24miss')
);
l_query :=
l_query
|| ' and last_actvty_date <
to_date(
sys_context( ''dctx1'',
''ENDDATE'' ),
''yyyymmddhh24miss'') ';
END IF;

OPEN v_select_ref FOR l_query;

FETCH v_select_ref BULK COLLECT
INTO ta_fam_name,
ta_given_name,
ta_cnt_stat_code,
ta_coll_seq_num,
ta_wrk_oid,
ta_app_stat_code,
ta_app_seq_num,
ta_app_oid,
ta_fs_abrvt_name,
ta_doc_id,
ta_deal_num,
ta_cntr_date,
ta_date_diff,
ta_flag_type;

o_rowcount := v_select_ref%ROWCOUNT;

IF o_rowcount = 0 THEN
RAISE px_no_data_found;
END IF;

--close the main reference cursor
IF v_select_ref%ISOPEN THEN
CLOSE v_select_ref;
END IF;

--initialize the object arrays
o_recordset := cntrtmgr_tab(cntrtmgr_rec( NULL,
NULL,
NULL,
0,
0,
NULL,
0,
0,
NULL,
NULL,
NULL,
NULL,
0.0,
NULL
)
);


FOR i IN 1.. o_rowcount
LOOP

o_recordset.EXTEND;
o_recordset(i) := cntrtmgr_rec (
ta_fam_name(i),
ta_given_name(i),
ta_cnt_stat_code(i),
ta_coll_seq_num(i),
ta_wrk_oid(i),
ta_app_stat_code(i),
ta_app_seq_num(i),
ta_app_oid(i),
ta_fs_abrvt_name(i),
ta_doc_id(i),
ta_deal_num(i),
ta_cntr_date(i),
ta_date_diff(i),
ta_flag_type(i)
);

END LOOP;

EXCEPTION

WHEN px_no_data_found
THEN
pkg_ec_error.make_error_msg_sp
(SQLCODE,
'No data found for the given input(s).',
pc_package_name,
pc_proc_name,
pkg_ec_error.parm_array (i_rte_one_dlr_id,
NULL,
NULL,
NULL
),
o_error_cd,
o_error_desc,
o_error_msg,
o_error_type
);

WHEN px_rte_one_dlr_id_null
THEN
pkg_ec_error.make_error_msg_sp
(SQLCODE,
NULL,
pc_package_name,
pc_proc_name,
pkg_ec_error.parm_array (i_rte_one_dlr_id,
NULL,
NULL,
NULL
),
o_error_cd,
o_error_desc,
o_error_msg,
o_error_type
);

WHEN OTHERS
THEN
pkg_ec_error.make_error_msg_sp
(SQLCODE,
SQLERRM,
pc_package_name,
pc_proc_name,
pkg_ec_error.parm_array (i_rte_one_dlr_id,
NULL,
NULL,
NULL
),
o_error_cd,
o_error_desc,
o_error_msg,
o_error_type
);
END my_proc;

When I checked the output of Dynamic query, it is returning 16 rows. But after collecting same into a collection(varray) I am getting 22 rows (duplicates). Could you please tell me what is wrong in my code?

Hi Tom,
Sorry for not keeping it simple. Here is my problem:

Procedure my_proc
TYPE t_ref_cur IS REF CURSOR;
v_select_ref t_ref_cur; --ref cursor
l_query varchar2(4000);

l_query := 'select c1,c2,... from t1 a,t2 b
where a.id=b.id';

DBMS_SESSION.set_context ('c1', 'STATUS', i_status);

l_query := l_query || ' and status_code = sys_context
( ''c1'', ''STATUS'' ) ';

open v_select_ref for l_query;

FETCH v_select_ref BULK COLLECT
INTO tab1,
tab2;

count:=v_select_ref%rowcount;

End my_proc;

When I captured l_query and executed, it gave me 16 records(which is correct). But when I stored the same thing in a
ref-cursor and checked for rowcount(using %rowcount), it is
showing 22 records. What could be the problem?

ops$tkyte@ORA9IR2> declare
  2          type array is table of varchar2(50);
  3          l_cursor sys_refcursor;
  4          tab1 array;
  5          tab2 array;
  6  begin
  7          open l_cursor for 'select ename, job from scott.emp';
  8          fetch l_cursor bulk collect into tab1, tab2;
  9
 10          dbms_output.put_line( l_cursor%rowcount );
 11          dbms_output.put_line( tab1.count );
 12  end;
 13  /
14
14
 
PL/SQL procedure successfully completed.


need full test case I can run, it isn't working that way for me. 

Once again sorry! Here are the complete details:
I have the following External Type declarations:

CREATE OR REPLACE TYPE ctrt_rec AS OBJECT
(
c1 VARCHAR2(35),
c2 NUMBER,
c3 VARCHAR2(50),
);

CREATE OR REPLACE TYPE ctrt_tab AS VARRAY (150) OF ctrt_rec;

---------------------------------------------

procedure my_proc(myrecord out ctrt_tab)
is

TYPE c1_arrayType IS TABLE OF VARCHAR2(35);
TYPE c2_arrayType IS TABLE OF NUMBER(15);
TYPE c3_arrayType IS TABLE OF VARCHAR2(50);


c1_array c1_arrayType;
c2_array c2_arrayType;
c3_array c3_arrayType;


TYPE t_ref_cur IS REF CURSOR;

v_select_ref t_ref_cur;
l_query varchar2(4000);

begin
l_query := 'select c1,c2,... from t1 a,t2 b where
a.id=b.id';

DBMS_SESSION.set_context ('c1', 'STATUS', i_status);
l_query := l_query || ' and status_code = sys_context( ''c1'', ''STATUS'' ) ';

open v_select_ref for l_query;

FETCH v_select_ref BULK COLLECT
INTO c1_array,
c2_array,
c3_array;


count:=v_select_ref%rowcount;

myrecord:= ctrt_tab (ctrt_rec(Null,0,null));

for i in .. count
loop
myrecord.extend;
myrecord(i) := ctrt_rec(
c1_array(i),
c2_array(i),
c3_array(i)
);
end loop;


my for-loop is iterating 22 times, instead of 16 times and
appending some duplicates.

Tom,

I have a doubt about refcursors. I need calculate the interval in which there is more work. For example: If I indicate from 8 am to 14 pm, the application should show to me the range (only one hour inside of the interval. This is, if returns 9, it would be from 9 to 10h). I have a refcursor which returns the application name and the different hours inside the range with their costs.

Refcursor rc:

APP_NAME "8/2/2006 8H-9H" "8/2/2006 9H-10H" TOTAL
=========================================================

APP1 12 10 22
APP2 4 7 11
APP3 8 22 30
...

In this case if I ask for the max cost from 8h to 10h, the result must be app3 and cost=30.

I don´t know how "ask to the refcursor" for the maximum value in a range and the application name. As the refcursor has a different number of columns each time depending on the range, I can´t create a register with a defined structure to load the refcursor.

Could you please help me?

Thank you.

Hi Tom,

Yes, you are right. Refcursor is the format that I must use to pass the result to my customer. The true question is "which hour produces the most amount of work inside of an interval".

Is there another better way to do this without refcursors?.

Thanks again.

Hi Tom,
I have a procedure which has a string as  an IN parameter and
a REF cursor as an OUT parameter.

I just open the Ref cursor for a query with a WHERE clause
equal to IN parameter.

In an anonymous block I want to call this procedure and print
the REF cursor column values using dbms_output.put_line.
But when I do this,it throws an error as
"Invalid reference to that variable".

SQL> declare
  2  my_rec sys_refcursor;
  3  my_rec2 my_rec%type;
  4  begin
  5  lkp('ZA',my_rec);
  6  my_rec2 := my_rec;
  7  dbms_output.put_line(my_rec2.ccode ||' '||my_rec2.pmr_period_date||' '||my_rec2.pmr_cycle);
  8  end;
  9  /
dbms_output.put_line(my_rec2.ccode ||' '||my_rec2.pmr_period_date||' '||my_rec2.pmr_cycle);
                             *
ERROR at line 7:
ORA-06550: line 7, column 30:
PLS-00487: Invalid reference to variable 'MY_REC2'
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored


How to do a work around for this??
Can a cursor be assigned a REF CURSOR??
Please do reply.
Bye 

I'm looking for a way to let a java method take a cursor from within a PL/SQL function.

Can one achieve this at all (haven't found an example so far)?

Yes, I've already that. But I'd like to pass a cursor as parameter from PL/SQL to JAVA:

select MyFct( cursor( <MyQuery> ) ) from dual ;

public static <SomeOracleType> myMethod( <???> )

Is there any appropriate Oracle type avaiable for "<???>" at all?

Hi Reader,
Try to bind ??? to OracleTypes.CURSOR
Extract the Result as ResultSet.

Example:

CallableStatement cstmt =
con.prepareCall("{? = call PACKAGE.FUNCTION }");

//Set the Out Parameter type to be of type CURSOR
cstmt.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR );
cstmt.execute();

//Cast the returned parameter,
//OracleTypes.CURSOR to a JDBC ResultSet ResultSet rs =
(ResultSet)cstmt.getObject(1);
while(rs.next()) {
System.out.println(rs.getString(1));
}