What is wrong with this api Pls help
Posted: Tue Nov 11, 2008 7:41 am
I am creating api to migrate jobs from an extenal table to hr but when executing the script it only inserts the first record and gives this message
"-20001 ORA-20001: The job you have entered already exists in this Business Group. Please enter a unique name for your job."
as I mentioned the job which he means already created is the first row of the table
here is the code
DECLARE
l_count Number :=0;
v_business_group_id Number :=101;
v_date_from date := TO_DATE('01-01-1900','DD-MM-YYYY');
v_job_group_id number := 21;
v_object_version_number number :=1;
v_segment1 varchar2(10);
v_segment2 varchar2(150);
v_attribute2 varchar2(150);
v_attribute3 varchar2(100);
v_attribute4 varchar2(50);
v_attribute5 varchar2(50);
v_attribute6 varchar2(50);
cursor job is select DESGN_CODE,
DESGN_DESP_M_A,
DESGN_DESP_F_A,
DESGN_DESP_E ,
DESGN_TYPE ,
DESGN_CAT_CODE,
GRP_CODE
from aa_tdesignation
Where rownum < 10
and desgn_desp_e is not null;
v_segment5 varchar2(100) := NULL;
v_segment6 varchar2(100) := NULL;
v_job_id number;
v_job_definition_id number;
v_name varchar2(100);
begin
dbms_output.put_line('##########################################################');
dbms_output.put_line('Data Migration Of Jobs:');
dbms_output.put_line('##########################################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
for my_cur in job
LOOP
BEGIN
v_segment1 := my_cur.DESGN_CODE;
v_segment2 := my_cur.DESGN_DESP_M_A;
v_attribute2 := my_cur.DESGN_DESP_F_A;
v_attribute3 := my_cur.DESGN_DESP_E;
v_attribute4 := my_cur.DESGN_TYPE;
v_attribute5 := my_cur.DESGN_CAT_CODE;
v_attribute6 := my_cur.GRP_CODE;
HR_JOB_API.CREATE_JOB
(p_business_group_id => v_business_group_id
,p_date_from => v_date_from
,p_job_group_id => v_job_group_id
,p_object_version_number => v_object_version_number
,p_segment1 => v_segment1
,p_segment2 => v_segment2
,p_segment5 => v_segment5
,p_segment6 => v_segment6
,p_attribute2 => v_attribute2
,p_attribute3 => v_attribute3
,p_attribute4 => v_attribute4
,p_attribute5 => v_attribute5
,p_attribute6 => v_attribute6
,p_job_id => v_job_id
,p_job_definition_id => v_job_definition_id
,p_name => v_name);
Dbms_output.put_line('Sucess'||' '||my_cur.DESGN_CODE||' '||my_cur.DESGN_DESP_M_A);
L_COUNT:=L_COUNT+1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
Exception
When others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
end;
/
Thanks
"-20001 ORA-20001: The job you have entered already exists in this Business Group. Please enter a unique name for your job."
as I mentioned the job which he means already created is the first row of the table
here is the code
DECLARE
l_count Number :=0;
v_business_group_id Number :=101;
v_date_from date := TO_DATE('01-01-1900','DD-MM-YYYY');
v_job_group_id number := 21;
v_object_version_number number :=1;
v_segment1 varchar2(10);
v_segment2 varchar2(150);
v_attribute2 varchar2(150);
v_attribute3 varchar2(100);
v_attribute4 varchar2(50);
v_attribute5 varchar2(50);
v_attribute6 varchar2(50);
cursor job is select DESGN_CODE,
DESGN_DESP_M_A,
DESGN_DESP_F_A,
DESGN_DESP_E ,
DESGN_TYPE ,
DESGN_CAT_CODE,
GRP_CODE
from aa_tdesignation
Where rownum < 10
and desgn_desp_e is not null;
v_segment5 varchar2(100) := NULL;
v_segment6 varchar2(100) := NULL;
v_job_id number;
v_job_definition_id number;
v_name varchar2(100);
begin
dbms_output.put_line('##########################################################');
dbms_output.put_line('Data Migration Of Jobs:');
dbms_output.put_line('##########################################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
for my_cur in job
LOOP
BEGIN
v_segment1 := my_cur.DESGN_CODE;
v_segment2 := my_cur.DESGN_DESP_M_A;
v_attribute2 := my_cur.DESGN_DESP_F_A;
v_attribute3 := my_cur.DESGN_DESP_E;
v_attribute4 := my_cur.DESGN_TYPE;
v_attribute5 := my_cur.DESGN_CAT_CODE;
v_attribute6 := my_cur.GRP_CODE;
HR_JOB_API.CREATE_JOB
(p_business_group_id => v_business_group_id
,p_date_from => v_date_from
,p_job_group_id => v_job_group_id
,p_object_version_number => v_object_version_number
,p_segment1 => v_segment1
,p_segment2 => v_segment2
,p_segment5 => v_segment5
,p_segment6 => v_segment6
,p_attribute2 => v_attribute2
,p_attribute3 => v_attribute3
,p_attribute4 => v_attribute4
,p_attribute5 => v_attribute5
,p_attribute6 => v_attribute6
,p_job_id => v_job_id
,p_job_definition_id => v_job_definition_id
,p_name => v_name);
Dbms_output.put_line('Sucess'||' '||my_cur.DESGN_CODE||' '||my_cur.DESGN_DESP_M_A);
L_COUNT:=L_COUNT+1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
Exception
When others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
end;
/
Thanks