Strange error on Ref Cursor

From: Murugesan Balakrishnan <mail_oracle_at_yahoo.com>
Date: Tue, 12 Sep 2000 19:18:13 -0700 (PDT)
Message-Id: <10617.116788@fatcity.com>

Hello Lists,

I have a procedure which returns the result of a query through ref cursor variable. When I execute the query in sql*plus it returns only 149 records. But when I dbms_output.put_line the result from ref cursor it displays more than 100,000 records. Could not count exact number of records as the system hangs. Following are the details:

Procedure:

PROCEDURE RTRV_CIC_CODES (NPA IN
sys1_cos_sa.NPA%TYPE,

                            NXX IN
sys1_cos_sa.NXX%TYPE,
                            LINE_NUM IN
sys1_cos_sa.LOW_NUM_RANGE%TYPE,
                            ResultSet OUT RefCurType)

IS
l_resultset refcurtype;
BEGIN
OPEN l_resultset FOR

      SELECT A.CIC_NAME,
	         A.ACNA,
	         A.CIC_CODE,
			 A.INTRALATA_FLAG,
			 A.INTERLATA_FLAG,
			 A.INTERNATIONAL_FLAG
      FROM SYS1_CIC A, SYS1_COS_SA B
      WHERE NPA = B.NPA AND
            NXX = B.NXX AND
            LINE_NUM BETWEEN B.LOW_NUM_RANGE AND
B.HIGH_NUM_RANGE AND
            A.CLLI_CODE = B.CLLI_CODE AND
            A.RSA_ID = B.RSA_ID;

resultset := l_resultset;

END; Table: sys1_cic

COUNT(*)

5116

SQL> desc sys1_cic

Name Null? Type

------------------------------------ RSA_ID NOT NULL VARCHAR2(4) CLLI_CODE NOT NULL CHAR(11) CIC_NAME VARCHAR2(50) CIC_CODE NOT NULL VARCHAR2(4) INTERLATA_FLAG CHAR(1) INTRALATA_FLAG CHAR(1) INTERNATIONAL_FLAG CHAR(1) ACNA VARCHAR2(3)

Table: Sys1_cos_sa

SQL> desc sys1_cos_sa
COUNT(*)

413685
Name Null? Type

------------------------ EXCHANGE VARCHAR2(20) MARKET_AREA_ID NOT NULL NUMBER(38) RATE_GROUP VARCHAR2(3) NPA NOT NULL CHAR(3) NXX NOT NULL CHAR(3) LOW_NUM_RANGE NOT NULL CHAR(4) COS_BEG_EFF_DATE NOT NULL DATE HIGH_NUM_RANGE NOT NULL CHAR(4) CLLI_CODE NOT NULL VARCHAR2(11) COS_END_EFF_DATE DATE SDM_DESC VARCHAR2(11) SDM VARCHAR2(2) COS_TYPE_CODE VARCHAR2(4) COS_SUPPORT_IND CHAR(1) SWITCH_TYPE VARCHAR2(8) STATE CHAR(2) OCN VARCHAR2(4) RSA_ID VARCHAR2(4) COS_RATE_CODE VARCHAR2(8) BAN VARCHAR2(13) ZONE VARCHAR2(2) LATA VARCHAR2(3)

Wrapping procedure to printout the results:

PROCEDURE proc_test IS

l_cic_name VARCHAR2(50);
l_acna     VARCHAR2(3);
l_cic_code VARCHAR2(4);
l_intra_lata_ind VARCHAR2(1);
l_inter_lata_ind VARCHAR2(1);
l_internal_ind     VARCHAR2(1);
l_count            NUMBER;

TYPE cic_record IS RECORD ( cic_name VARCHAR2(50),
	 			   		    acna     VARCHAR2(3),
							cic_code VARCHAR2(4),
							intra_lata_ind VARCHAR2(1),
							inter_lata_ind VARCHAR2(1),
							internal_ind   VARCHAR2(1)
						  );

TYPE ref_handle is REF CURSOR;
ref_cursor ref_handle;

CURSOR test_cur IS
SELECT A.CIC_NAME,

	         A.ACNA,
	         A.CIC_CODE,
			 A.INTRALATA_FLAG,
			 A.INTERLATA_FLAG,
			 A.INTERNATIONAL_FLAG
      FROM SYS1_CIC A, SYS1_COS_SA B
      WHERE 915 = B.NPA AND
            041 = B.NXX AND
            9998 BETWEEN B.LOW_NUM_RANGE AND
B.HIGH_NUM_RANGE AND
            A.CLLI_CODE = B.CLLI_CODE AND
            A.RSA_ID = B.RSA_ID;

BEGIN l_count := 0;
messages1.RTRV_CIC_CODES(915, 041, 9998, ref_cursor);

LOOP
FETCH ref_cursor INTO
l_cic_name,l_acna,l_cic_code,l_intra_lata_ind,l_inter_lata_ind,l_internal_ind; EXIT WHEN ref_cursor%NOTFOUND;
dbms_output.put_line(l_cic_name||' '||l_acna||' '||l_cic_code||' '||
l_intra_lata_ind||' '||l_inter_lata_ind||' '||l_internal_ind);
l_count := l_count + 1;
END LOOP;
dbms_output.put_line(' ');
dbms_output.put_line('Total number of records displayed are '||to_char(l_count));

/*OPEN test_cur;
LOOP
FETCH test_cur INTO
l_cic_name,l_acna,l_cic_code,l_intra_lata_ind,l_inter_lata_ind,l_internal_ind; EXIT WHEN test_cur%NOTFOUND;
dbms_output.put_line(l_cic_name||' '||l_acna||' '||l_cic_code||' '||
l_intra_lata_ind||' '||l_inter_lata_ind||' '||l_internal_ind);
l_count := l_count + 1;
END LOOP;
close test_cur;
dbms_output.put_line(' ');
dbms_output.put_line('Total number of records displayed are '||to_char(l_count));
*/

END; Received on Tue Sep 12 2000 - 21:18:13 CDT