Toad World® Forums

RE: SQL query runs fine in TOAD, but not on UNIX (sqlplus) [def ined as DATE]


#1

I noticed (playing with Norm’s suggestion) that if I run using the execute arrow I get one format, but if I run using the execute as script lightning bolt, I get a different format.

select sysdate from dual;
(with execute)
5/26/2010 3:40:29 PM

(with execute as script)
26-MAY-10

Is this a question of differing formats within toad or does the as script option run in a different environment?

Please note: message attached

email.eml (13.7 KB)


#2

Hi Ralph,

I noticed (playing with Norm’s suggestion) that if I run
using the execute arrow I get one format, but if I run using
the execute as script lightning bolt, I get a different format.

F9 (lightning bolt) = run a single statement and as such, uses date
formats specified in your Toad Options.
F5 = run as script = Try to be as identical to SQL*Plus as possible =
use NLS_DATE_FORMAT for the default format.

HTH

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


#3

Ah, DATEs and the default date format, one of my favourites!

Yes… a fun one to work with indeed. I seem to recall in the distant hazy
past that your desktop date format setting can actually interfere with that as
well… I think it was when someone was dealing with an Access query to an
Oracle database but can’t remember the exact scenario.

The basic rule with DATEs is simple, never assume a default

date format will be what you think it will be!

Excellent rule to live by, one I follow myself.

However, don’t forget that if you apply any functions to the

operands on the left side of the comparison operator, you will

prevent the use of any indices that may be defined

Ahh… now that one I’m going to have to disagree with Norm on…
but only facetiously. Believe it or not I’ve actually worked with people
who coded like…

WHERE TO_DATE(’25.05.2010’, ‘dd.mm.yyyy’)

So… one person’s left might just be another person’s right :wink:

Of course, to be less facetious, Norm’s advice is sound. Never apply a
function to a column unless the index built for that column was built with the
function applied.

Roger S.


#4

Morning Roger,

Ahh… now that one I’m going to have to disagree with Norm
on… but only facetiously. Believe it or not I’ve actually
worked with people who coded like…

WHERE TO_DATE(‘25.05.2010’, ‘dd.mm.yyyy’)
Now that’s quite spooky! I’ve never come across anyone who codes “left
handed” like this in the past. How strange. To me it’s “natural” to
specify the column first (on the left) and then the value on the right.

So… one person’s left might just be another person’s right :wink:
And in my case, sometimes my left is my right and vice versa!

(I’d give my left arm to be ambidextrous!)

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

Having given up your left arm you could still be at least dexterous –
since in essence dexterous refers to the right arm. :wink:

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg


#6

since in essence dexterous refers to the right arm

Ok, I’ll admit… I actually had to look that up. Strange that
ambidextrous is defined as “equally skilled in both hands” while
dexterous is defined as “skilled in the right hand”.

I’ve always viewed dexterous as “skilled in one hand”. Ah
well… I think this is one of those cases I’ll be a rebel and depart
from the norm and keep my historical definition in place :wink:

To me it’s “natural” to specify the column first (on the

left) and then the value on the right.

Me as well.

I hadn’t considered it before, but perhaps it has to do with social
upbringing. English is commonly written left to right, top to bottom, math tends
to be done the same way – x = 5. Assignment in computer languages also
matches that (probably no surprise there considering most computer technology
origins).

Perhaps the background of the individual (it was a while back) was of a more
Asian or Middle-East upbringing where the languages are structured differently.

Roger S.


#7

since in essence dexterous refers to the right arm

Ok, I’ll admit… I actually had to look that up. Strange that
ambidextrous is defined as “equally skilled in both hands” while
dexterous is defined as “skilled in the right hand”.

I’ve always viewed dexterous as “skilled in one hand”. Ah
well… I think this is one of those cases I’ll be a rebel and depart
from the norm and keep my historical definition in place :wink:

To me it’s “natural” to specify the column first (on the

left) and then the value on the right.

Me as well.

I hadn’t considered it before, but perhaps it has to do with social
upbringing. English is commonly written left to right, top to bottom, math tends
to be done the same way – x = 5. Assignment in computer languages also
matches that (probably no surprise there considering most computer technology
origins).

Perhaps the background of the individual (it was a while back) was of a more
Asian or Middle-East upbringing where the languages are structured differently.

Roger S.


#8

In Latin, “dexter” means right and “sinister” means
left. Since most people are right handed and are thus more skilled in their
right hands than their left, “dexter” then became associated with
being skilled with the hands, i.e. dexterous. “Ambidextrous”
essentially means having two right hands, i.e. having two skilled hands.

The association of “sinister” with being evil, I’m told, comes
from assassins. Question: why is shaking hands considered a gesture of
friendship? Because you’re showing that you’re not carrying a weapon
in your right hand. But a left handed person can perfectly well shake hands with
the right hand while holding a dagger in the left. So being left handed, or
being “sinister” handed, became associated with being nasty, and the
word “sinister” picked up that association.

Nate Schroeder (lefty)


#9

Actually the Latin for left is ”sinistra”

So to the left was “a sinistra”.

From Answer.com – here is some extra history:

There are almost a half dozen English language words that include ‘left’ in
their meaning, and that trace their origins back to the ancient, classical
Latin. One example is the adjective ‘sinister’. The word is defined as ‘evil or
suggestive of evil’. But it also has a meaning of ‘leftwardness’. For the
‘sinister events’ on a family’s heraldic shield are found on the left side. And
the word ‘sinister’ indeed tracks back to the Old French ‘sinistre’, and on
further to the earlier source in the Latin ‘sinister’, or ‘left-hand’.

Likewise, the adjective ‘sinistral’ indicates the left side is uppermost in
flatfish, and whorls go leftward in spiral shells. And it tracks back to the Old
French or Medieval Latin ‘sinistralis’, which means ‘of or relating to the left
hand’.

Additionally, the adjective ‘sinistorsal’ and the noun ‘sinistrorse’ find their
origins in the Latin ‘sinistrorsus’, which refers to a ‘twining spirally upward
to the left’.


#10

Well, if you want to get technical, the Latin word is an adjective, which means
its ending changes to match the gender, number, and case of the noun that it is
modifying. If I recall correctly, sinister would be the masculine singular
nominative; sinistra would be feminine singular nominative (or neuter plural
nominative); and other endings would be used for other combinations.

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

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

314-694-2592