Oracle Stored Proc - Compilation Error

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_details 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_project set sponsor_id = ind.cor_id where sponsor_id = ind.incor_id;
update baseline_project set pmoaccmgr_id = ind.cor_id where pmoaccmgr_id = ind.incor_id;
update baseline_project set projectmgr_id = ind.cor_id where projectmgr_id = ind.incor_id;
update baseline_project set comments_userid = ind.cor_id where comments_userid = ind.incor_id;
update baseline_project set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project set delegator_id = ind.cor_id where delegator_id = ind.incor_id;
update baseline_project set pm_md_id = ind.cor_id where pm_md_id = ind.incor_id;
update baseline_project set editor_id = ind.cor_id where editor_id = ind.incor_id;
update baseline_project set evaluator_id = ind.cor_id where evaluator_id = ind.incor_id;
update baseline_project_budget_detail set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_budget_master set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_comp_criteria set last_edited_by = ind.cor_id where last_edited_by =ind.incor_id;
update baseline_project_dependencies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_documents set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_is_isnot set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_kpi set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_md set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_parameters set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_teamroster set user_id = ind.cor_id where user_id = ind.incor_id;
update baseline_project_teamroster set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update baseline_project_url set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update batch_process_reports set fromuser = ind.cor_id where fromuser = ind.incor_id;
update batch_process_reports 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_budget_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_id = ind.cor_id where program_owner_id = 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_master 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_information 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_completion_criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_dependencies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_documents set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_is_isnot set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update program_kpi_details 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_prioritisation 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_assumptions 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_closeout_feedback set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_completion_criteria set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_consideration set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_dependencies set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_documents 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_evaluation set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_is_isnot 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_details 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_nextstep set owner_id = ind.cor_id where owner_id = ind.incor_id;
update project_nextstep set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_parameters set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_prioritisation set last_edited_by = ind.cor_id where last_edited_by = ind.incor_id;
update project_resource_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_teamroster set user_id = ind.cor_id where user_id = ind.incor_id;
update project_teamroster 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_capacity 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_parameters 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_details set owner = ind.cor_id where owner = ind.incor_id;
update task_slack_details set created_by = ind.cor_id where created_by = ind.incor_id;
update team_availability set user_id = ind.cor_id where user_id = ind.incor_id;
delete from teamroster_detail where user_id = ind.incor_id;
update teamroster_md set user_id = ind.cor_id where user_id = ind.incor_id;
update user_report_rights 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.sql" 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.

Jun 7 '07 #1

Subscribe Post Reply

1437

frozenmist

179

Expert 100+

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

Jun 7 '07 #2

ocmeng

thanks for the reply.

it said something like the script success with compilation error

Jun 7 '07 #3

debasisdas

8,127

Expert 4TB

use SHOW ERROR to get the list of errors

then recompile after rectifying the problems.

Jun 7 '07 #4

by: Marc Rondeau | last post by:

Hi every expert. I very need Help! I don't know what is wrong in my code. When i try to execute this code on my access , it give me give this weard error The stored proc have no parameters, i...

Microsoft Access / VBA

Oracle Pro-C supports long long data type?

by: wenmang | last post by:

C / C++

OleDb Provider and parameterized SQL to Oracle

by: Tom | last post by:

Looking for some help with stored procedure call issues. Conceptually, I need to pass a data structure as the sole parameter to the Oracle stored procedure. Sounds simple enough....but how? ...

C# / C Sharp

calling oracle stored proc from .net

by: =?Utf-8?B?Vmlua2k=?= | last post by:

Hello Everyone, I am trying to call a oracle stored proc from .net code and I am getting an error PLS-00306: wrong number or types of arguments in call to KSSP_MEMBER_NEW. I counted all the...

C# / C Sharp

return value from Oracle stored proc

by: =?Utf-8?B?Vmlua2k=?= | last post by:

Hello Everyone, I can successfully insert and update the oracle database by calling a oracles stored proc from my .net code. This oracle stored proc is returning some value. I cannot see that...

C# / C Sharp

getting the oracle stored proc value in .net

by: =?Utf-8?B?Vmlua2k=?= | last post by:

Hello Everyone, I can successfully insert and update the oracle database by calling a oracles stored proc from my .net code. This oracle stored proc is returning some value. I cannot see that...

.NET Framework

return Oracle stored function value back to Access VBA

by: colmkav | last post by:

Can someone tell me how I can access the return value of a function called from Oracle as opposed to a store proc from oracle? my oracle function is get_num_dates_varposfile. I am only used to...

Microsoft Access / VBA

handling errors from stored procs

by: mirandacascade | last post by:

Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...

Microsoft Access / VBA

Access frontal interface for Oracle database

by: Gloops | last post by:

Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...

Microsoft Access / VBA

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Oracle Stored Proc - Compilation Error

Similar topics