Home » RDBMS Server » Server Administration » REF CURSOR Related
REF CURSOR Related [message #370702] Thu, 20 January 2000 19:24
Suresh Vattenad
Messages: 2
Registered: January 2000
Junior Member
I am having a problem with REF CURSORS. For reference,
I have included a portion of the procedure that is defined in package called crm_sosi_package. The code is listed below:


PROCEDURE proc_sosi (p_lsr_id IN OUT VARCHAR2,
p_cur_req_status_seq OUT cur_req_status_seq,
p_cur_req_cond_seq OUT cur_req_cond_seq,
p_lsrdd_date OUT DATE,
p_lsrod_date OUT DATE,
p_lsrcomp_date OUT DATE,
p_cur_so_status_seq OUT cur_so_status_seq,
p_cur_so_cond_seq OUT cur_so_cond_seq,
p_sqlerrm OUT VARCHAR2,
p_sqlcode OUT VARCHAR2,
p_message OUT VARCHAR2) AS
/*
Variable Declarations
*/
t_request_pk_id request_versions.request_pk_id%TYPE; --latest REQUEST_PK_ID
t_request_id requests.request_id%TYPE; --latest REQUEST_ID */
t_version_pk_id request_versions.version_pk_id%TYPE; --latest VERSION PK ID
t_temp_req_id requests.request_id%TYPE; --temp variable for request_id
t_temp_req_pk_id requests.request_pk_id%TYPE; --temp variable for request_pk_id
t_temp_req_status_id requests.req_status_id%TYPE; --temp variable for req_status_id

BEGIN
/*
Selecting The Most Latest LSR
*/

SELECT request_id, request_pk_id, req_status_id
INTO t_temp_req_id, t_temp_req_pk_id, t_temp_req_status_id
FROM requests
WHERE request_pk_id =
(SELECT MIN(request_pk_id)
FROM request_versions
WHERE request_version_id = p_lsr_id
AND owner_id = 1
AND err_flag = 'N'
AND del_ind = 'N')
AND req_status_id 4;

...........................................
...........................................
...........................................
EXCEPTION
WHEN OTHERS THEN
p_sqlerrm := SUBSTR(SQLERRM, 1, 100);
p_sqlcode := SQLCODE;
END proc_sosi;



For testing puposes, I am calling this procedure from within an unnamed block form SQL*Plus.

When I call this procedure with a value for p_lsr_id that exists in the database, I get the required results. But when I call it with a value that doesnt exist, I get a
"end-of-file on communication channel" error and I have to logon again. This went on for some time and finally it brought down the database.

The unnamed block is as follows:

DECLARE
t_lsr_id requests.request_id%TYPE;
t_lsrdd_date DATE;
t_lsrod_date DATE;
t_lsrcomp_date DATE;
t_sqlerrm VARCHAR2(100);
t_sqlcode VARCHAR2(100);
t_message VARCHAR2(100);
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
BEGIN
t_lsr_id := '478768';
time_before := dbms_utility.get_time;
crm_sosi_package.proc_sosi(t_lsr_id,
:b_cur_req_status_seq, :b_cur_req_cond_seq,
t_lsrdd_date, t_lsrod_date, t_lsrcomp_date,
:b_cur_so_status_seq, :b_cur_so_cond_seq,
t_sqlerrm, t_sqlcode, t_message);
time_after := dbms_utility.get_time;
dbms_output.put_line('time :' || to_char(time_after - time_before));
dbms_output.put_line('*********************');
dbms_output.put_line('t_lsr_id ' || t_lsr_id);
------
dbms_output.put_line('*********************');
----
dbms_output.put_line('t_lsrdd_date ' || to_char(t_lsrdd_date, 'dd-mon-yy'));
dbms_output.put_line('t_lsrod_date ' || to_char(t_lsrod_date, 'dd-mon-yy'));
dbms_output.put_line('t_lsrcomp_date ' || to_char(t_lsrcomp_date, 'dd-mon-yy'));
----
dbms_output.put_line('*********************');
dbms_output.put_line('t_sqlerrm $' || t_sqlerrm || '$');
dbms_output.put_line('t_sqlcode $' || t_sqlcode || '$');
dbms_output.put_line('t_message $' || t_message || '$');
END;

Before running this block, I am declaring the following variables in the SQL*Plus environment:

VARIABLE b_cur_req_status_seq REFCURSOR
VARIABLE b_cur_req_cond_seq REFCURSOR
VARIABLE b_cur_so_status_seq REFCURSOR
VARIABLE b_cur_so_cond_seq REFCURSOR

I am expecting the Oracle Error - NO_DATA_FOUND when I pass a value that doesn't exist in the database.

Could someone throw some light on this..

TIA,
Suresh Vattenad
303-965-5959
Previous Topic: REF CURSOR related
Next Topic: Viewing Bind Variable Values on the Database
Goto Forum:
  


Current Time: Thu Oct 22 04:53:25 CDT 2020