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

Answers embedded.

Posting questions should not be considered as a replacement for reading the manual.

Hth,

Sybrand Bakker, Senior Oracle DBA

"John Garner" <jgarner_at_commercenti.com> wrote in message news:3B8D16FC.4EF0D9CE_at_commercenti.com...
> 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).

The sql92 standard, as you might be aware, has several levels.
>
> 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.

Oracle *requires/urges* you to use indicator fields. If you don't want that, better forget about an Oracle version of your product.

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

Long is obsolete and you shouldn't use them. It is retained for backwards compatibility only. CLOBs are the way to go, and they have been implemented to overcome the limitations of longs: you can have only one long per table. Please study the dbms_lob package before you start complaining.

Varchar2's are 4k in the database and 32k *internally* in pl/sql

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

Yet that is about the most stupid thing (sorry to say so) you can do, as it will limit scalability of your application due to excessive parsing.

>
> 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?
No it isn't, it's part of sql. You need something what is called in pl/sql and Pro*C 'execute immediate'

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?

There isn't. Oracle is an US product and for American citizens the world stops with the US.

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 - 19:00:54 CDT