We have a stored procedure in Oracle that uses global temporary tables. In most of our other stored procedures, first thing we do is delete data from global temporary tables. However, in few of the stored procedures we do not have the delete's.
Are there any other options other than adding the delete statements? Can something be done on the Server side to forcefully delete data from those temporary tables when that SP is ran?
the GTT's are defined with ON COMMIT PRESERVE ROWS;
I think your title is misleading: the problem is not "getting data from different session", it is re-using the same session. Terminating a session always flushes a temporary table:
SQL> conn apc
Enter password:
Connected.
SQL> create global temporary table tmp_23 (username varchar2(30))
2 on commit preserve rows
3 /
Table created.
SQL> insert into tmp_23 values (user)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from tmp_23
2 /
USERNAME
------------------------------
APC
SQL> conn apc
Enter password:
Connected.
SQL> select * from tmp_23
2 /
no rows selected
SQL>
From within a session there is no way to flush a temporary table which has PRESERVE ROWS except by deletion of truncation. There is no way to annotate a stored procedure in the manner you suggest. So I'm afraid that if you are experiencing the problem as you describe it you will have to bite the bullet and add the DELETE (or TRUNCATE) calls to your procedures. Or define the tables with DELETE ROWS; but that probably won't suit your processing.
Incidentally, it seems like you are using temporary tables quite heavily. This is unusual in Oracle systems, because temporary tables are relatively expensive objects (all those writes to disk) and there is normally a more performant way approaching things: e.g. caching data in PL/SQL collections or just using SQL. It is common for developers coming from a non-Oracle background - especially SQL Server - to overuse temporary tables because they are used to that way of working.