Several code(and design) Qs/probs from an OCI newcomer

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. Received on Wed Aug 29 2001 - 11:23:24 CDT