Solved

Help fixing Oracle Errors

Posted on 2006-11-07
12
621 Views
Last Modified: 2008-01-09
Hello All,

We have a common stored procedure we use for two different vendors. The procedure is working fine for one vendor and giving an error for another vendor. I want to know what could go wrong with another vendor where we use the same code for both. The record counts 2000 vs 6000 between the vendors.

I did research a little on the error code but want to know why it is working for one and not for the other.

Error:
--------
DECLARE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-06508: PL/SQL: could not find program unit being called:
"PUBLIC.DBMS_OUTPUT"
ORA-06512: at "ODSN.DRUGD_PROC", line 1767
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 7

Thanks in advance for all the inputs.
0
Comment
Question by:cutie_smily
  • 7
  • 3
  • 2
12 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
On both systems log in to the DB as the SYSTEM user and issue the following:
show parameter open_cursors

Make sure the numbers match.

My guess is that the code isn't properly closing cursors after it uses them and the 'working' vendor just hasn't hit the breaking limit yet.

0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks for the inputs. Below is what I got

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     5250
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 100 total points
Comment Utility
Is it possible that its opening a new cursor for every record ???
In that case, vendor1 with 2000 records is well below 5250 whereas vendor2 with 6000 records fails, which kind of explains the error.
0
 

Author Comment

by:cutie_smily
Comment Utility
It is a pretty long code to post here. Is there any other way to post the whole code ? Or I can email.

Thanks
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
Try to analyse the code and check if all instances of open cursor <cur_name> are complemented with a corresponding close cursor. Still if ur not able to figure out the error, u can email the code to jinesh_kamdar @ ml.com.
0
 

Author Comment

by:cutie_smily
Comment Utility
Below is the complete code. I think there is a problem with reference cursor but not sure though. In the package it is defined as

TYPE trill_cur IS RECORD
( DATE_FILLED DATE,
 ROW_SEQ_NUM CUST_CUSTNBR_MATCH_RTN .ROW_SEQ_NUM%TYPE,
 CUSTNBR_MATCH_RTN_NBR  CUST_CUSTNBR_MATCH_RTN .CUSTNBR_MATCH_RTN_NBR%TYPE);


/*********************************************************************************/

PROCEDURE process_CustNbr_output (IN_CUST_CD IN VARCHAR2, ErrorString OUT VARCHAR2) IS

TYPE CUSTNBR_CURSOR is REF CURSOR;
ld_cursor     CUSTNBR_CURSOR;
query_statement varchar2(400);
v_stg_table_name  varchar2(100);
input_rec trill_cur;

/* Declare variables */
v_custnr             tt_string1;
v_src              tt_string1;
v_custnr_out         VARCHAR2(20);
v_src_out          VARCHAR2(20);
v_REJ_TBL_NM       VARCHAR2(100);
v_stg_dt_fill      MD_CUST_DYN_SQL_DEFN_NEW.SRC_COL_NM%TYPE;
----v_cust_cnt          BINARY_INTEGER;
v_reject_cnt_stmt  VARCHAR2(2000);
v_dt_filled        DATE;
v_get_custnr_span    BOOLEAN;
v_CustNbr_flag    CUST_CUSTNBR_MATCH_RTN.CUSTNBR_MATCH_RTN_NBR%TYPE;
v_row_seq_nbr      CUST_CUSTNBR_MATCH_RTN.ROW_SEQ_NUM%TYPE;
i                  BINARY_INTEGER;
v_array_size       BINARY_INTEGER;
v_error_string     VARCHAR2(2000);
v_found_flag       BOOLEAN;
get_cust_failed     EXCEPTION;
cleanup_failed     EXCEPTION;
CUST_rpt_file           UTL_FILE.FILE_TYPE;
cust_match_cnt           BINARY_INTEGER;
reject_cnt         BINARY_INTEGER;
v_rec_test         NUMBER(10);


/*Create cursor to get the custber number from the CustNbr table */

CURSOR  v_get_cust_num IS
SELECT  CUST_NBR, SRC_REC_CD
  FROM  CUST_CUSTNBR_MATCH_RTN
 WHERE  CUSTNBR_MATCH_RTN_NBR= v_CustNbr_flag AND
 TRIM(CUST_CD) = 'ODS';

BEGIN
CUST_rpt_file := UTL_FILE.FOPEN ('PBM_TMP', 'CUST_rpt', 'A');
cust_match_cnt:=0;

SELECT DISTINCT UPPER(SRC_TBL_NM),UPPER(SRC_COL_NM)
INTO v_stg_table_name,v_stg_dt_fill
FROM MD_CUST_DYN_SQL_DEFN_NEW
WHERE CUST_CD = IN_CUST_CD
AND CUSTNBR_CURSR_APPLY_IND = 'Y';

query_statement:= 'SELECT C.'||v_stg_dt_fill||',
                           X.ROW_SEQ_NUM,
                           X.CUSTNBR_MATCH_RTN_NBR
                    FROM '||v_stg_table_name||' C, CUST_CUSTNBR_MATCH_RTN X
                    WHERE C.ROW_SEQ_NUM=X.ROW_SEQ_NUM
                    AND   C.SOURCEID = X.SRC_REC_CD
                    ORDER BY  X.CUSTNBR_MATCH_RTN_NBR';

OPEN ld_cursor FOR query_statement;
LOOP
-----v_cust_cnt := 0;
      FOR i in 1..20
      LOOP
            v_custnr(i):= NULL ;
            v_src(i):= NULL;
      END LOOP;

FETCH ld_cursor into input_rec;
EXIT WHEN ld_cursor%NOTFOUND;

     v_dt_filled :=input_rec.date_filled;
     v_row_seq_nbr :=input_rec.row_seq_num;
     v_CustNbr_flag :=input_rec.CustNbr_match_rtn_nbr;
       i:=1;
      v_found_flag:=FALSE;

OPEN v_get_cust_num;
      LOOP
            FETCH v_get_cust_num INTO v_custnr(i), v_src(i);
    EXIT WHEN v_get_cust_num%NOTFOUND;
            v_found_flag:=TRUE;
----    v_cust_cnt := v_cust_cnt+1;
         i:=i+1;
      END LOOP;
CLOSE v_get_cust_num;

IF (v_found_flag) THEN

/*****************************************************************************/
/* Get the exact customer number by passing the values to the function */

v_get_custnr_span:= get_cust_nbr(v_custnr,v_dt_filled,v_custnr_out,v_src_out,v_error_string);

        IF v_error_string IS NOT NULL THEN
                    RAISE get_cust_failed;
            END IF;

            IF v_get_custnr_span THEN
      cust_match_cnt:=cust_match_cnt+1;
                  UPDATE CUST_CUSTNBR_MATCH_RTN
                  SET CUST_NBR= v_custnr_out, SRC_REC_CD= trim(v_src_out)
                  WHERE trim(SRC_REC_CD)= trim(IN_CUST_CD) AND
                  CUSTNBR_MATCH_RTN_NBR=v_CustNbr_flag AND
              ROW_SEQ_NUM = v_row_seq_nbr;

            END IF;
      END IF;
END LOOP;
Close ld_cursor;

IF v_error_string IS NULL THEN

clean_up_CustNbr_output (IN_CUST_CD, ErrorString);
            IF ErrorString IS NOT NULL THEN
                  RAISE cleanup_failed;
            ELSE
                  COMMIT;
            END IF;
END IF;

/* Get counts for rejects and deletes  */

SELECT DISTINCT UPPER(REJ_TBL_NM)
INTO v_REJ_TBL_NM
FROM MD_CUST_DYN_SQL_DEFN_NEW
WHERE CUST_CD = IN_CUST_CD;

v_reject_cnt_stmt := 'SELECT COUNT(*) FROM '|| v_REJ_TBL_NM;
EXECUTE IMMEDIATE v_reject_cnt_stmt INTO reject_cnt;

 UTL_FILE.PUT_LINE(CUST_rpt_file,'PBM_ID  ' || IN_CUST_CD );
 UTL_FILE.PUT_LINE(CUST_rpt_file,'Custber# Matched   ' || cust_match_cnt);
 UTL_FILE.PUT_LINE(CUST_rpt_file,'Rejected No Matching Custber  ' || reject_cnt );

UTL_FILE.FCLOSE (CUST_rpt_file);

EXCEPTION
    WHEN get_cust_failed THEN
    IF ld_cursor%ISOPEN THEN
                 CLOSE ld_cursor;
    END IF;
    IF v_get_cust_num%ISOPEN THEN
                 CLOSE v_get_cust_num;
    END IF;
    DBMS_OUTPUT.PUT_LINE(ErrorString);
        ROLLBACK;

    WHEN cleanup_failed THEN
    IF ld_cursor%ISOPEN THEN
                 CLOSE ld_cursor;
    END IF;
    IF v_get_cust_num%ISOPEN THEN
                 CLOSE v_get_cust_num;
    END IF;
    DBMS_OUTPUT.PUT_LINE(ErrorString);
        ROLLBACK;

    WHEN OTHERS THEN
    IF ld_cursor%ISOPEN THEN
                 CLOSE ld_cursor;
    END IF;
    IF v_get_cust_num%ISOPEN THEN
                 CLOSE v_get_cust_num;
    END IF;
    ErrorString:= 'process_CustNbr_output: Oracle Error:' || SQLCODE || ':'|| SQLERRM;
        DBMS_OUTPUT.PUT_LINE(ErrorString);
        ROLLBACK;

END process_CustNbr_output;
END CUST_Nbr;

/***********************************************************************************/

FUNCTION get_cust_nbr ( member_in  IN  tt_string1, date_filled_in in date, member_out out VARCHAR2, src_out out VARCHAR2, errorstring out VARCHAR2) RETURN BOOLEAN IS

v_custnr1 VARCHAR2(200);
v_custnr2 VARCHAR2(200);
v_custnr_new MEMBER_SPAN.MEMBER#%TYPE;
v_src_new MEMBER_SPAN.SOURCEID%TYPE;
v_match_flag BOOLEAN:=FALSE;
cur integer:=dbms_sql.open_cursor;
cur2 integer:=dbms_sql.open_cursor;
where_in VARCHAR2(2000);
feedback number(10);
r_feedback NUMBER(10);
v_query_span VARCHAR2(2500);

BEGIN
/* Loop the array to pass all the numbers to the cursor */
FOR i in 1..member_in.LAST
LOOP
      IF i=1 THEN
            v_custnr1:=''''||trim(member_in(i))||'''';
      ELSE
            v_custnr2:=trim(member_in(i));
            v_custnr1:=v_custnr1||','||''''||v_custnr2||'''';
      END IF;
END LOOP;

where_in:='IN ('|| v_custnr1||') AND  '||''''||DATE_FILLED_IN||'''' ||' BETWEEN YMDEFF AND YMDEND AND VOID <> ''V'' )';
v_query_span:='SELECT MEMBER#, SOURCEID
             FROM MEMBER_SPAN
            WHERE ROW_SEQ_NUM IN (SELECT MAX(ROW_SEQ_NUM)
                              FROM MEMBER_SPAN
                               WHERE MEMBER# ';

v_query_span:=v_query_span|| where_in;
DBMS_SQL.PARSE(cur,v_query_span,DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(cur,1,v_custnr_new,20);
DBMS_SQL.DEFINE_COLUMN(cur,2,v_src_new,20);
feedback:=DBMS_SQL.EXECUTE(cur);
r_feedback:=DBMS_SQL.FETCH_ROWS(cur);
IF r_feedback > 0 THEN
      DBMS_SQL.COLUMN_VALUE(cur,1,v_custnr_new);
      DBMS_SQL.COLUMN_VALUE(cur,2,v_src_new);
END IF;
DBMS_SQL.CLOSE_CURSOR(cur);

IF v_custnr_new IS NOT NULL THEN
      member_out:=v_custnr_new;
      src_out:=v_src_new;
      v_match_flag:=TRUE;
      --ErrorString:=SQLCODE;
      RETURN v_match_flag;
END IF;

v_query_span:='SELECT MEMBER#, SOURCEID
                       FROM MEMBER_SPAN
                       WHERE ROW_SEQ_NUM IN (SELECT MAX(ROW_SEQ_NUM)
                                                         FROM MEMBER_SPAN_FS_BT
                                                     WHERE MEMBER# ';

v_query_span:=v_query_span|| where_in;
DBMS_SQL.PARSE(cur2,v_query_span,DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(cur2,1,v_custnr_new,20);
DBMS_SQL.DEFINE_COLUMN(cur2,2,v_src_new,20);
feedback:=DBMS_SQL.EXECUTE(cur2);
r_feedback:=DBMS_SQL.FETCH_ROWS(CUR2);
IF r_feedback > 0 THEN
      DBMS_SQL.COLUMN_VALUE(cur2,1,v_custnr_new);
      DBMS_SQL.COLUMN_VALUE(cur2,2,v_src_new);
END IF;
DBMS_SQL.CLOSE_CURSOR(cur2);

IF v_custnr_new IS NOT NULL THEN
      member_out:=v_custnr_new;
      src_out:=v_src_new;
      v_match_flag:=TRUE;
      ErrorString:=SQLCODE;
      RETURN v_match_flag;
END IF;
RETURN v_match_flag;

/*Raise exception IF any */

EXCEPTION
      WHEN OTHERS THEN
      ErrorString:= 'get_cust_nbr: Oracle Error:' || SQLCODE || ':'|| SQLERRM;
      RETURN FALSE;
END get_cust_nbr;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:cutie_smily
Comment Utility
I think the code is opening a cursor for every record. Can you please find out the solution for this.

SQL> select a.value, b.name
  2              from v$mystat a, v$statname b
  3             where a.statistic# = b.statistic#
  4               and b.name = 'opened cursors current';

     VALUE NAME
---------- ----------------------------------------------------------------
      5249 opened cursors current


Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
On a quick scan of your code the only place I can see a potential problem is in the function get_cust_nbr.

In the declaration section you open 2 cursors:
cur integer:=dbms_sql.open_cursor;
cur2 integer:=dbms_sql.open_cursor;

In the code it is possible to return before closing cur2:
IF v_custnr_new IS NOT NULL THEN
...
    RETURN v_match_flag;
END IF;

Without knowing the complete workflow, how often are you likely to hit the above IF statement?
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks for your valuable comments. I think you might be right on your guess.  I will try to explain what get_cust_nbr does and please help me out on fixing this issue.

process_CustNbr_output procedure pass costumer numbers to the get_cust_nbr function to check for the spans. If we have multiple customers then we pick the max row_seq_num to eliminate the dups. There are two v_query_span sql statements (Typo in my previous post: FROM MEMBER_SPAN_C and second table FROM MEMBER_SPAN_F ).

If the customer number is not found in the table MEMBER_SPAN_C then I have to look in MEMBER_SPAN_F table and return the appropriate Customer number.

Hope this helps a little. Please let me know if you have any questions.

Thanks
0
 

Author Comment

by:cutie_smily
Comment Utility
I comment the below code and it worked fine without any errors. It updated the customer number perfectly. The change I want now is if the member numbers were found in the first v_query_span then just output those and no need to goto the second v_query_span.

Hope that make sense.

Thanks

*********************************************************************
v_query_span:='SELECT MEMBER#, SOURCEID
                     FROM MEMBER_SPAN
                     WHERE ROW_SEQ_NUM IN (SELECT MAX(ROW_SEQ_NUM)
                                                    FROM MEMBER_SPAN_FS_BT
                                                 WHERE MEMBER# ';

v_query_span:=v_query_span|| where_in;
DBMS_SQL.PARSE(cur2,v_query_span,DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(cur2,1,v_custnr_new,20);
DBMS_SQL.DEFINE_COLUMN(cur2,2,v_src_new,20);
feedback:=DBMS_SQL.EXECUTE(cur2);
r_feedback:=DBMS_SQL.FETCH_ROWS(CUR2);
IF r_feedback > 0 THEN
     DBMS_SQL.COLUMN_VALUE(cur2,1,v_custnr_new);
     DBMS_SQL.COLUMN_VALUE(cur2,2,v_src_new);
END IF;
DBMS_SQL.CLOSE_CURSOR(cur2);

IF v_custnr_new IS NOT NULL THEN
     member_out:=v_custnr_new;
     src_out:=v_src_new;
     v_match_flag:=TRUE;
     ErrorString:=SQLCODE;
     RETURN v_match_flag;
END IF;
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
Comment Utility
Sorry but I really don't have the time to spend to fully understand the business process.

If it was in fact cur2 causing the problem just change the declaration and move the open down in the code:
FUNCTION get_cust_nbr...
...
cur2 integer;
...
v_query_span:=v_query_span|| where_in;
cur2 :=dbms_sql.open_cursor;
DBMS_SQL.PARSE(cur2,v_query_span,DBMS_SQL.NATIVE);
...


0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks worked great!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now