Toad World® Forums

Substitution Variable Bug


#1

#2

This executes fine against my 10g DB using an 11g client. Do you have any other
text around that query that may be affecting it or are you able to reproduce
this when the only text in the Editor is the “select case…”
statement that you posted below? If there is other text surrounding your
statement verify that the statement above is properly terminated and that this
statement is properly terminated. If there are statements that are not properly
terminated then Toad may be sending more to the database than you expect. You
can use SQL Monitor or Spool SQL to verify what Toad is sending.

Michael


#3

Hi

i can reproduce the same case with 11g client and toad 10.1

monitoring via sql monitor shows :


#4

Afternoon all,

I can reproduce this on a 10g database with 9i client as well as on a 9i
databases with a 9i client. (9208 by the way). Toad 10 GA.

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


#5

I tried this and have the same problem.

Here is print screen of message when executed F9

NOTE the bind variable is blank. It looks like the parser sees a colon and does
funny stuff.

The SELECT was the only statement in the editor.

Here is what happens with F5
image001.png


#6

I tried this and have the same problem.

Here is print screen of message when executed F9

NOTE the bind variable is blank. It looks like the parser sees a colon and does
funny stuff.

The SELECT was the only statement in the editor.

Here is what happens with F5
image002.png


#7

Using Toad 10.1.1.8 against a 10.1 client and 10.1 db and running this statement
(only statement in editor) “SELECT CASE WHEN 1 = 1 THEN ‘:’ END FROM dual;” via
F9 popup up the Variables window which was blank showing no variables in the
list. Entering a value and clicking OK gave an “ORA-01036: illegal variable
name/number”. Running via F5 gave the same error.

The good news is doing the same in the 10.5 beta gave no errors and returned “:”
in the grid.

Ed

[TeamT]


#8

I am not sure what\2019s up. I can\2019t reproduce in any version of Toad from
9.1 to the beta excluding 9.5 which I didn\2019t try because I don\2019t have it
installed.

Michael


#9

Correction, I nuked my 10.1 user files and can now reproduce it in 10.1, but not
the beta.

Michael


#10

Hi Michael,

I am not sure what\2019s up. I can\2019t reproduce in any
version of Toad from 9.1 to the beta excluding 9.5 which I
didn\2019t try because I don\2019t have it installed.

Smart Quotes from Word by any chance?

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


#11

(possibly a BG spybots included in each word document)

i have a REAL question (for Oracle 10G Gurus)
Underpaid overworked and overstressed DBA is inserting records to a small table
which constitutes a foreign key for Master
The Master table which references the small table will toss A foreign key
constraint if the small table child record
1)isnt there
2)is inserted or updated but not yet committed
The DBA noticing that he hasnt has food in last 12 hours runs to the local
lunchroom to salvage scraps before scurvy sets in

Client uses Browser to insert brand new master table record which references the
just inserted record (which is not commited)
The client is displayed resource busy and starts jangling the phone of the DBA
who is on his hands and knees looking for crumbs
The DBA returns to his desk to find the client has contacted “world authority”
to complain that he/she/it is seeing resource busy while inserting his record
via JDBC (assume thin type4) acquired connection

What can a conscientious,hardworking (and starving) DBA do to prevent these
(fairly common) resource busy errors?
Martin Gainty


Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.


#12

Yup. I saw your post mentioning that they could be turned off a week or two back, but after 30 seconds of looking for the option(s) in Word I gave up. I hate them, but not enough to devote more time than that to disable them.

Michael


#13

Go home. Eat. Drink. Drink some more. Sleep…
Jason

What can a conscientious,hardworking (and starving) DBA do to prevent these (fairly common) resource busy errors?
Martin Gainty


#14

Morning Martin,

Underpaid overworked and overstressed DBA is inserting
records to a small table which constitutes a foreign key for Master
So, this small table is the PARENT table in the relationship.

The Master table which references the small table will toss
A foreign key constraint if the small table child record
1)isnt there
2)is inserted or updated but not yet committed.
This ‘master’ table is actually the CHILD in the relationship then.
Given that, (1) and (2) ONLY apply if the FK constraint is not deferred.
If the constraint is deferrable and currently deferred, the insert will
not fail until COMMIT time.

The DBA noticing that he hasn’t has food in last 12 hours
runs to the local lunchroom to salvage scraps before scurvy sets in
Yes, we must keep our sustenance levels high!

Client uses Browser to insert brand new master table record
which references the just inserted record (which is not committed)
Which should have failed immediately if the FK constraint is immediate.
I suspect that it may actually be DEFERRED so the insert was allowed to
happen even without seeing the uncommitted row in the ‘small’ PARENT
table.

The client is displayed resource busy and starts jangling
the phone of the DBA who is on his hands and knees looking for crumbs
Not sure about this. Do you mean that an error “resource busy and
acquired with nowait” occurred or the client saw an hour-glass or other
“working …” cursor?

This is interesting, because if the FK constraint is deferred, no delay
should have been seen. At client commit time, an error would have
occurred as the client cannot see the DBAs inserts that are not yet
committed. I won’t mention the fact that the DBA left a transaction
uncommitted while he was fighting the local pigeons for crumbs! :wink:

If the FK is immediate, then the client should have seen an error as
soon as s/he inserted the child row with no parent in the ‘small’ table.

Is it possible that the client’s insert is actually not hanging on the
uncommitted ‘small’ inserts done (and uncommitted) by the DBA and is
actually hanging up on some other problem such as a “potential” PK
duplicate (or other Unique constraint) as follows:

  • User 1 inserts a row with PK=1234 into table. Does not commit yet.
  • User 2, cannot see User 1’s inserted row, and does exactly the same
    insert - PK=1234.

At this point, user two will hang on, if I remember correctly, a mode 6
enqueue (Normal enqueue hangs are mode 3). This remains hanging until
User 1 commits, in which case User 2 gets a constraint violation, or
rolls back in which case, User 2 gats the insert to work.

Causes of this problem are many and varied:

  • Not using a sequence to create the PK column.
  • Using a table to get the next value. Anyone reading the current value
    at the same time, gets the same value.
  • Using “select max(pk) + 1” to get the next PK value.
  • For CHAR based PKs, somehow managing to generate the same PK value.
  • etc.

The DBA returns to his desk to find the client has contacted
“world authority” to complain that he/she/it is seeing
resource busy while inserting his record via JDBC (assume
thin type4) acquired connection
Users, always in a rush!

What can a conscientious, hardworking (and starving) DBA do
to prevent these (fairly common) resource busy errors?
Martin Gainty
The following:

  • Check the deferability of the FK constraint;
  • Are the FK columns indexed in the CHILD (master) table?
  • Check how the master table PK columns (or other Uniquely constrained
    columns) have their data generated;
  • Run a trace against the client session showing waits (and binds too,
    might be useful) and analyse the trace file.
  • Don’t leave uncommitted transactions lying around!
  • V$SESSION_WAIT.
  • BLOCKERS & WAITERS scripts. (Or in Toad!)
  • Toad’s session browser, current statement, Waits tab. See what the
    client session is actually waiting on.
  • Etc.

Good hunting.

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


#15

The good news is doing the same in the 10.5 beta gave no errors and returned “:”
in the grid.

Yay!

In that case I’m more than happy!

Thanks.