Avatar of MIHIR KAR
MIHIR KAR
Flag for India asked on

PL/SQL Cursor fetch error!

Hi Expert,

I have a issue with a job, i'm trying to fetch and insert into common_error_log table.

FYI.. I have mentioned the code chunk.
This is failing before insert into Common_Error_Log table as the cursor has no record to fetch .
   OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE; 
   END IF;

Open in new window


But after checked the value from table it's there, could you please throw an light if entry is there than why going this failure.

Please find the following attachment select stmt for cursor.

CEL
Please let me know if there need more info.
Thanks,
Mihir
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
Qlemo

After the FETCH and CLOSE that cursor is gone, you only have a variable with a value.
We need to see your INSERT, best in a simplified test case.
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MIHIR KAR

ASKER
I think the code shouldn't be an issue as because i have used the variable not the cursor after fetch, those works well for me. in my other program unit

 OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;   -- Here the variable has value
   CLOSE C_GET_MAX_RETRY;


Please guide me with the following code .
FUNCTION LOG_ERRORS (O_error_message        IN OUT  VARCHAR2,
                      I_CEL_TBL              IN OUT  RIS_COMMON_ERR_LOG_TBL,
                      I_module               IN      VARCHAR2)
   RETURN BOOLEAN IS
   
   L_program_name      VARCHAR2(100) := 'RIS_COMMON_ERROR_LOG_SQL.LOG_ERRORS';
   LP_sysdate          DATE          := SYSDATE;
   LP_user             VARCHAR2(25)  := USER;
   L_exists            VARCHAR2(1);
   L_msg_seq_no        NUMBER(10)    := RIS_COMMON_ERR_LOG_MSG_SEQ.NEXTVAL;
   L_max_retry_count   NUMBER(10);
   
   CURSOR C_GET_MAX_RETRY IS
      SELECT max_retry_count
        FROM ris_common_error_config
       WHERE module = I_module
         AND type = 'LOG';
   
   CURSOR C_VALIDATE_INPUT IS
      SELECT 'x'
        FROM TABLE(CAST(I_CEL_TBL AS risgap.RIS_COMMON_ERR_LOG_TBL)) err_tbl
       WHERE err_tbl.entity_id         IS NULL
         OR  err_tbl.entity_name       IS NULL
         OR  err_tbl.sub_entity_name   IS NULL
         OR (err_tbl.attr_char1        IS NULL AND
             err_tbl.attr_char2        IS NULL AND
             err_tbl.attr_char3        IS NULL AND
             err_tbl.attr_clob         IS NULL AND
             err_tbl.attr_xml          IS NULL AND
             err_tbl.attr_num1         IS NULL AND
             err_tbl.attr_num2         IS NULL AND
             err_tbl.attr_num3         IS NULL
             );
BEGIN

   IF (I_CEL_TBL IS NULL) THEN
      O_error_message :=  'CEL Table is null:@'||L_program_name;
      RETURN FALSE;   
   END IF;
   
   OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;   -- Here the variable has value
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN   -- Here it shouldn't be null as there value present in table
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE; 
   END IF;

   OPEN C_VALIDATE_INPUT;
   FETCH C_VALIDATE_INPUT INTO L_exists;
   IF C_VALIDATE_INPUT%FOUND THEN
      O_error_message := 'MANDATORY_COLUMNS_NULL';
      CLOSE C_VALIDATE_INPUT;
      RETURN FALSE;
   END IF;
   CLOSE C_VALIDATE_INPUT;
   
   INSERT INTO RIS_COMMON_ERROR_LOG(msg_seq_no      ,
                                    entity_id       ,  
                                    entity_name     ,
                                    sub_entity_name ,
                                    attr_char1      ,
                                    attr_char2      ,
                                    attr_char3      ,
                                    attr_clob       ,
                                    attr_xml        ,
                                    attr_num1       ,
                                    attr_num2       ,
                                    attr_num3       ,
                                    status          ,
                                    error_msg       ,
                                    curr_retry_count,
                                    max_retry_count ,
                                    crt_updt_dttm   ,
                                    crt_updt_user_id,
                                    lst_updt_dttm   ,
                                    lst_updt_user_id)
                             SELECT L_msg_seq_no,
                                    entity_id       ,  
                                    entity_name     ,
                                    sub_entity_name ,
                                    attr_char1      ,
                                    attr_char2      ,
                                    attr_char3      ,
                                    attr_clob       ,
                                    attr_xml        ,
                                    attr_num1       ,
                                    attr_num2       ,
                                    attr_num3       ,
                                    status          ,
                                    error_msg       ,
                                    0               ,
                                    L_max_retry_count,  --using the variable here
                                    LP_sysdate      ,
                                    LP_user         ,
                                    LP_sysdate      ,
                                    LP_user
                               FROM TABLE(CAST(I_CEL_TBL AS risgap.RIS_COMMON_ERR_LOG_TBL));

   RETURN TRUE;
EXCEPTION
   WHEN OTHERS THEN 

      O_error_message := SQL_LIB.CREATE_MSG ('PACKAGE_ERROR', 
                                             'CEL Error: Unexpected Error - ' || SQLERRM, 
                                             L_PROGRAM_NAME, 
                                             TO_CHAR (SQLCODE));
    RETURN FALSE;
END LOG_ERRORS;

Open in new window


Thanks,
Mihir
Sean Stuber

OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;   -- Here the variable has value
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN   -- Here it shouldn't be null as there value present in table
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE;
   END IF;

I don't believe your comments are correct.
If l_max_retry_count has a value where you indicate it does - how have you determined that?  The first time you look at the variable is the IS NULL check.

Also, I wasn't saying your use of the cursor was "why" you had a problem, only that it was not a good way to write a single value query like that.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck