We help IT Professionals succeed at work.

PL/SQL Cursor fetch error!

153 Views
1 Endorsement
Last Modified: 2018-11-11
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
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
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.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.