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 .
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.
Please let me know if there need more info.
Thanks,
Mihir
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;
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.
Please let me know if there need more info.
Thanks,
Mihir
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 .
Thanks,
Mihir
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;
Thanks,
Mihir
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.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
We need to see your INSERT, best in a simplified test case.