This discussion is archived
2 Replies Latest reply: Aug 27, 2013 2:59 PM by tomtom26 RSS

unable to create plsql procedure, fails at cursor

866532 Newbie
Currently Being Moderated

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;

/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points