Home » SQL & PL/SQL » Client Tools » good idea try to write only ANSI SQL? Use online SQL Validator? Is there a local tool? (Oracle 11g and MySQL 5.6)
good idea try to write only ANSI SQL? Use online SQL Validator? Is there a local tool? [message #619326] Mon, 21 July 2014 08:52 Go to next message
rc3d
Messages: 187
Registered: September 2013
Location: Baden-W├╝rttemberg
Senior Member
Hi

Oracle was my first RDBMS. I write (for example) TO_NUMBER/TO_CHAR/TO_DATE instead of ANSI SQL CAST. Simply because I didn't know.

Does it make sense if I will write only ANSI SQL? Even when Oracle only?

I found a Slashdot Story from 2002 with online SQL validator: http://developers.slashdot.org/story/02/02/19/1720246/sql-validator -> http://developer.mimer.com/validator/index.htm

Quote:

Mimer SQL Validator

Ensure that You Maintain Portability

All too often people find themselves locked into a specific DBMS because their SQL code doesn't conform to the SQL standard.

Here at Mimer Information Technology, we pride ourselves on conforming to the SQL standard and we play an active role in the Database Languages standardization group which determines exactly what is SQL standard. For further information, see our specific SQL standard page.

You can be certain that your SQL conforms to SQL standards by using our unique Mimer SQL Validators.

Mimer SQL Validators

Many applications lack portability between different DBMS products due to vendor-specific lock-ins! By using our on-line Mimer SQL Validators, you can easily verify that your SQL statements conform to the SQL standard.

Mimer SQL Validator provides you with vital information on your SQL code. Does it conform to Core SQL? Does it use features outside Core SQL? Is your SQL statement incorrect or incomplete? Mimer SQL Validator gives you the low down on your code and helps you by providing a list of lexical units (tokens) and a suggested correction. Also, use the validator to find out exactly which words are vendor-reserved.

You are welcome to use our validator products below!


The validator looks "shitty" and is only available in the cloud. Is there a local alternative? Maybe even solution from Oracle? In future I need to work with MySQL (even later MariaDB) and Oracle Server.
Re: good idea try to write only ANSI SQL? Use online SQL Validator? Is there a local tool? [message #619327 is a reply to message #619326] Mon, 21 July 2014 09:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
rc3d wrote on Mon, 21 July 2014 19:22

Does it make sense if I will write only ANSI SQL? Even when Oracle only?


It's up to you whether or not to use ANSI syntax. Oracle supports it. It would make a difference to me only if I see any performance difference between the two syntax.

Quote:
Is there a local alternative? Maybe even solution from Oracle?


I don't know and never heard of "Oracle" itself providing any tool Shocked
Re: good idea try to write only ANSI SQL? Use online SQL Validator? Is there a local tool? [message #619331 is a reply to message #619326] Mon, 21 July 2014 10:19 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Does it make sense if I will write only ANSI SQL? Even when Oracle only?


No.
TI_NUMBER, TO_CHAR, TO_DATE are NOT the equivalent to CAST.

Example:
SQL> select cast('10-DEC-2014' as date) from dual;
select cast('10-DEC-2014' as date) from dual
            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_date('10-DEC-2014','DD-MON-YYYY','nls_date_language=american') from dual;
TO_DATE('10-DEC-201
-------------------
10/12/2014 00:00:00


Previous Topic: dbms_output in Toad or SQL Plus
Next Topic: SQL Developer 4 -- DBMS Output not working
Goto Forum:
  


Current Time: Thu Oct 02 03:35:30 CDT 2014

Total time taken to generate the page: 0.08790 seconds