I have a ruby program that needs to load a lot of data from different views concurrently using large IN clauses. Regular sql gives me PGA limit error. My next approach is to create a function that takes in an array of ids and a sql statement, inserts the ids into a global temporary table then runs the sql query using exists on the global temporary table. This seems to work fine when run serially but errors out when done concurrently.

Here's my global temporary_table. This table has been pre-created and is never dropped.`p`p

CREATE GLOBAL TEMPORARY TABLE temp_id_table (
  id NUMBER
)
on commit preserve rows;

Here's my package

CREATE OR REPLACE PACKAGE test.bulk_query
IS
  TYPE temp_ids IS TABLE OF NUMBER;

  FUNCTION run_query(ids IN temp_ids, query IN VARCHAR2) RETURN sys_refcursor;

END bulk_query;
/

CREATE OR REPLACE PACKAGE BODY test.bulk_query
IS
  /* Retrieve data for syncing
   */
  FUNCTION run_query(ids IN temp_ids, query IN VARCHAR2) RETURN sys_refcursor
  IS
    dataset SYS_REFCURSOR;
  BEGIN
    FOR i IN 1 .. ids.count LOOP
      execute immediate 'INSERT INTO TEMP_ID_TABLE (ID) VALUES (:id)' using ids(i);
    END LOOP;

    OPEN dataset FOR query;

    commit;

    RETURN dataset;
  END;
END bulk_query;
/

The error that I'm getting when run concurrently is this:

ORA-00955: name is already used by an existing object

Again, when run serially, it finishes fine. I am not using a connection pool. What am I doing wrong?

    FOR i IN 1 .. ids.count LOOP
      execute immediate 'INSERT INTO TEMP_ID_TABLE (ID) VALUES (:id)' using ids(i);
    END LOOP;