Hi everyone, I'm very stumped by this and appreciate any help you can give.
I'm using Toad version 14.2.104.1069. My database is Oracle 19c. I have a view that I know has data in it. When I select from the view using SQLPLUS, it returns data. But when I select from the view using Toad or SQL Developer, it returns no data. There was a suggestion on Oracle's forum that said "Change the LANGUAGE part of NLS_LANG such that USERENV('LANG') returns the same for SQL Developer and SQLPlus.", but I have checked that, and my language is set to US in both Toad and SQLPlus.
Any ideas what could be causing this? I'm using the simplest of queries, "select * from [view];"
Thank you, John. Yes, I am 100% certain it is the same database, as we only have one database, and one instance. I also know it is the correct schema, as I can query tables without trouble. It is only views that have this issue.
I took your suggestion and tried querying other views to see if they also had the same issue. Oddly, I can query some views in Toad without a problem, but not others. One big clue for me was one view returns rows in SQLPlus, but in Toad, Oracle throws the error "[Error] Execution (10:1): ORA-01843: not a valid month". So what on earth could cause a view to return normal rows when queried in SQLPlus, but throw that error when queried in Toad? Is there some setting in Toad that affects the way it processes view definitions in some way that is different from SQL Plus?
I did as you suggested and took the view definition and pasted it, and ran it. When I run it in SQLPlus, I get the same result- just rows returned. When I run it in Toad, I get the same error, "not a valid month"
I think I've figured out the root cause, but I don't know how to fix it. In the view, dates are defined as 'MM/DD/YYYY', for example, the date '01/01/1900' appears. SQLPlus accepts this date format as default. However, my Toad has this format as default: '01-JAN-00'. How can I set Toad to use the same default date format as SQLPlus?
Are you doing something like this?
select blah
from table
where date_column = '07-17-2024'
If so, you need to use TO_DATE and specify your date format. Something like:
select blah
from table
where date_column = to_date('07-17-2024', 'MM-DD-YYYY')
You can of course use whatever date format you want but if you don't specify, you are asking for trouble.
So I have hundreds of legacy views that were written prior to my working here, some of which have thousands of lines in their definitions. I cannot go through them all and change them to say TO_DATE everywhere there is a date. Even if I could, I can only change the Dev environment, so I'm stuck with the date format that the views are defined as in Prod. So I need a way to make Toad able to process and display these views with the current date formats that they are defined with. SQLPlus is doing it, so there must be a way for Toad to do it (I dearly hope).
I'm sure it's a lot of work to fix all that code, but it's a really bad practice to not use TO_DATE. If you fix it in your code, it won't matter what the NLS_DATE_FORMAT of the application that happens to run it is. By not fixing it, you are counting on these applications to have the format that you expect. That's Toad and anything else.
The newest version of Toad has a really easy way to set NLS_DATE_FORMAT (or any other ALTER SESSION setting). You can use wildcards in there for User/database if you want and make the date format whatever you want.
But...that option does not exist in version 14.2.
In 14.2 your best bet is to manually run the alter session command when you need it.
For example: ALTER SESSION SET NLS_DATE_FORMAT = 'mm/dd/yyyy';
Be sure you have these options checked/unchecked as shown. Otherwise, your view may run in a different background session than where you ran the alter session command.
By the way, you don't need to use TO_DATE everywhere you reference date fields.
Only the ones where you convert strings to dates.
For instance this, does not need a TO_DATE
Select *
from table_1 t1, table_2, t2
where t1.date_column = t2.date_column;
But this does:
Select *
from table_1 t1, table_2, t2
where t1.date_column = t2.date_column
and t1.date_column = to_date('07-17-2024', 'MM-DD-YYYY')
Unfortunately, whether or not something is bad practice is irrelevant, as I cannot change Production view definitions because I do not have access to Production. Sometimes we have to deal with code that was given to us which we cannot alter, so we have to find other solutions.
I have thought of setting NLS_DATE_FORMAT manually for my session every time I log in, but that is also frustrating. Since SQLPlus is not changing NLS_DATE_FORMAT (it has it set to DD-MON-RR), but is still able to read from the views, I was hoping Toad could find a way to also read from those views without altering NLS_DATE_FORMAT. It's odd to me that SQLPlus can achieve something that Toad seems unable to.
Thank you for all your help today. You definitely pointed me in the right direction. The key was to dig into the view definitions and find the specific lines that Toad is unable to read correctly. Thank you!
Try this:
Put your ALTER SESSION command in a file (with a semicolon and line feed after it)
Then go to Options -> Startup.
Select your file under "File to execute on new connections"
That will cause the file to be loaded into the editor and run whenever you make a connection.
It's not as convenient as what we have in the latest version but it's better than nothing.
Thank you, that sounds really neat! I'm sure I can come up with lots of other startup commands I'd like to run, too.
The thing is, I'm not clear on why Toad requires me to alter NLS_DATE_FORMAT, when SQLPlus doesn't. SQLPlus really is using NLS_DATE_FORMAT set to DD-MON-RR, just like Toad is. I feel like there must be something else?
I'm not clear on why Toad requires me to alter NLS_DATE_FORMAT, when SQLPlus doesn't. SQLPlus really is using NLS_DATE_FORMAT set to DD-MON-RR, just like Toad is. I feel like there must be something else?
I see the same thing in SQL*Plus as I do in Toad
And if I use the expected date format, both work:
Do you have a login.sql or glogin.sql file that sets your date format in SQLPlus?
glogin.sql is in your Oracle Client's SQLPlus\admin folder.
For me: C:\Oracle\FullClients\x86\12.2\product\12.2.0\client_1\sqlplus\admin\glogin.sql
login.sql, if it exists, lives in a folder defined by a SQLPATH environment variable.
If it finds either of these files, SQLPlus will run them immediately when you make a connection. That could explain why SQL*Plus is behaving differently than Toad here.
