Re: [GENERAL] Oracle buys Innobase

Jan Wieck wrote:
> 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.
>

Interesting.

> 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.
>

From docs 4.1.2.1:

--------------------------
Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written in one constant. For example:

SELECT 'foo'
'bar';

is equivalent to

SELECT 'foobar';

but

SELECT 'foo' 'bar';

is not valid syntax. (This slightly bizarre behavior is specified by
SQL; PostgreSQL is following the standard.)
--------------------------

Is that what you're talking about? Is that section incorrect, or did I
misunderstand you?

Regards,
Jeff Davis