Re: [GENERAL] Oracle buys Innobase - Mailing list pgsql-advocacy

On 10/21/2005 1:58 AM, Jeff Davis wrote:
> SCassidy@overlandstorage.com wrote:
>> A "PostgreSQL to Oracle converter" might be a really big project.
>>
>> Having ported an application from PostgreSQL (7.3) to Oracle 9i, as I
>> recall, my biggest problems were:
>>
>> -  Quoting issues:  the original PostgreSQL application quoted
>> integer/numeric type, and Oracle will not allow that, so I had to make sure
>> only types that had to be quoted, were.  (I normally do not quote numbers
>> in PostgreSQL, but I inherited the application).
>
> What does the standard say about that? Which types of values in Oracle
> need to not be quoted?

Section 5.3 of SQL92 defines:

      <literal> ::=
             <signed numeric literal>
           | <general literal>

      <unsigned literal> ::=
             <unsigned numeric literal>
           | <general literal>

      <general literal> ::=
             <character string literal>
           | <national character string literal>
           | <bit string literal>
           | <hex string literal>
           | <datetime literal>
           | <interval literal>

The Restrictions for Entry level SQL say that

         a) A <general literal> shall not be a <national character string
           literal>.

         b) A <general literal> shall not be a <datetime literal> or
           <interval literal>.

There are no restrictions that say a <general literal> cannot be a
<numeric literal>. <numeric literal> is defined without quotes, but from
the above I conclude that a <general literal> is allowed instead of a
<numeric literal> to represent a numeric value, which means Oracle is
wrong IMNSVHO.

No reason to swell your breast though, we aren't perfect in the literal
area either. Because

      <datetime literal> ::=
             <date literal>
           | <time literal>
           | <timestamp literal>

      <date literal> ::=
           DATE <date string>

      <time literal> ::=
           TIME <time string>

      <timestamp literal> ::=
           TIMESTAMP <timestamp string>

      <date string> ::=
           <quote> <date value> <quote>

      ...

We allow a <general literal> to be used as a <datetime literal> or
<interval literal> without the keyword DATE, TIME, TIMESTAMP or
INTERVAL. Isn't that violating the restrictions for Entry level SQL?

Further

      1) In a <character string literal> or <national character string
         literal>, the sequence:

           <quote> <character representation>... <quote>
           <separator>... <quote> <character representation>... <quote>

          is equivalent to the sequence

           <quote> <character representation>...  <character
           representation>... <quote>

         Note: The <character representation>s in the equivalent se-
         quence are in the same sequence and relative sequence as in the
         original <character string literal>.

The rule is more or less repeated for other literal types. But 8.0.4 claims

     wieck=# select 'foo ' 'bar';
     ERROR:  syntax error at or near "'bar'" at character 15
     LINE 1: select 'foo ' 'bar';
                           ^

I guess neither Oracle nor we get the cookie here.


Jan

>
> Regards,
>     Jeff Davis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #