3 Replies Latest reply on Nov 12, 2018 5:08 PM by rp0428

    ERROR: DECLARATION OF CURSOR IS incomplete or malformed

    akhilreddy

      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: