Unanswered: scope of sessions using temporary tables
Hi - I'm running an Oracle stored procedure that populates a temporary table. The contents of this table are then returned to a crystal report via the ref cursor method.
When I call the procedure from a crystal report the data is displayed but when I refresh the crystal report the data is duplicated i.e. the rows are still held in the temporary table from the first run and have been added again. The rows do not seem to delete until I close crystal. Also if I open a new report and base it on the same procedure I can reference rows created by the first report.
I would really appreciate information on the scope of sessions. If I have crystal open are rows created in temp tables owned by the application or specific to each open report (the example above suggests the application) and any ideas on how to correct the above problem
The scope of a global temporary table is the current session. If you have on commit peserve rows then you need to treat the table more like a perminate table (you need to delete from the table when you want to empty it for reuse within the same session) But even with this setting the rows will not exist outside the session (when the session exits the table will be removed). So when you refreshed the table the script ran to reload the table with the same rows (your duplicates) So you should probably have the procedure delete from the table prior to loading it to make sure it is not a refresh.
Mark - Thanks for the suggestion of the explicit delete from the table. Could you give me a definition on what is considered to be the current session?
For example if I have the Crystal Reports application open is that considered to be a session or is each individual report I have open in crystal constitute a session. My problem seems to suggest the first which means that 1 report can see the records of the other report
Any information on this would be really appreciated
The sessions can be found in the V$session table basically each connection to the database is a session. A 'session' is created when a user logs in to the database and depending upon you application you may have more then one session (in our tools for example we have a GIS connection for spatial data and an ado connection for some special tools support). You can look at the program field of the V$session and it usually has the name of the program that created the connection but not always. So you need to look at this table and run you tool and see how it behaves. If it stays connected (perfered behavior and looks like it is) then the multiple reports run in the same session will see the perserved rows from the previous runs. Another setting you may consider is the on commit delete rows. (May not work) this makes the rows you insert into the temporay table exist until you commit so they are not only session based but limited to a transaction. But none of the software you use may commit or you will lose the rows. (I have not had a good use for this option our products commit too often)