Home » Developer & Programmer » Precompilers, OCI & OCCI » Bind variables, database links, Oracle 7 OCI and Oracle 9
Bind variables, database links, Oracle 7 OCI and Oracle 9 [message #119595] Fri, 13 May 2005 08:50 Go to next message
chwb
Messages: 3
Registered: May 2005
Location: Stockholm, Sweden
Junior Member
I work on the development and maintenance of a mature application software product written mainly in IBM Smalltalk and running under Windows (NT, 2000, XP, and 2003). It uses two Oracle databases - one directly, and the other via an SQL*Net database link. In most installations, the Oracle server is a Unix machine.

The application always uses the Oracle 7 OCI, irrespective of the Oracle version against which it is installed. To do this, its installation program searches the Oracle client DLLs for one containing the entry point named "ORLON", and renames a copy of that DLL to "ORA73.DLL". The application program itself then always expects to find an "ORA73.DLL" containing the Oracle 7 OCI entry points.

This approach has worked perfectly over a period of at least six years for a variety of Oracle 7 and Oracle 8 versions.

My colleagues and I have recently tried installing the application against Oracle 9, and encountered the following problem:

Queries which are parameterized by a bind variable and which run against a view which is itself defined in terms of a database link return no rows (and no error indication either).

For example, the query
SELECT * FROM FRED WHERE COLUMN1 = :001;
returns no rows if FRED is a view defined as
SELECT * FROM BERT@MY_DB_LINK
even if BERT@MY_DB_LINK contains rows matching the condition for a given value of the bind variable.

If FRED is a local table with suitable content, the query works. If FRED is a view defined against local tables (i.e. not using a database link), the query works.

If the query is not parameterized by a bind variable (e.g. SELECT * FROM FRED WHERE COLUMN1 = 'SEARCH_VALUE';) the query works even when FRED is a view defined in terms of a database link.

We have tried this both with Oracle 8 and Oracle 9 client libraries for Windows. The behaviour is identical.

I would be mightily grateful for any light which can be cast on this problem. Thanks in anticipation.
Re: Bind variables, database links, Oracle 7 OCI and Oracle 9 [message #120375 is a reply to message #119595] Thu, 19 May 2005 06:19 Go to previous messageGo to next message
chwb
Messages: 3
Registered: May 2005
Location: Stockholm, Sweden
Junior Member
I can now refine this problem statement somewhat.

We have only seen the problem if the bind variable (bound with obndra or obndrv) is of external datatype 7 (PACKED DECIMAL). This is normally only used by COBOL clients according to the documentation, but the IBM Smalltalk framework uses it too, and I have now modified the Oracle-provided OCI sample program cdemo2.c to use it and thereby demonstrate the same problem completely repeatably from a C client. I can post the modified source here if anybody would find it useful, and if nobody thinks it would violate Oracle's copyright. I guess I could post my modifications in any case.

I would really like to hear from anybody who has seen this same problem in a COBOL client against an Oracle 9 database, especially if they know of a solution for it.

Thanks in advance for any possible help!

Christopher.
Re: Bind variables, database links, Oracle 7 OCI and Oracle 9 [message #122612 is a reply to message #119595] Tue, 07 June 2005 11:10 Go to previous message
chwb
Messages: 3
Registered: May 2005
Location: Stockholm, Sweden
Junior Member
It seems that this problem is already known to Oracle as bug number 2671230, filed on 15 November 2002.

It is fixed in Oracle 10, apparently, but there is neither a fix nor a recommended work-around for Oracle 9.

The bug description as received from Oracle confirms our own experience that the problem only affects code using PACKED DECIMAL as the external data type, so we have changed our code to use a different data type, and everything looks good so far.

I'd like to thank all members of this forum for their enthusiastic help and imaginative suggestions in relation to the problem.
Previous Topic: how cobol program can run in oracle
Next Topic: Pro *C in oracle 9.2.0.2 in hp-ux?
Goto Forum:
  


Current Time: Mon May 16 00:07:25 CDT 2022