Executing Stored Procedure from inside Cursor For Loop?

740713

I originally posted this in the SQL Developer forum, but I tried it in SQLPlus and am getting the same error, so I think it's a coding issue.
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.

/* Declares source_fac and dest_fac variables.
The source_fac is the facility we are copying parts from.
The dest_fac is the facility we are copying parts to.*/

DECLARE
source_fac facility.facility_num%type;
DEST_FAC FACILITY.FACILITY_NUM%TYPE;

/*Declares cursor to be used in for loop.
Cursor loads facility number and facility's state.
Doesn't get facility 1 or 2, since these will be
the master source facilities.*/

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

/*Test for Facility State. If a canadian state, set source_fac to 2.
Otherwise, set source_fac to facility 1.*/

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;

/*Sets the destination facility to the facility_num from the cursor*/

DEST_FAC := FAC_ROW.FACILITY_NUM;

/*Execute the three procedures, passes the source and dest fac variables*/

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;

Edited by: SunDevilKid on Mar 3, 2010 3:31 PM
Updated comments so you can make more sense of the code.

730428

EXECUTE is a SQLPlus command, change your code to

dest_fac := fac_row.facility_num;
ptfile_copy_fac(source_fac, dest_fac);
ptxref_copy_fac(source_fac, dest_fac);
ptvndrs_copy_fac(source_fac, dest_fac);
END LOOP;

Max
http://oracleitalia.wordpress.com

740713

FWIW, the datatype for source_fac and dest_fac are just integers.

6363

I think you just want to remove the executes.

All this looping looks like an awfully bad way of doing something.

730428

EXECUTE is a SQLPlus command, change your code to

dest_fac := fac_row.facility_num;
ptfile_copy_fac(source_fac, dest_fac);
ptxref_copy_fac(source_fac, dest_fac);
ptvndrs_copy_fac(source_fac, dest_fac);
END LOOP;

Max
http://oracleitalia.wordpress.com

740713

Bingo, that did it. So in SQL Developer, you don't need to use the Exec statement? Chalk up another syntax difference between T-SQL and PL/SQL.

Pointless, if you have a better suggestion, I'm all ears. I don't like cursors, but it seemed the best way to do this.

The way I see it, I can either copy 7995 parts 160 times, or I can copy 1 part at a time to each 160 facilities, repeated for all 7995 parts... Not sure what better way there is to do it other then loop through for each facility.