Re: Pro*C/PL/SQL problem. Is upgrade the answer?
Date: Fri, 04 Dec 1998 21:40:23 +0000
Message-ID: <366856C7.7E49_at_earthlink.net>
I got some feedback from Thomas Kyte (tkyte_at_us.oracle.com), giving me a solution to my problem of trying to compile a PL/SQL block in Pro*C, where the code in the block was attempting to declare two records whose types are defined in a package, assign values to them, and then call a package routine. The Pro*C compiler was from Oracle 7.3.2. Pro*C kept giving me an "Invalid variable reference" error, when I tried to assign a value to one of the record types.
Mr. Kyte suggested putting the Pro*C PL/SQL block in a macro definition, and then using strcpy() to copy the macro contents into a char array, and using the char array in a simple dynamic SQL statement. This solves the problem, but is archaic since you end up referencing bind variables in your PL/SQL block-string, and then saying later, "Use these C bind variables in the block I just gave you." But...it works. Essentially, it bypasses Pro*C's PL/SQL parser, but allows you to transfer data values from a C program to the PL/SQL block.
What I did not think to mention in my original message, since I wasn't informed of this, was that the server Pro*C was attempting to interface with to do semantic checking was an Oracle 8 database. This, as we found out, was the problem.
When I copied the PL/SQL package from Oracle 8 to Oracle 7, and then had Pro*C interface with it, the problem went away. This was not a Pro*C bug, as I had assumed, but rather a problem with backward compatibility between Oracle 8 server and Oracle 7 developer tools.
The interesting thing was that I was able to ultimately reach the goal of interfacing with Oracle 8. While Pro*C had a problem interfacing with Oracle 8, the final compiled executable did not. The solution was to have the package in two places: On the Oracle 7 server, for Pro*C to look at and check against, and on Oracle 8, where the compiled program could interface with the production version of the package.
I know this sounds convoluted. The situation is that we aren't able to upgrade SCO, our Unix development platform, to Oracle 8, since Oracle has not released this version for it. Oracle 7.3.4 was apparently the last one. We are developing a system for a client, which is a Unix server (coded in Pro*C) that will receive data, and then transfer it to an Oracle 8 server on Windows NT, using PL/SQL package "interfaces", generated by D2K, which provide an abstract layer between the data source, and the base tables.
---Mark
mmiller_at_nyx.net
Received on Fri Dec 04 1998 - 22:40:23 CET