Thread: Re: [GENERAL] Oracle buys Innobase
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?
Regards,
Jeff Davis
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 #
I hope it's ok if I jump in here. I am having a difficult time following Jan's logic: JW> The Restrictions for Entry level SQL say that JW> a) A <general literal> shall not be a <national character string JW> literal>. JW> b) A <general literal> shall not be a <datetime literal> or JW> <interval literal>. JW> There are no restrictions that say a <general literal> cannot be a JW> <numeric literal>. I just took a look at my copy of SQL:2003, and I do agree that there is no rule per se stating that "a <general literal>" cannot be a <numeric literal>". However, I also find nothing to indicate that a <general literal> *can* be a <numeric literal>. Looking at the definition for <general literal>, I see: <general literal> ::= <character string literal> | <national character string literal> | <Unicode character string literal> | <binary string literal> | <datetime literal> | <interval literal> | <boolean literal> I see seven possible manifestations of a general literal (<boolean literal>, <interval literal>, and so forth). None of these manifestations has to do with numeric values. I see numeric literals defined as follows: <signed numeric literal> ::= [ <sign> ] <unsigned numeric literal> <unsigned numeric literal> ::= <exact numeric literal> | <approximate numeric literal> And, digging down further, I see no quotes anywhere in the definitions. From all that I have seen so far in the standard, if you write a literal such as, say, '23.5', what you have is a character string. I see no ambiguity on that point. Now, Oracle (and possibly other databases) will allow you to use a character string when a number is called for. For example: SELECT some_number + '23.5' from some_table; In such cases, Oracle implicitly converts the character string, but implicit conversion is potentially a can of worms and I don't think it's really the issue you are talking about, is it? Interestingly, DB2 is rather hard-nosed about implicit conversions. I would have to test again to be certain, but I don't believe DB2 would implicitly convert '23.5' to a number in the above context. Pardon me if I've missed the boat somewhere, or misunderstood the issue, but I don't at the moment see where you can put quotes around digits and still have a numeric literal. I believe the quotes give you a character string. Best regards, Jonathan Gennick Editor, O'Reilly Media 906.387.1698 mailto:jgennick@oreilly.com Friday, October 21, 2005, 10:29:43 AM, Jan Wieck (JanWieck@Yahoo.com) wrote: JW> 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? JW> Section 5.3 of SQL92 defines: JW> <literal> ::= JW> <signed numeric literal> JW> | <general literal> JW> <unsigned literal> ::= JW> <unsigned numeric literal> JW> | <general literal> JW> <general literal> ::= JW> <character string literal> JW> | <national character string literal> JW> | <bit string literal> JW> | <hex string literal> JW> | <datetime literal> JW> | <interval literal> JW> The Restrictions for Entry level SQL say that JW> a) A <general literal> shall not be a <national character string JW> literal>. JW> b) A <general literal> shall not be a <datetime literal> or JW> <interval literal>. JW> There are no restrictions that say a <general literal> cannot be a JW> <numeric literal>. <numeric literal> is defined without quotes, but from JW> the above I conclude that a <general literal> is allowed instead of a JW> <numeric literal> to represent a numeric value, which means Oracle is JW> wrong IMNSVHO. JW> No reason to swell your breast though, we aren't perfect in the literal JW> area either. Because JW> <datetime literal> ::= JW> <date literal> JW> | <time literal> JW> | <timestamp literal> JW> <date literal> ::= JW> DATE <date string> JW> <time literal> ::= JW> TIME <time string> JW> <timestamp literal> ::= JW> TIMESTAMP <timestamp string> JW> <date string> ::= JW> <quote> <date value> <quote> JW> ... JW> We allow a <general literal> to be used as a <datetime literal> or JW> <interval literal> without the keyword DATE, TIME, TIMESTAMP or JW> INTERVAL. Isn't that violating the restrictions for Entry level SQL? JW> Further JW> 1) In a <character string literal> or <national character string JW> literal>, the sequence: JW> <quote> <character representation>... <quote> JW> <separator>... <quote> <character representation>... <quote> JW> is equivalent to the sequence JW> <quote> <character representation>... <character JW> representation>... <quote> JW> Note: The <character representation>s in the equivalent se- JW> quence are in the same sequence and relative sequence as in the JW> original <character string literal>. JW> The rule is more or less repeated for other literal types. But 8.0.4 claims JW> wieck=# select 'foo ' 'bar'; JW> ERROR: syntax error at or near "'bar'" at character 15 JW> LINE 1: select 'foo ' 'bar'; JW> ^ JW> I guess neither Oracle nor we get the cookie here. JW> 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
Jan Wieck wrote:
> 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.
>
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