I needed to be able to run a stored procedure once for each item in a list. In Microsoft SQL Server, I would probably do this with a table variable and a cursor, and the code would look something like this:
DECLARE @wo_table TABLE (wo_id VARCHAR(10) NOT NULL);
DECLARE @wo_id VARCHAR(10);
INSERT INTO @wo_table (wo_id) VALUES
('123456')
,('234567')
,('345678')
,('456789')
,('567890');
DECLARE wo_cursor CURSOR FOR
SELECT wo_id FROM @wo_table;
OPEN wo_cursor;
FETCH NEXT FROM wo_cursor INTO @wo_id;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC dbo.MyStoredProc @wo_id;
FETCH NEXT FROM wo_cursor INTO @wo_id;
END;
CLOSE wo_cursor;
DEALLOCATE wo_cursor;
However, PL/SQL does not have table variables. While an option like a global temporary table could work, I decided that a PL/SQL table was a better solution.
Declare the PL/SQL table type, instantiate the PL/SQL table with the list of values (strings, in this case), then loop over the PL/SQL table, running the stored proc with each value. Notice that the “EXECUTE” keyword is missing. It is not only unnecessary in this context; it will not work here.
Below is the completed code:
DECLARE
TYPE wo_table_type IS TABLE OF VARCHAR2(10);
wo_table wo_table_type := wo_table_type(
'123456'
,'234567'
,'345678'
,'456789'
,'567890');
BEGIN
FOR i IN 1..wo_table.COUNT LOOP
PackageName.MyStoredProc(wo_table(i));
END LOOP;
END;