Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Executing Stored Procedures inside a Cursor For Loop

740713Mar 3 2010 — edited Mar 4 2010
I have a piece of code that I'm trying to write that will only be run once. The goal: We have three tables related to parts info. Each table has a column that stores the site number that the part belongs to. We want to copy the parts from one site to about 130 sites that have no parts info. The site numbers are stored in another table. So I created three stored procedures, one for each of the three tables that we have, that take 2 inputs: a source site and destination site. The procedure names are: ptfile_copy_fac, ptxref_copy_fac, and ptvndrs_copy_fac.

The problem I'm having is that I can execute the procedures in a separate worksheet in SQL Developer, but when they are embedded in this cursor for loop, I get the following message from SQL Developer:

ORA-06550: line 23, column 11:
PLS-00103: Encountered the symbol "PTFILE_COPY_FAC" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "PTFILE_COPY_FAC" to continue.

It repeats this for each of the three procedures. I've attached the code I'm trying to run below. It's expecting an assignment operator, but I have no idea why.

If there is a better way to be doing this, by all means let me know. I'm a SQL Server guy, so I'm not quite sure how to do what I need to do using PL\SQL.

DECLARE
source_fac facility.facility_num%type;
dest_fac facility.facility_num%type;
CURSOR fac_cursor
IS
SELECT
facility_num,
div_state
FROM
facility
WHERE
facility_num NOT IN (1,2);
BEGIN
FOR fac_row IN fac_cursor LOOP
IF fac_row.div_state IN ('AB','BC','MB','NB','NL','NT','NS','NU','ON','PE','QC','SK','YT')
THEN
source_fac := 2;
ELSE
source_fac := 1;
END iF;
dest_fac := fac_row.facility_num;
execute ptfile_copy_fac(source_fac, dest_fac);
execute ptxref_copy_fac(source_fac, dest_fac);
execute ptvndrs_copy_fac(source_fac, dest_fac);
END LOOP;
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2010
Added on Mar 3 2010
4 comments
4,058 views