Home » SQL & PL/SQL » SQL & PL/SQL » Can not see data in GTT although in same session (10G and TOAD)
icon8.gif  Can not see data in GTT although in same session [message #317231] Wed, 30 April 2008 02:48 Go to next message
vinaycdm
Messages: 7
Registered: February 2008
Junior Member
Hi all,

There are some GTT tables used in stored procedures (SP) where they are populated initially and then data is moved from them to main table.

While debugging I want to view the data in this Temp table.
For this after the data is inserted in to GTT table.I execute a select statement in sql editor in the same session but i don't see any data at all.

This GTT table is constructed with

ON COMMIT PRESERVE ROWS
NOCACHE;

I know they are session-specific but even though i try to retrieve data well within the session, why do i see no data in this GTT?

SP works fine and uses and moves the data from GTT to main table.
Re: Can not see data in GTT although in same session [message #317235 is a reply to message #317231] Wed, 30 April 2008 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without any useful information to work on, I'd guess that your stored procedure is clearing down the temporary table after it has finished with it.

If you could show us some code, or an example demonstrating your problem, it'd be a great help.
Re: Can not see data in GTT although in same session [message #317241 is a reply to message #317235] Wed, 30 April 2008 03:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You're not using a web-based client-side tool that uses connection pooling are you?

If you were to do this in Application Express SQL Workshop, each separately executed SQL would grab a separate session.

Ross Leishman
Re: Can not see data in GTT although in same session [message #317352 is a reply to message #317231] Wed, 30 April 2008 16:28 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
I had a similar bad experience once, one particular web page
was not loading at all, cause in the package.function from which we were sending the data to the middle layer had a GTT, on commit
delete rows..

and somebody placed a test insert into it, and committed for
testing purpose, and forgot to comment / remove it...

After wasting about 30 mins of time exploring other things,we
finally figured this out...


which actually prompts to ask this question:

Is a GTT tailored for reporting purposes at all times?
My understanding is, using GTT improves performance as multiple
users can request for the same report using different parameters,
without any concurrency issues...


But, in what situations should a ref cursor be preferred (i am talking about reporting) ?
and in what situations should I go for a GTT whenever it comes
to reporting ?


Re: Can not see data in GTT although in same session [message #317395 is a reply to message #317352] Thu, 01 May 2008 00:48 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My understanding is, using GTT improves performance as multiple
users can request for the same report using different parameters,
without any concurrency issues...

This is wrong, as GTT are session independent I don't see how they can improve performances across users.

Quote:
Is a GTT tailored for reporting purposes at all times?

GTT is seldom the answer. It depends on what is your report. You use GTT when you can't use direct SQL on base tables.

Regards
Michel
Previous Topic: Exiting an SQL Application
Next Topic: Running Executable file through dbms_scheduler job
Goto Forum:
  


Current Time: Tue Dec 06 04:29:00 CST 2016

Total time taken to generate the page: 0.07550 seconds