Link to home
Start Free TrialLog in
Avatar of gram77
gram77Flag for India

asked on

Viewing data in temporary table.

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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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?
Avatar of gram77

ASKER

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
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.
- 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.
Avatar of gram77

ASKER

can't use debug statements as i have only read access in uat
- try open up a sql query window then query the temporary table while the code execution freezes. see if that works.
Avatar of gram77

ASKER

sql window will be a new session
ASKER CERTIFIED SOLUTION
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Aiello,

Read a few posts back.  They have read only access.
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
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.
Avatar of gram77

ASKER

sql developer debugger does not provide temp table values only package variable values.
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.
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.

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
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.