Executing Stored Procedures in Loops Using PL/SQL Tables

Oracle Database PL/SQL

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;

Primavera P6 Session Hung on Oracle Database

Oracle Primavera P6 EPPM

Occasionally a user will close a P6 application and the user’s session does not just go away. The easiest thing I’ve found is to check to see if there are hung sessions in the usession table (in Oracle – other database types may use a different name). This is the type of error you might see:

Primavera P6 hung user session

Look for hung sessions by running the following query, plugging the actual username into ‘Username’:

SELECT * FROM usession WHERE user_id = (
SELECT user_id FROM users WHERE user_name = 'Username');

If any are present, run this query:

DELETE FROM usession WHERE user_id = (
SELECT user_id FROM users WHERE user_name = 'Username');
COMMIT;

The user should again be able to log into P6 at this point.

%d bloggers like this: