Message-Id: <10617.116788@fatcity.com> From: Murugesan Balakrishnan Date: Tue, 12 Sep 2000 19:18:13 -0700 (PDT) Subject: Strange error on Ref Cursor 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;