Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Executing Stored Procedure from inside Cursor For Loop?

740713
Member Posts: 16
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.
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.
Best Answer
-
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
Answers
-
FWIW, the datatype for source_fac and dest_fac are just integers.
-
I think you just want to remove the executes.
All this looping looks like an awfully bad way of doing something. -
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 -
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.
This discussion has been closed.