Toad World® Forums

Saving data in 12.1.0.3 is a problem

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

Are you saying that the database DOES contain data, or that you don’t want to see the message?

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Friday, July 19, 2013 8:58 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Saving data in 12.1.0.3 is a
problem

Thread created by wimdelange_062

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

The result of the query contains data. But if the “display all results in grid” is off, Toad thinks there is no data to export. Which indicates two problems. 1) There is data, but it won’t export. 2) And if there was no data, it should export and not give an error. The fact that a report is empty should not lead to an error. Maybe I’m interested in a field list with no records in the selected format.

But for starters, the fact that “Display results in grid” is off should not lead to the fact that Toad thinks there is no date, while clearly there is.

Groetjes,
Wim

2013/7/19 John Dorlon bounce-jdorlon@toadworld.com

RE: Saving data in 12.1.0.3 is a problem

Reply by John Dorlon
Are you saying that the database DOES contain data, or that you don’t want to see the message?

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Friday, July 19, 2013 8:58 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Saving data in 12.1.0.3 is a
problem

Thread created by wimdelange_062

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

PLUS – one should always set
“display all results in grid” to off for efficiency/performance reasons. So if this is not working, then it’s a best practice that doesn’t work
L

Bert

Hi Wim,

I think this is what happened:

You had the “threaded queries” option enabled, which runs DML and selects in the editor in their own session.

You had some freshly inserted, but not committed rows in your dataset

When you exported with ‘display all results in grid’ unchecked, Toad executed a separate (nonthreaded, not in the same session as the editor) query
to pull data….but since it was uncommitted, well, you know the rest…

I should be able to make it use the same session as the threaded query. For now, do a commit first and it should work.

-John

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Saturday, July 20, 2013 2:50 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re: Saving data in
12.1.0.3 is a problem

Reply by wimdelange_062

The result of the query contains data. But if the “display all results in grid” is off, Toad thinks there is no data to export. Which indicates two problems. 1) There is data,
but it won’t export. 2) And if there was no data, it should export and not give an error. The fact that a report is empty should not lead to an error. Maybe I’m interested in a field list with no records in the selected format.

But for starters, the fact that “Display results in grid” is off should not lead to the fact that Toad thinks there is no date, while clearly there is.

Groetjes,

Wim

2013/7/19 John Dorlon bounce-jdorlon@toadworld.com

RE:
Saving data in 12.1.0.3 is a problem

Reply by John Dorlon

Are you saying that the database DOES contain data, or that you don’t want to see the message?

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Friday, July 19, 2013 8:58 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Saving
data in 12.1.0.3 is a problem

Thread created by wimdelange_062

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Sorry, but that is not a correct description of the situation.
I have the “threaded queries” option enabled (who hasn’t :-)?).

But I don’t have freshly inserted rows.

I start Toad, run the query that get me the totals of the general ledger of the previous month, I see some date on the screen (Row 1 of 500 fetched so far (more rows exists)) and then do the save to Excel (xlsx) with the 'Display all results in grid" off, giving the following result:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

Let me know if you want more information (Toad settings, Oracle versions, etc).

Did some further testing. I cannot produce this error in another Oracle database? So there is a dependency somewhere.

What fails is Oracle 10, but Oracle 9 is OK. I don’t have a clue what the difference is between the users I use to access the database in both systems. What type of information can you use?

Groetjes,
Wim

2013/7/22 John Dorlon bounce-jdorlon@toadworld.com

RE: Saving data in 12.1.0.3 is a problem

Reply by John Dorlon
Hi Wim,

I think this is what happened:

You had the “threaded queries” option enabled, which runs DML and selects in the editor in their own session.

You had some freshly inserted, but not committed rows in your dataset

When you exported with ‘display all results in grid’ unchecked, Toad executed a separate (nonthreaded, not in the same session as the editor) query
to pull data….but since it was uncommitted, well, you know the rest…

I should be able to make it use the same session as the threaded query. For now, do a commit first and it should work.

-John

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Saturday, July 20, 2013 2:50 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re: Saving data in
12.1.0.3 is a problem

Reply by wimdelange_062

The result of the query contains data. But if the “display all results in grid” is off, Toad thinks there is no data to export. Which indicates two problems. 1) There is data,
but it won’t export. 2) And if there was no data, it should export and not give an error. The fact that a report is empty should not lead to an error. Maybe I’m interested in a field list with no records in the selected format.

But for starters, the fact that “Display results in grid” is off should not lead to the fact that Toad thinks there is no date, while clearly there is.

Groetjes,

Wim

2013/7/19 John Dorlon bounce-jdorlon@toadworld.com

RE:
Saving data in 12.1.0.3 is a problem

Reply by John Dorlon

Are you saying that the database DOES contain data, or that you don’t want to see the message?

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Friday, July 19, 2013 8:58 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Saving
data in 12.1.0.3 is a problem

Thread created by wimdelange_062

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

I tried to reproduce this on 10.2.0.5 and cannot get your issue. So it has to be some obscure setting I’m guessing. So imagine John will want your toad.ini
at a min – maybe even a zipped copy of your user files directory ……

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Tuesday, July 23, 2013 3:06 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re: Saving data in
12.1.0.3 is a problem

Reply by wimdelange_062

Sorry, but that is not a correct description of the situation.

I have the “threaded queries” option enabled (who hasn’t :-)?).

But I don’t have freshly inserted rows.

I start Toad, run the query that get me the totals of the general ledger of the previous month, I see some date on the screen (Row 1 of 500 fetched so far (more rows exists))
and then do the save to Excel (xlsx) with the 'Display all results in grid" off, giving the following result:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

Let me know if you want more information (Toad settings, Oracle versions, etc).

Did some further testing. I cannot produce this error in another Oracle database? So there is a dependency somewhere.

What fails is Oracle 10, but Oracle 9 is OK. I don’t have a clue what the difference is between the users I use to access the database in both systems. What type of information
can you use?

Groetjes,

Wim

2013/7/22 John Dorlon bounce-jdorlon@toadworld.com

RE:
Saving data in 12.1.0.3 is a problem

Reply by John Dorlon

Hi Wim,

I think this is what happened:

You had the “threaded queries” option enabled, which runs DML and selects in the editor in their own session.

You had some freshly inserted, but not committed rows in your dataset

When you exported with ‘display all results in grid’ unchecked, Toad executed a separate (nonthreaded, not in the same session as the editor) query to pull data….but since
it was uncommitted, well, you know the rest…

I should be able to make it use the same session as the threaded query. For now, do a commit first and it should work.

-John

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Saturday, July 20, 2013 2:50 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re:
Saving data in 12.1.0.3 is a problem

Reply by wimdelange_062

The result of the query contains data. But if the “display all results in grid” is off, Toad thinks there is no data to export. Which indicates two problems. 1) There is data,
but it won’t export. 2) And if there was no data, it should export and not give an error. The fact that a report is empty should not lead to an error. Maybe I’m interested in a field list with no records in the selected format.

But for starters, the fact that “Display results in grid” is off should not lead to the fact that Toad thinks there is no date, while clearly there is.

Groetjes,

Wim

2013/7/19 John Dorlon bounce-jdorlon@toadworld.com

RE:
Saving data in 12.1.0.3 is a problem

Reply by John Dorlon

Are you saying that the database DOES contain data, or that you don’t want to see the message?

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Friday, July 19, 2013 8:58 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Saving
data in 12.1.0.3 is a problem

Thread created by wimdelange_062

When I want to use to save the data grid in 12.1.0.3 I get the following error:

[Window Title]

Warning

[Main Instruction]

Toad Message

[Content]

The dataset does not contain data.

[OK]

This error goes away if you check the box with “Display all results in grid”.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Could there be some other reason why a different session wouldn’t see the rows? It IS a different session when threaded queries is enabled. I have fixed
that for next beta…which should be today. Please try it again when that becomes available.

It’s interesting to hear that it only happens on your 10g database. I don’t think your user files folder will reveal anything to me.

Afternoon all,
On 23/07/13 14:15, John Dorlon wrote:

*RE: Saving data in 12.1.0.3 is a problem
*

Reply by John Dorlon
Could there be some other reason why a different session wouldn’t see
the rows? It IS a different session when threaded queries is
enabled.
There is a possibility that the Editor session - filling the grid with data to be saved - might not be able to see [some] data if that missing data was:

  • Added or changed by other session(s) and committed; AND
  • Added or changed after the start of the transaction for the statement filling the grid; AND
  • The grid session executed a “set transaction read only;” statement; AND
  • The grid session is not SYS.
    (Phew!)
    That doesn’t mean that there cannot be inserts or updates etc, merely that the grid session will not see any newly committed data after the execution of the above "set transaction"command. It has also to be the very first statement in the transaction, so something like the following in the grid session:
    connect norman/secret

    commit;
    set transaction read only;
    select stuff for grid;

    commit;
    So, the first commit ends the transaction and begins a new one. The SET TRANSACTION makes this transaction unable to see new data added by other sessions after the start of this transaction, even if the data are committed. The final commit ends the read only (a bad name if ever there was one!) transaction.
    Example:
    Session 1: select * from norm;
    a

616
Session 1: set transaction read only;
Transaction set.
Session 1: select * from norm;
a

616
Session 2: insert into norm values (666);
Session 2: commit;
Session 2: select * from norm;
a

616
666
Session 1: select * from norm;
a

616
So, session 1 still cannot see any new data, inserted or updated, and committed, since the start of it’s current transaction.
If we continue in session 1:
Session 1: commit;
Commit complete.
Session 1: select * from norm;
a

On 23/07/13 15:30, Norm [TeamT] wrote:
A seemingly truncated email! It was fine when it left me, honest!
The bits missing are as follows:
If we continue in session 1:
Session 1: commit;
Commit complete.
Session 1: select * from norm;
a

Well now, something odd is happening! I’ll try again!
If we continue in session 1:
Session 1: commit;
Commit complete.
Session 1: select * from norm;
a

616
666
The above is the only way that I can think of to prevent one session from seeing data that were changed or added, and committed, by another session.
The big problem with the above is, this isn’t happening in the “other” session that is fetching the data to write out to the disc file, Excel or whatever! But is is a way in which one transaction can be prevented from seeing committed data.
Other than this, and the obvious reasons given by Bert, I can’t think of any reason why the background session in Toad, wanting to export the data, is not able to see the committed data visible to the editor session that filled the grid.
– Cheers,
Norm. [TeamT]
– Cheers,
Norm. [TeamT]

Problem is still there in 12.1.0.5. The following query on a table (not a view) generates 1083 records (year - 2013, period - 2)

SELECT company

,accounting_year

,accounting_period

,account

,SUM(amount_balance) total_amount

FROM accounting_balance_tab

WHERE accounting_year = :year

AND accounting_period = :period

GROUP BY company

,accounting_year

,accounting_period

,account

This creates a spreadsheet with the records

But if Display all results in grid is checked off, the following happens, even when I don’t redo the query. So there is data. And there are no ongoing transactions.

And as stated before, this happens for this user and this database. I cannot reproduce this in another environment.

Groetjes,
Wim

2013/7/23 Norm [TeamT] bounce-NormTeamT@toadworld.com

Re: Saving data in 12.1.0.3 is a problem

Reply by Norm [TeamT]

Well now, something odd is happening! I’ll try again!

If we continue in session 1:

Session 1: commit;

Commit complete.

Session 1: select * from norm;

a

=====

616

666

The above is the only way that I can think of to prevent one session

from seeing data that were changed or added, and committed, by another

session.

The big problem with the above is, this isn’t happening in the “other”

session that is fetching the data to write out to the disc file, Excel

or whatever! But is is a way in which one transaction can be prevented

from seeing committed data.

Other than this, and the obvious reasons given by Bert, I can’t think of

any reason why the background session in Toad, wanting to export the

data, is not able to see the committed data visible to the editor

session that filled the grid.

Cheers,

Norm. [TeamT]

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

John is on vacation – and he normally handles these issues. So you may need to wait a week or two for him to get back and caught up ….

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Tuesday, July 30, 2013 6:46 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re: Saving data in
12.1.0.3 is a problem

Reply by wimdelange_062

Problem is still there in 12.1.0.5. The following query on a table (not a view) generates 1083 records (year - 2013, period - 2)

SELECT company

    ,accounting_year

    ,accounting_period

    ,account

    ,SUM(amount_balance) total_amount

FROM accounting_balance_tab

WHERE accounting_year = :year

     AND accounting_period = :period

GROUP BY company

    ,accounting_year

    ,accounting_period

    ,account

This creates a spreadsheet with the records

Inline afbeelding 1

But if Display all results in grid is checked off, the following happens, even when I don't redo the query. So there is data. And there are no ongoing transactions.

Inline afbeelding 2

And as stated before, this happens for this user and this database. I cannot reproduce this in another environment.

Groetjes,

Wim

2013/7/23 Norm [TeamT] bounce-NormTeamT@toadworld.com

Re:
Saving data in 12.1.0.3 is a problem

Reply by Norm [TeamT]

Well now, something odd is happening! I'll try again!

If we continue in session 1:

Session 1: commit;

Commit complete.

Session 1: select * from norm;

a

=====

616

666

The above is the only way that I can think of to prevent one session

from seeing data that were changed or added, and committed, by another

session.

The big problem with the above is, this isn't happening in the "other"

session that is fetching the data to write out to the disc file, Excel

or whatever! But is is a way in which one transaction can be prevented

from seeing committed data.

Other than this, and the obvious reasons given by Bert, I can't think of

any reason why the background session in Toad, wanting to export the

data, is not able to see the committed data visible to the editor

session that filled the grid.

--

Cheers,

Norm. [TeamT]

--

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Afternoon Wim,
On 30/07/13 12:51, Bert Scalzo wrote:

Reply by Bert Scalzo
John is on vacation – and he normally handles these issues. So you may
need to wait a week or two for him to get back and caught up ….
Do me a favour please. Can you turn SQL Spooling on from the point where you are just about to execute your own query to populate the grid in Toad, then run it as normal and as you described, to create an Excel spreadsheet.
Use all the options that you mentioned that prevent you getting data into Excel please.
That way, we might be able to see something that is amiss in the spooled output. Assuming that it’s a database problem of course!
Just trying to think of something that might help. If Toad’s own spooling doesn’t help, I’m going to suggest going down the road of a 10046 level 12 trace aka DBMS_SUPPORT.START_TRACE_IN_SESSION aka DBMS_MONITOR.SESSION_TRACE_ENABLE.
Using Toad’s own spooling will maybe show what’s going on/wrong.
Cheers,
Norm.
– Cheers,
Norm. [TeamT]

To be clear, this has nothing to do with multiple session or threading. The data in period 2013-02 is not changing (period 2013-02 is closed in the ledger). I’ve switched off the threaded execution in Toad and still the problem is there. It is only the check box together with something in this user that Toad decides not to write the information.

Did some more testing. If I use another table and use rownum to limit the number of records (499, 600, 1083), there is never a problem.

But if the query is run against another table and the result should contain the same data (accounting information against general ledger and result is the same) it generates the error. So there is something really strange going on.

The structure of the query plays a role? But how?

Groetjes,
Wim

2013/7/23 Norm [TeamT] bounce-NormTeamT@toadworld.com

Re: Saving data in 12.1.0.3 is a problem

Reply by Norm [TeamT]

Well now, something odd is happening! I’ll try again!

If we continue in session 1:

Session 1: commit;

Commit complete.

Session 1: select * from norm;

a

=====

616

666

The above is the only way that I can think of to prevent one session

from seeing data that were changed or added, and committed, by another

session.

The big problem with the above is, this isn’t happening in the “other”

session that is fetching the data to write out to the disc file, Excel

or whatever! But is is a way in which one transaction can be prevented

from seeing committed data.

Other than this, and the obvious reasons given by Bert, I can’t think of

any reason why the background session in Toad, wanting to export the

data, is not able to see the committed data visible to the editor

session that filled the grid.

Cheers,

Norm. [TeamT]

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

Did some extra testing.
The problem is not there in toad 11.6.043 but it is also existing in Toad 12.0.0.61. All 64 bits. Same query, same export.

Groetjes,
Wim

2013/7/30 Wim de Lange wimdelange@gmail.com

To be clear, this has nothing to do with multiple session or threading. The data in period 2013-02 is not changing (period 2013-02 is closed in the ledger). I’ve switched off the threaded execution in Toad and still the problem is there. It is only the check box together with something in this user that Toad decides not to write the information.

Did some more testing. If I use another table and use rownum to limit the number of records (499, 600, 1083), there is never a problem.

But if the query is run against another table and the result should contain the same data (accounting information against general ledger and result is the same) it generates the error. So there is something really strange going on.

The structure of the query plays a role? But how?

Groetjes,
Wim

2013/7/23 Norm [TeamT] bounce-NormTeamT@toadworld.com

Re: Saving data in 12.1.0.3 is a problem

Reply by Norm [TeamT]

Well now, something odd is happening! I’ll try again!

If we continue in session 1:

Session 1: commit;

Commit complete.

Session 1: select * from norm;

a

=====

616

666

The above is the only way that I can think of to prevent one session

from seeing data that were changed or added, and committed, by another

session.

The big problem with the above is, this isn’t happening in the “other”

session that is fetching the data to write out to the disc file, Excel

or whatever! But is is a way in which one transaction can be prevented

from seeing committed data.

Other than this, and the obvious reasons given by Bert, I can’t think of

any reason why the background session in Toad, wanting to export the

data, is not able to see the committed data visible to the editor

session that filled the grid.

Cheers,

Norm. [TeamT]

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

The Toad spool file.


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.490

BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.533

BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.620

SELECT company

,accounting_year

,accounting_period

,account

,SUM(amount_balance) total_amount

FROM accounting_balance_tab

WHERE accounting_year = :year

AND accounting_period = :period

GROUP BY company

,accounting_year

,accounting_period

,account

:year(VARCHAR[4],IN)=‘2013’

:period(VARCHAR[2],IN)=‘02’


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:59.185

begin dbms_output.get_line(line => :line, status => :status); end;

:line(LONG,OUT)=

:status(INTEGER,OUT)=


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:59.188

BEGIN DBMS_OUTPUT.DISABLE; END;


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:59.218

BEGIN DBMS_OUTPUT.DISABLE; END;

Dumb question – all those dbms_output calls make me ask – are you using execute (f9) or execute as script (f5) ???

There is a major difference that might explain why you’re having issues ……

From: wimdelange_062 [mailto:bounce-wimdelange_062@toadworld.com]

Sent: Tuesday, July 30, 2013 7:41 AM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] Saving data in 12.1.0.3 is a problem

Re: Saving data in
12.1.0.3 is a problem

Reply by wimdelange_062

The Toad spool file.


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.490

BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.533

BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:58.620

SELECT company

    ,accounting_year

    ,accounting_period

    ,account

    ,SUM(amount_balance) total_amount

FROM accounting_balance_tab

WHERE accounting_year = :year

     AND accounting_period = :period

GROUP BY company

    ,accounting_year

    ,accounting_period

    ,account

:year(VARCHAR[4],IN)=‘2013’

:period(VARCHAR[2],IN)=‘02’


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:59.185

begin dbms_output.get_line(line => :line, status => :status); end;

:line(LONG,OUT)=

:status(INTEGER,OUT)=


Session: IFSAPP@IFS_PROD

Timestamp: 14:30:59.188

BEGIN DBMS_OUTPUT.DISABLE; END;

Hi Wim,
thanks for that.
On 30/07/13 13:40, wimdelange_062 wrote:

The Toad spool file.


Session: IFSAPP@IFS_PROD
Timestamp: 14:30:58.490
BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
DBMS_OUTPUT? I’m confused! Is this part of your SQL that is giving problems or has it appeared from toad itself? Never miknd, I’ve raun a test on Toad 12 myself, it appears that Toad is adding this, even when I execute an SQL with F9 and not with F5. I wonder if this is the cause of the problem?
I have tried this on a Toad 11 system, connecting to the same test database as I used for Toad 12, and when I run that SQL, I only see the SQL statement, there are no DBMS_OUTPUT statements.

Session: IFSAPP@IFS_PROD
Timestamp: 14:30:58.620
SELECT company
,accounting_year
,accounting_period
,account
,SUM(amount_balance) total_amount
FROM accounting_balance_tab
WHERE accounting_year = :year
AND accounting_period = :period
GROUP BY company
,accounting_year
,accounting_period
,account
:year(VARCHAR[4],IN)=‘2013’
:period(VARCHAR[2],IN)=‘02’


On 30/07/13 14:21, Norm [TeamT] wrote:
The following was sent with my last post to this problem/thread, but was missing when the email arrived back here:
BEGIN:
At least we can see that your values are being used.

More DBMS_OUTPUT stuff removed…
I am now wondering if the DBMS_OUTPUT that appears to be running in the background is responsible for your problem? However, I’m unable to get it to fail to create an “export” of the grid - but as my test rig doesn’t have Excel installed, I used CSV to Clipboard instead. Not sure if that affects things or not.
Still, we found a difference!
END.
I think, it’s all those hyphens on the output. or some reason - possibly because two together at the start of a line - indicate end of text/start of sig.
Might be a Thunderbird thing, might be the web converting replies to whatever it sends out. I don;t know, but it’s not the first time this has happened with one of my replies. :frowning:
– Cheers,
Norm. [TeamT]