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;
/