Hello ,
I am trying to run a function and getting the cursor is incomplete error. I am not getting exactly where I missed something.
query:
FUNCTION INS_Process_Log
(
p_Process_Id IN Process_Log.Process_Id%TYPE,
p_Process_Dt IN Process_Log.Process_Dt%TYPE,
p_File_Name IN Process_Log.File_Name%TYPE,
p_Parent_Process_Id IN Process_Log.Parent_Process_Id%TYPE DEFAULT 0,
p_Subtask_Code IN Process_Log.Subtask_Code%TYPE DEFAULT 0
) RETURN NUMBER
IS
l_error_message process_error_log.error_message%TYPE ;
l_possible_solution process_error_log.possible_solution%TYPE ;
l_supporting_data process_error_log.supporting_data%TYPE ;
l_component_name process_error_log.component_name%TYPE ;
l_Process_Log_Id process_log.process_log_id%type;
l_return_status NUMBER ;
l_process_name process.process_name%type;
cursor c_mystat is
SELECT
Process_Log_Id_Seq.NEXTVAL as process_log_id,
SID,
nvl(sys_context('userenv','fg_job_id'),
sys_context('userenv','bg_job_id') ) as job_id
FROM v$mystat ;
l_rec c_mystat%ROWTYPE;
Undeclared_Code EXCEPTION;
PRAGMA EXCEPTION_INIT(Undeclared_Code, -02291);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
OPEN c_mystat;
FETCH c_mystat into l_rec;
CLOSE c_mystat;
SELECT process_name
INTO l_process_name
FROM process
WHERE process_id = p_process_id;
INSERT INTO Process_Log (
Process_Log_Id,
Process_Id,
process_name,
Process_Status,
Process_Dt,
Start_Dt,
File_Name,
comments ,
Subtask_Code,
Parent_Process_Id,
SID,
JOB_ID
)
VALUES(
l_rec.process_log_id,
p_Process_Id,
l_process_name,
'START',
p_Process_Dt,
SYSDATE,
p_File_Name,
to_char(sysdate , 'mm/dd/yyyy hh:mi AM') || ': PROCESS STARTED' ,
p_Subtask_Code,
DECODE(p_Process_Id, 0, l_rec.process_log_id, p_Parent_Process_Id),
l_rec.SID,
l_rec.job_id
) ;
COMMIT;
RETURN l_rec.process_log_id;
EXCEPTION
WHEN Undeclared_Code THEN
ROLLBACK;
l_Process_Log_Id := p_Parent_Process_Id ;
l_Component_Name := 'PCR_Process_Log_PKG.INS_Process_Log';
l_Error_Message := 'Either Or Both Process Code And Process Status Are Invalid.';
l_Supporting_Data := 'Process Code : ' || p_Process_Id ||
', Subtask Code : ' || p_Subtask_Code ;
l_Possible_Solution := 'Ask DBA Or Tier-IV To Verify Existance Of Process_Id In Process Table.';
l\_Return\_Status := PCR\_Process\_Error\_Log\_Pkg.INS\_Process\_Error\_Log
(l\_Process\_Log\_Id, l\_Component\_Name, l\_Error\_Message, l\_Supporting\_Data, l\_Possible\_Solution);
RETURN(E\_API\_CALL\_NOT\_SUCCESSFUL);
WHEN OTHERS THEN
ROLLBACK;
l_Process_Log_Id := p_Parent_Process_Id ;
l_Component_Name := 'PCR_Process_Log_PKG.INS_Process_Log';
l_Error_Message := 'Error Creating Process Log';
l_Supporting_Data := SQLERRM(SQLCODE) ||
', Process Code : ' || p_Process_Id ||
', Subtask Code : ' || p_Subtask_Code ||
', Process Date : ' || TO_CHAR(p_Process_Dt,'MM/DD/YYYY');
l_Possible_Solution := 'Contact Application DBA With Supporting Data For Further Recomendation.';
l\_Return\_Status := PCR\_Process\_Error\_Log\_Pkg.INS\_Process\_Error\_Log
(l\_Process\_Log\_Id, l\_Component\_Name, l\_Error\_Message, l\_Supporting\_Data, l\_Possible\_Solution);
RETURN(E\_API\_CALL\_NOT\_SUCCESSFUL);
END INS_Process_Log;
Please someone help me to debug this error. I am getting compilation error as below:
