Re: [GENERAL] Oracle buys Innobase

On 10/21/2005 1:58 AM, Jeff Davis wrote:
> SCassidy(at)overlandstorage(dot)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(at)Yahoo(dot)com #