Oracle Stored Proc - Compilation Error

[ocmeng]

Dear All,

I am kinda new to Oracle and need to work on a stored proc to update some data. However, I am getting compilation error for the following stored proc and got no idea on how to resolve it.

=============== =============== ========
CREATE or REPLACE procedure RemoveDupID as
begin
for ind in (select cor_id, incor_id from duplicateID) loop
update account_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update activity set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update activity_detail s set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update audit_trail set user_id = ind.cor_id where user_id = ind.incor_id;
update audit_trail set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update band set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t set sponsor_id = ind.cor_id where sponsor_id = ind.incor_id;
update baseline_projec t set pmoaccmgr_id = ind.cor_id where pmoaccmgr_id = ind.incor_id;
update baseline_projec t set projectmgr_id = ind.cor_id where projectmgr_id = ind.incor_id;
update baseline_projec t set comments_userid = ind.cor_id where comments_userid = ind.incor_id;
update baseline_projec t set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t set delegator_id = ind.cor_id where delegator_id = ind.incor_id;
update baseline_projec t set pm_md_id = ind.cor_id where pm_md_id = ind.incor_id;
update baseline_projec t set editor_id = ind.cor_id where editor_id = ind.incor_id;
update baseline_projec t set evaluator_id = ind.cor_id where evaluator_id = ind.incor_id;
update baseline_projec t_budget_detail set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_budget_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_comp_criteria set last_edited_by = ind.cor_id where last_edited_by =ind.incor_id;
update baseline_projec t_dependencies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_documents set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_is_isnot set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_kpi set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_md set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_parameters set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_teamroster set user_id = ind.cor_id where user_id = ind.incor_id;
update baseline_projec t_teamroster set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_projec t_url set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update batch_process_r eports set fromuser = ind.cor_id where fromuser = ind.incor_id;
update batch_process_r eports set touser = ind.cor_id where touser = ind.incor_id;
update bg_project set sponsor_id = ind.cor_id where sponsor_id = ind.incor_id;
update bg_project set originator_id = ind.cor_id where originator_id = ind.incor_id;
update bg_project set pmoaccmgr_id = ind.cor_id where pmoaccmgr_id = ind.incor_id;
update bg_project set projectmgr_id = ind.cor_id where projectmgr_id = ind.incor_id;
update bg_project set comments_userid = ind.cor_id where comments_userid = ind.incor_id;
update bg_project set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update bg_project set delegator_id = ind.cor_id where delegator_id = ind.incor_id;
update bg_project set pm_md_id = ind.cor_id where pm_md_id = ind.incor_id;
update bg_project set editor_id = ind.cor_id where editor_id = ind.incor_id;
update bg_project_budg et_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update bg_project_kpi set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update bg_user_access set user_id = ind.cor_id where user_id = ind.incor_id;
update budget_exercise set created_by = ind.cor_id where created_by = ind.incor_id;
update budget_exercise set updated_by = ind.cor_id where updated_by = ind.incor_id;
update budget_exercise _detail set created_by = ind.cor_id where created_by = ind.incor_id;
update budget_exercise _detail set last_updated_by = ind.cor_id where last_updated_by = ind.incor_id;
update budget_exercise _master set program_owner_i d = ind.cor_id where program_owner_i d = ind.incor_id;
update budget_exercise _master set created_by = ind.cor_id where created_by = ind.incor_id;
update budget_exercise _master set updated_by = ind.cor_id where updated_by = ind.incor_id;
update category_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update cbe set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update classification_ master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update consideration_m aster set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update department set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update division set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update email_log set from_id = ind.cor_id where from_id = ind.incor_id;
update email_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update email_master set from_id = ind.cor_id where from_id = ind.incor_id;
update email_reminder set from_id = ind.cor_id where from_id = ind.incor_id;
update group_details set user_id = ind.cor_id where user_id = ind.incor_id;
update group_details set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update groupinfo set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update initiative set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update initiative set il_id = ind.cor_id where il_id = ind.incor_id;
update kpi set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update login_informati on set user_id = ind.cor_id where user_id = ind.incor_id;
update message_master set last_edited_by = ' 4378' where last_edited_by = ind.incor_id;
update perspective set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_complet ion_criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_depende ncies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_documen ts set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_is_isno t set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_kpi_det ails set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_md set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_owner set owner_id = ind.cor_id where owner_id = ind.incor_id;
update program_priorit isation set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_score set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project set sponsor_id = ind.cor_id where sponsor_id = ind.incor_id;
update project set originator_id = ind.cor_id where originator_id = ind.incor_id;
update project set pmoaccmgr_id = ind.cor_id where pmoaccmgr_id = ind.incor_id;
update project set projectmgr_id = ind.cor_id where projectmgr_id = ind.incor_id;
update project set comments_userid = ind.cor_id where comments_userid = ind.incor_id;
update project set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project set delegator_id = ind.cor_id where delegator_id = ind.incor_id;
update project set pm_md_id = ind.cor_id where pm_md_id = ind.incor_id;
update project set editor_id = ind.cor_id where editor_id = ind.incor_id;
update project set evaluator_id = ind.cor_id where evaluator_id = ind.incor_id;
update project_assumpt ions set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_budget set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_budget_ detail set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_budget_ master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_change_ request set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_change_ request set created_by = ind.cor_id where created_by = ind.incor_id;
update project_closeou t_feedback set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_complet ion_criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_conside ration set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_depende ncies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_documen ts set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_effort_ estimate set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_evaluat ion set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_is_isno t set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_issue set owner_id = ind.cor_id where owner_id = ind.incor_id;
update project_issue set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_kpi set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_kpi_det ails set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_md set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_nextste p set owner_id = ind.cor_id where owner_id = ind.incor_id;
update project_nextste p set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_paramet ers set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_priorit isation set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_resourc e_demand set user_id = ind.cor_id where user_id = ind.incor_id;
update project_risk set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_score set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_success _criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_task set created_by = ind.cor_id where created_by = ind.incor_id;
update project_task set maintained_by = ind.cor_id where maintained_by = ind.incor_id;
update project_teamros ter set user_id = ind.cor_id where user_id = ind.incor_id;
update project_teamros ter set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_url set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update resource_capaci ty set user_id = ind.cor_id where user_id = ind.incor_id;
update risk_management set owner = ind.cor_id where owner = ind.incor_id;
update risk_management set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update score_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update skills set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update source_fund set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update system_paramete rs set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update task_resource set user_id = ind.cor_id where user_id = ind.incor_id;
update task_resource set created_by = ind.cor_id where created_by = ind.incor_id;
update task_resource set maintained_by = ind.cor_id where maintained_by = ind.incor_id;
update task_slack_deta ils set owner = ind.cor_id where owner = ind.incor_id;
update task_slack_deta ils set created_by = ind.cor_id where created_by = ind.incor_id;
update team_availabili ty set user_id = ind.cor_id where user_id = ind.incor_id;
delete from teamroster_deta il where user_id = ind.incor_id;
update teamroster_md set user_id = ind.cor_id where user_id = ind.incor_id;
update user_report_rig hts set user_id = ind.cor_id where user_id = ind.incor_id;
delete from user_roles where user_id = ind.incor_id;
delete from user_master where user_id = ind.incor_id;
commit;
end loop;
end;
=============== =============== ==

I have save the above script in a file named "RemoveDupID.sq l" and place it in C:\. For deployment, I log in with the database user account and use the following command:

SQL> @ C:\RemoveDupID. sql;
145> /

Pls let me know if i miss out anything. Thanks in advance.

[frozenmist]

Hi,
Can you please post what error you are getting?

[ocmeng]

thanks for the reply.

it said something like the script success with compilation error

[debasisdas]

use SHOW ERROR to get the list of errors

then recompile after rectifying the problems.