We help IT Professionals succeed at work.

Viewing data in temporary table.

gram77
gram77 asked
on
Medium Priority
3,163 Views
Last Modified: 2016-09-02
The code i work on uses temporary tables.

Temporary tables are available till session lasts.

I use oracle sql developer debugger, which can freeze code execution till next line to where the temporary table is inserted into.


Is it possible to view the data into temporary table in such a case?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
by temporary table do you mean Global Temporary Tables (GTT)?

Only the session that inserts data into a GTT can see it.  I'm not familiar with SQL Developer but as long as the SQL worksheet doesn't create a new database session, you should be able to query the GTT.

I suggest you just try it and see?

Author

Commented:
If a oracle sql developer session is running on a debug mode, the code execution freezes at the line next to insert into gtt,

how can i check the values into gtt in the same session
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I'm not a sql developer person but I would hope you can select the sql worksheet while the debugger is running or at least have a window where you can issue 'debug' type commands.
Top Expert 2011

Commented:
- in sql developer there is dbms output window. you can google for how to use dbms_output.put_line in your codes to output the data and it will display on the dbms output window.

Author

Commented:
can't use debug statements as i have only read access in uat
Top Expert 2011

Commented:
- try open up a sql query window then query the temporary table while the code execution freezes. see if that works.

Author

Commented:
sql window will be a new session
Top Expert 2011
Commented:
- without granted with the rights/privileage, you would not be able to alter the codes then i dont think you be able to query/view the value for the temporary table.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It appears you might be able to see the GTT values with some of the options in the debug window itself.

Again, I've not played with SQL Developer much but here is a quick tutorial that shous how you can step into code and display/modify certain data points.

I would hope a GTT cursor would be one of the objects you could view.

http://st-curriculum.oracle.com/obe/db/11g/r2/prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm

Commented:
gram77:

If you just need to see the values for debugging purposes, why not create a regular table, and use it for debugging, then go back to using the temp table.

AielloJ
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Aiello,

Read a few posts back.  They have read only access.

Commented:
slightwv:

They can still create and populate a static table with a SELECT statement for testing and debugging purposes.  That will capture a static set of values they can work with to debug, then go back to the temp tables.

AielloJ
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
They are doing user acceptance testing.  They cannot create a table in the database where the testing is being performed.

Doing it in another database serves no purpose.

Author

Commented:
sql developer debugger does not provide temp table values only package variable values.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I've never used SQL developer.  I have to rely on what I can find in the docs and by searching.

I'll ask another Senior Expert that I believe has used it.  If he knows I'm sure he'll let you know.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I would be surprised if it could.

The debugging works through the dbms_debug api which requires two sessions.  One to run the code, one to hook into it for debugging.

Since your debugging session isn't the same session as the code running you can't see the GTT.

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
and yes, same applies for the DBMS_DEBUG_JDWP  api too
As far as viweing data of temp table like we do in dataset is concerned while debugging in sql its not possible according to me instead there is a way to viwe data into xml format

DECLARE @v XML = (SELECT * FROM <YourTempTableName> FOR XML AUTO)

by doing above step u can view data into xml format while debugging no need to fire select statement as it creates out of memory problem if used in loop
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Amar - this thread has been closed for nearly 5 years.  You're certainly allowed to post, but it doesn't really help the thread to do so - especially since your answer appears to be for T-SQL syntax which is not supported under Oracle.