by Hi,
I'm sorry to be taking up so much of your all of your times but I've
come up against some problems with Oracle's features (or limits) that we
weren't expecting. We have previously used Postgres with our product
with the idea that we'd make it possible to use other (suitable)
databases too when we got round to it, and if it became worthwhile. So
stuff was written as generically as possible and trying to stick to SQL
standards. I know it's very hard sometimes and we had to make sacrifices
to this idealogy for functionality and performance in some cases, but we
hoped it wouldn't be too difficult to port to another database when the
time came. So we came to look at Oracle support (the powers that be
decided it was wanted) for the new version. And doing it using the OCI
way rather than going the ODBC way, for speed (the boost will be worth
it, we hope). Postgres non-odbc is just fine and dead easy using the
libraries.
Unfortunately Oracle falls down a lot where we didn't expect it to with
respect to Postgres (yeah I know Oracle's got lots of other things going
for it - like decent backup and recovery procedures, better product
support and docs and etc....). I'm not trying to invite flames so please
don't start, just stating some facts.
1) Oracle 8i is not SQL92 compliant in the area of NULLs/empty strings -
so how can you store an empty string in a column? I took a look at
Oracle 9i too, doesn't look like they've fixed it yet from the bits I've
had a short browse at (the 8i docs say they will fix it in a future
version).
We don't want unset (NULL) strings getting confused with set-but-empty
ones (we use this in filters, if a field is NULL don't include it in the
filter otherwise it must match the appropriate value). We also don't
want to have to set a separate boolean-type flag saying whether a field
is actually not set or just empty if it is set.
2) The 4k limit for strings - ok for half the strings we use, but for
others we want to use LONG (LONG VARCHAR) having read the docs, cos
LOB's just don't have the same ease of use and we want to treat the data
like a varchar (for ease of coding, plus fewer changes to port it to
Oracle in the C++ class used to implement our Oracle support), but LONGs
are apparently being deprecated in future Oracle versions so we perhaps
ought not to use them. LONG's mean LONG VARCHAR (and other LONG types)?
Or does it just mean a 'LONG' type and that the long variations on other
types will stay?
I swear I've seen (on technet.oracle.com) that Oracle8i limit for
varchar2 or one of the similar types is 64k (minus a few bytes). But
I've also seen it as 4k, and when I try to create a table column the
limit appears to be 4k. 64k would be survivable and shouldn't cause any
problems but it doesn't appear to be the case, all strings seem to be 4k
limited.
3) Why won't the OCI let me alter the database?
I can connect from exactly the same terminal and exactly the same
environment and schema user/password with either: sqlplus - everything
works fine; or the OCI (under C++) and queries(selects) work fine, but
anything requiring changes doesn't, i.e. update, insert. I get the error
"ORA-24333: zero iteration count" and haven't been able to find out any
more about the cause of it. I use the same handles (reinitialised if
necessary) for all the OCI requests. No values are bound - we use values
directly in the query strings dynamically created. Not binding works
fine in the selects (eg. a "where x>5"). I can't think of anything else.
Have I forgotten to do something in the (C++ class) code that calls the
OCI? I call the standard OCIStmtPrepare, OCIStmtExecute, environment all
appears set up ok, no defines are set up until the results of the
execute are known. I can run as many selects one after another in the
same process as I want, it all works fine and they return stuff fine.
But try an update or insert (or the alter session commands) and they all
return OCI_ERROR with the ORA-24333 mentioned above.
4) Is the alter session command part of sqlplus? It doesn't work when
run through the OCI as a query. I'm thinking specifically of the one to
set nls_date_format or whatever it's called. In fact I'm not doing well
with dates - I've set up the OCIDefineByPos() to request various string
types back and not had any luck, do I need to get back an OCIDate() and
do stuff with that to get the fields/final string format, or use
to_char() (or whatever, I know how to use it) every time? Is this
another symptom of the same problem mentioned in 3) above?
Oh yes - is there a format specifier for the GMT offset in the date
format? I can't see one. Makes comparing dates/times from different
timezones much safer.
I'd be very happy if anyone could shed some light/make suggestions about
any of the above points.
Thanks,
John G.