Does bulk collect affect multiple sesssions?

Question

I have a piece of code that uses tables as well as PL/SQL tables and collections. This piece of code runs for multiple sessions (multiple companies in our business terms)

 create or replace TYPE TY_REC FORCE IS OBJECT
  (
   :
   :
  );

 create or replace TYPE  TY_TAB  AS TABLE OF TY_REC ;


  v_tab_nt.DELETE;
  FETCH v_tab_cur BULK COLLECT INTO  v_tab_nt;
  CLOSE v_tab_cur ;

  FOR i IN v_tab_nt.FIRST..v_tab_nt.LAST
     LOOP
       :
       :
          insert into xyz table --this table is present in multiple schema's
     END LOOP;

This is working fine in my dev enviornment ,but today's in productions i can see v_tab_cur is fetching data from schema1 and inserting data into xyz table of schema2,which looks strange to be ,the amount of data is huge. Can anyone make a guess of what is wrong with the bulk collect.

Offhand the only thing I can think of is a public synonym problem.. Unless forced to used a complete table name for the insert owner.table when there is public synonym for a table in another schema and you are logged into an account that can update the table this could happen. Run the code with a user that see only one schema, and does not have any access outside that schema. — jim mcnamara, Dec 02 '14 at 13:26
It could also be a matter of which schema is being used by default in production vs. development. The code above has been so heavily edited that I can't understand what's going on. For example, the text of `v_tab_cur` is not shown so there's no way to know what the cursor is attempting to fetch. — Bob Jarvis - Слава Україні, Dec 02 '14 at 13:31
@jim mcnamara:No hardcoding of schema is used in the code ,package is `authid current_user`. — Gaurav Soni, Dec 02 '14 at 15:48
@BobJarvis : v_tab_cur is referring to tables that is specific to schema ,and no hardcoding is been done ,the package run with auth id current user .And one more thing ,this code is a batch job ,which runs for all the companies(schema's) at an interval of 6 sec. — Gaurav Soni, Dec 02 '14 at 15:55
Bob and I are saying pretty much the same thing. You cannot ignore the fact that when this code runs there is an invalid cross-schema reference to the table being selected/updated. If a user can access **one and only one** table (for either select or insert) because the user cannot get to the other schema then what you have cannot happen. Period. — jim mcnamara, Dec 02 '14 at 17:16
@jimmcnamara: Thanks for the reply,still we have 100 companies ,if some cross -schema reference to the table exists then it should happen for all the 100 companies ,but it happens for 2 companies only,for rest of the company output is what we need.I think my question is ambiguous ,my question arises because i have read that pl sql tables use PGA ,still i need to do more research and then come to you ,thanks alot for your help. — Gaurav Soni, Dec 02 '14 at 19:27
No - it would not necessarily happen for 100 companies. This is really a DBA issue. What you describe fits even better to what @BobJarvis and I have been saying. You need to alert your DBA, give her the table name(s) that show the problems and the username(s) you are running the code under. It is not code issue. — jim mcnamara, Dec 02 '14 at 20:31

Does bulk collect affect multiple sesssions?

0 Answers0