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

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

Post by John Garne » Fri, 31 Aug 2001 01:23:24



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.

 
 
 

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

Post by Sybrand Bakke » Fri, 31 Aug 2001 09:00:54


Answers embedded.

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

Hth,

Sybrand Bakker, Senior Oracle DBA


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

Quote:

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

- Show quoted text -

Quote:

> 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

Quote:

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

Quote:

> 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

Quote:> 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

- Show quoted text -

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


 
 
 

1. Several Paradox probs that no one can explain...

I've been having several Paradox problems that I can't figure out.

1)  Sometimes changes to a table do not stay there.  I will add/edit a
record and later the changes will be gone.  IE: I will make a change
with an application.  I will close the application (the post method is
called & the table is closed afterwards).  I then load the application
and changes are gone.

2) Sometimes I will copy a table from somewhere else but I will still
see the old data.  I will have to reboot to see the new data.  It
appears the data is cached and the BDE does not know the cached data is
no longer valid.  Is there a way to flush the cache? (& the

3) Sometimes I get an error when I am running both Database Desktop &

an error message 'Record locked by user [me]'.  I'm not even trying to
write/edit the record either, just read or findkey.

I haven't been able to find anyone with answers other than "Paradox
sucks" or "Paradox is notorious for dirty data".   I don't really like
those answers...

This is not just me.  Several people at work have had these problems.

Thanks for any help.

Phil


2. How to handle empty import files?

3. NT Service with OCI Code cannot start with wsock32.dll code

4. Create Partitioned Table

5. Database Design Question - Several rows, or only one?

6. Change Password on first Log-in

7. D3 install probs & tape probs

8. implicitly commit?

9. OCI Array Fetch Sample Code.

10. Compiling OCI C++ code on HPUX 11

11. OCI Sample code in C++/C

12. OCI: OCILobOpen() causes ORA-00600: Internal Error Code

13. Simple OCI example code?