2 Replies Latest reply: Aug 27, 2013 4:59 PM by tomtom26 RSS

    unable to create plsql procedure, fails at cursor

    866532

      This was a working procedure which would take ID and then copy data from source parameter to destination parameter. Now I would like to have NAME as parameter, I have changed the code to accommodate the new parameters. But I am not able to create the procedure. I am getting 3 error at CURSOR (in bold red). I would like really appreciate if someone can take a look and let me know what is wrong.

      Thanks in advance

       

      3 ERRORs

      1. PLS-00103: Encountered the symbol "CUR_V_HSP_COLUMN_DETAIL" when expecting one of the following:

       

         := . ( @ % ;

      The symbol ":=" was substituted for "CUR_V_HSP_COLUMN_DETAIL" to continue.

       

      2. PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:

       

         (

      The symbol "(" was substituted for "NUMBER" to continue.

       

      3. PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:

       

         (

       

       

      CREATE OR REPLACE procedure EPM_PLAN_PLANSAMP.Copy_Details_test1 --Arguments

      ( in_From_Version_Name IN VARCHAR2, --HSP_object.OBJECT_NAME - Version From

      in_From_Scenario_Name IN VARCHAR2 , --HSP_object.OBJECT_NAME - Scenarios From

      in_From_Year_Name IN VARCHAR2 , --HSP_object.OBJECT_NAME - Year From

      in_To_Version_Name IN VARCHAR2, --HSP_object.OBJECT_NAME - Version To

      in_To_Scenario_name IN VARCHAR2, --HSP_object.OBJECT_NAME - Scenarios To

      in_To_Year_Name IN VARCHAR2 --HSP_object.OBJECT_NAME - Year To

      )

       

      IS

       

      v_From_Object_Id number; --  Version From

      s_From_Object_Id number; -- Scenarios From

      y_From_Object_Id number; -- Year From

      v_To_Object_Id number; -- Version To

      s_To_Object_Id number; -- Scenarios To

      y_To_Object_Id number; -- Year To

       

      BEGIN

       

      Select object_id into v_From_Object_Id

      from hsp_object

      where object_type = 35

      and object_name = in_from_version_name;

       

      Select object_id into s_From_Object_Id

      from hsp_object

      where object_type = 31

      and object_name = in_from_scenario_name;

       

      Select object_id into y_From_Object_Id

      from hsp_object

      where object_type = 38

      and object_name = in_from_year_name;

       

      Select object_id into v_To_Object_Id

      from hsp_object

      where object_type = 35

      and object_name = in_to_version_name;

       

      Select object_id into s_To_Object_Id

      from hsp_object

      where object_type = 31

      and object_name = in_to_scenario_name;

       

      Select object_id into y_To_Object_Id

      from hsp_object

      where object_type = 38

      and object_name = in_to_year_name;

       

       

      --Select Supporting Details for the current Version

      CURSOR Cur_V_HSP_COLUMN_DETAIL (cV_From_Object_Id IN NUMBER, cS_From_Object_Id IN NUMBER) IS

      Select DETAIL_ID From EPM_PLAN_PLANSAMP.HSP_COLUMN_DETAIL Where DIM5 = cV_From_Object_Id AND DIM1 = cS_From_Object_Id;

       

      li_DETAIL_ID NUMBER;

      Li_Next_DETAIL_ID NUMBER;

      FETCH_STATUS NUMBER := 0;

      v_step_name varchar2(200);

      v_rec_cnt number := 0;

      v_cnt number;

      v_err varchar2(2000);

       

       

      -----------------------------------------Begin Copy Version ---------------------------

       

      BEGIN

      -- Delete Next version if already exists

      v_step_name := 'Delete on HSP_COLUMN_DETAIL_ITEM';

       

      Delete from HSP_COLUMN_DETAIL_ITEM

      Where DETAIL_ID in (Select DETAIL_ID from HSP_COLUMN_DETAIL

      Where DIM5 = v_To_Object_Id AND DIM1 = s_To_Object_Id);

       

      v_cnt := sql%rowcount;

      insert into t_copy_supporting_dtls_log values (v_step_name, v_cnt,1,'Success',sysdate);

       

      v_step_name := 'Delete on HSP_COLUMN_DETAIL';

       

      Delete from HSP_COLUMN_DETAIL

      where DIM5 = v_To_Object_Id AND DIM1 = s_To_Object_Id;

       

      v_cnt := sql%rowcount;

      insert into t_copy_supporting_dtls_log values (v_step_name, v_cnt,1,'Success',sysdate);

       

      Open Cur_V_HSP_COLUMN_DETAIL(v_From_Object_Id, s_From_Object_Id);

       

      v_step_name := 'Inserts ';

       

      LOOP

       

      FETCH Cur_V_HSP_COLUMN_DETAIL INTO li_DETAIL_ID;

      EXIT WHEN Cur_V_HSP_COLUMN_DETAIL%NOTFOUND;

      -- Find next detail_id

      Select Max(DETAIL_ID) + 1 INTO Li_Next_DETAIL_ID From HSP_COLUMN_DETAIL;

       

      -- Insert Into HSP_COLUMN_DETAIL Table

      Insert Into HSP_COLUMN_DETAIL ( DETAIL_ID , PLAN_TYPE , DIM1 , DIM2 , DIM3 , DIM4 , DIM5 , DIM6 ,

      DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

      DIM16 , DIM17 , DIM18 , DIM19 , DIM20 )

      Select Li_Next_DETAIL_ID , PLAN_TYPE , S_To_Object_Id , DIM2 , DIM3 , DIM4 , V_To_Object_Id , DIM6 ,

      DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

      DIM16 , DIM17 , DIM18 , DIM19 , DIM20

      From HSP_COLUMN_DETAIL

      Where DETAIL_ID = li_DETAIL_ID;

       

      v_rec_cnt := v_rec_cnt + sql%rowcount;

       

      -- Insert Into HSP_COLUMN_DETAIL_ITEM Table

      Insert Into HSP_COLUMN_DETAIL_ITEM ( DETAIL_ID , VALUE , POSITION , GENERATION , OPERATOR , LABEL)

      Select Li_Next_DETAIL_ID , VALUE , POSITION , GENERATION , OPERATOR , LABEL

      From HSP_COLUMN_DETAIL_ITEM Where DETAIL_ID = li_DETAIL_ID;

       

      v_rec_cnt := v_rec_cnt + sql%rowcount;

       

      END LOOP;

       

      Close Cur_V_HSP_COLUMN_DETAIL;

       

      insert into t_copy_supporting_dtls_log values (v_step_name, v_rec_cnt,1,'Success',sysdate);

      commit;

       

      exception when others then

      rollback;

      v_err := substr(sqlerrm,1,2000);

      insert into t_copy_supporting_dtls_log values (v_step_name, 0,-1,v_err,sysdate);

      commit;

      END;

      END;

      /