gram77
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?
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?
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
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.
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.
ASKER
sql window will be a new session
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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 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.
Doing it in another database serves no purpose.
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'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.
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
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.
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?