Help fixing Oracle Errors

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.

Thanks for the inputs. Below is what I got

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     5250

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

Thanks

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.

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;

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

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?

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

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;

Thanks worked great!!!