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
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
sdstuberCommented:
It's inefficient to open/fetch/close like that.  You should simply use an implicit cursor.  The code will be simpler as well.

Use an small anonymous block within your existing block to read the value and check for missing record.

    BEGIN
        SELECT max_retry_count
          INTO l_max_retry_count
          FROM ris_common_error_config
         WHERE module = 'PRICING' AND TYPE = 'LOG' AND ROWNUM=1;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            l_max_retry_count := NULL;
    END;

    IF l_max_retry_count IS NULL
    THEN
        o_error_message := 'INVALID_MAX_RETRY_COUNT';
        RETURN FALSE;
    END IF;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
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
sdstuberCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.