Category: Oracle Database

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;