Strange Behavior in Toad 9.7.2.5

Yes – that’s what I was thinking – because not sure what
appeared in editor meant J

By Editor I mean the top portion where you edit the code.

The output also showed the code.

This is very hard to believe without actual screenshots to prove it.

I did the following in the 10.1 editor

create table test_20100222 as
select ‘x’ x, ‘y’ y from dual
union all
select ‘a’, ‘d’ from dual
union all
select ‘b’, ‘c’ from dual;

the above creates an easily reproducible table.

Then I did

select * from test_20100222;

  • this produces a data grid
  • I right clicked in the data grid and chose “export dataset”
  • In the dialogue I chose INSERT statements and clipboard, specified a schema and a table name

I then went into a new editor and did CTRL V

I got the following:

SET DEFINE OFF;
Insert into EROLLA.TEST_20100222
(X, Y)
Values
(‘x’, ‘y’);
Insert into EROLLA.TEST_20100222
(X, Y)
Values
(‘a’, ‘d’);
Insert into EROLLA.TEST_20100222
(X, Y)
Values
(‘b’, ‘c’);
COMMIT;

No strange statements were added. I executed it via the LIGHTENING BOLT and still no strange commands were inserted.

I saved it as a file.
Then I opened it in a new tab.

No strange commends inserted.
I executed it with the lightening bolt.
Still no strange commands inserted.

The only thing I can suggest is screen shots that prove it is happening. There is a Japanese proverb “believe nothing that you hear and only half of what you see”.

She then ran the script. The “BEGIN DBMS_LOCK.SLEEP(1); END;”
appeared in the script between each insert statement.

Question: how did she run the script?

Morning Nate,

They've never heard of using a Sequence for that part of the history
key??? (Or use a Sequence as the entire PK of the history
table, with a non-unique index on other-key-plus-date.)

They've never heard of TIMESTAMP so why would they think of using a
sequence? And if they did, they'd probably write some (java!) code to
create a new sequence for every single ID in the table. Go figure (and I
have see this done!!!!)

However, yesterday, I had a chat with them and the next maintenance
release will indeed use one single sequence to get the key to be unique.
Result!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Norm!

They've never heard of TIMESTAMP so why would they think of using a
sequence? And if they did, they'd probably write some (java!) code to
create a new sequence for every single ID in the table. Go figure (and I
have see this done!!!!)

I'm surprised! Everyone knows that the only way to do this is to have a
single table that holds a row for each of the 700 tables that needs an
identifying key. And if the DBA can't figure out why the database is so
slow, well maybe it's time for a new DBA.

Rich -- [TeamT]

Disclaimer: If you believe this garbage, I've got this bridge for sale...

Hi Rich,

I'm surprised! Everyone knows that the only way to do this
is to have a single table that holds a row for each of the 700 tables

that needs an identifying key.
Please, don't even joke about this method of implementing a "sequence" -
I have been down the road of sorting this particular problem far too
many times for it to be "funny" any longer.

Usually on applications what can, ahem, "run perfectly well on any
database backend you wish". I ran that through translate.google.com,
from English to English, and it came back with "runs incredibly badly on
every database backend you wish"!

And if the DBA can't figure out why the
database
is so slow, well maybe it's time for a new DBA.

Off the top of this particular DBA's head, I would say:

  • Enqueues (mode 3) caused by "select value from table where key =
    'something' for update;" and user has gone for lunch; or

  • Enqueues (mode 6) caused by "select value from table where key =
    'something';" followed by "update table set value = value + 1 where key
    = 'something';" followed by trying to feed the PK with a duplicate value
    because M other sessions have all seen the same "value" but none of the
    "value + 1"s because of Oracle not allowing dirty reads of uncommitted
    data.

I'm pretty certain I got the modes right!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Is this repeatable, or was it a one-time thing?

I’m grasping at unlikely straws here, but could it have been either of
these?

(a) The script originally was

Long long command; begin dbms_lock.sleep(1); end;

Another long long command; begin dbms_lock.sleep(1); end;

Etc.

and you didn’t see the SLEEP commands because they were off the edge of
the screen; but when the script was formatted or autoformatted or when wrap was
turned on, the SLEEP commands appeared?

(b) Someone has programmed a regular expression search-and-replace macro of some
sort on that machine, and somehow she triggered it?

Nate Schroeder

IT Commercial Technical Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167

314-694-2592

I'm surprised! Everyone knows that the only way to do this
is to have a single table that holds a row for each of the 700 tables

that needs an identifying key.
Please, don't even joke about this method of implementing a "sequence" -

Heh, Norm has a point Rich. Some of the less experienced that follow this group
won’t realize you were being facetiously ironic :wink:

Roger S.

I have the following query that returns 296 rows when run in Toad for Oracle
10.1 but returns 0 rows when run in Toad for Data Analysts 2.5

select distinct
c.ReportSequence,
c.Description,
n.INFO1,
n.INFO2,
n.INFO3,
n.INFO4,
o.INFO1,
o.INFO2,
o.INFO3,
o.INFO4
from DATAINFO n
left join DATAINFO o on n.INFOCODE = o.INFOCODE and n.INFO1 = o.INFO1 and
o.HRVSTNUM = ‘10/01/2009’
left join DATAINFOCODES c on n.INFOCODE = c.DATAINFOCODE
where n.HRVSTNUM = ‘01/07/2010’ and n.INFOCODE in (75,76, 77, 78, 86, 88, 79,
89)

Any way to get this to return records in the Analysts flavor? I need to write a
report using this.

Thanks,
Eva

Sounds like either

Is HRVSTNUM defined as a DATE type in the database? If so, your problem may be
comparing a date with a character string relying on the default conversion. If
HRVSTNUM is a DATE, try it with o.HRVSTNUM =
TO_DATE(‘10/01/2009’,’MM/DD/YYYY’) and n.HRVSTNUM =
TO_DATE(‘01/07/2010’, ‘MM/DD/YYYY’) and see if that helps.

Nate Schroeder

IT Commercial Technical Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167

314-694-2592

Hey Roger,

Please, don't even joke about this method of implementing a
"sequence" -

Heh, Norm has a point Rich. Some of the less experienced that follow
this group won't realize you were being facetiously ironic :wink:

Which was the thrust behind the verbiage of the Disclaimer... :slight_smile:

Rich -- [TeamT]

Disclaimer: The only clear choice is ambiguity.

It is defined as text, not a date. It just looks like a date.

I am logged into the same schema, so #2 is out.

Would it be committed in TFO and not in DA? If so, how do I commit it
completely?

It was the committed issue. The data had been inserted and not committed.
Closing the session and reopening it showed none of the inserted rows had ever
been committed.

THANKS!!!

Just a note: we made a choice a while back to set the “auto Commit” option
to OFF by default for Toad for Data Analysts. The thinking there is that Toad
for Data Analysts has a higher likelihood of being used by people who may not
understand the implications of a commit, unlike Toad for Oracle’s typical
users. As much as we’d like to assume that these folks have limited
permissions to the database, I think we all know that’s a scary assumption to
make! So in the interest of safety, the default option requires the user to
commit changes explicitly.

If you would prefer, you can change the default for Oracle connections by
checking the checkbox in the Options page (note: this applies to a new saved
connection in Toad for Data Analysts; existing saved connections need to be
modified individually). This way you won’t have to remember to commit your
changes.

--Daniel

Yeah, I saw that after the fact! Thanks for all the help and I promise to commit myself when appropriate from now on :slight_smile: (pun fully intended)


Yeah, I saw that after the fact! Thanks for all the help and I promise to commit
myself when appropriate from now on :slight_smile: (pun fully intended)

Sorry, TFO. The commit should happen in the session where it was inserted.

AutoCommit should be outlawed if you ask me. I don’t even understand why you
would ever use it when working with Oracle…