Re: Pro*C PL/SQL bug. Is upgrade the solution?
Date: Fri, 20 Nov 1998 21:33:59 GMT
Message-ID: <3656dec0.19516032_at_192.86.155.100>
A copy of this was sent to Mark Miller <mmille10_at_earthlink.net> (if that email address didn't require changing) On Thu, 19 Nov 1998 20:32:34 -0700, you wrote:
>I have found a bug in the Pro*C compiler I am using, in relation to
>PL/SQL blocks. We have Oracle 7.3.2.2.0, and the Pro*C version is
>2.2.2.0.0, running on SCO OpenServer, Release 5.
>
>I am trying to get a Pro*C program to interface with the CAPI/TAPI
>packages and stored procedures generated by D2K. Most of it works,
>except for one part.
>
>Here is what I have tried:
>
>EXEC SQL EXECUTE
> DECLARE
> cwblah cw$checklist_items.cw$ind_type;
> record cw$checklist_items.cw$row_type;
> BEGIN
> record.datetime := to_date(:expireDate,
> 'YYYYMMDDHH24MI');
> record.file_name := :filename;
> cwblah.id := :value;
>
> cw$checklist_items.ins(record, cwblah);
> END;
>END-EXEC;
>
Another way to code this, to bypass pro*c even parsing the above code and avoid the bug alltogether is as such:
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR sqlstmt[250];
VARCHAR expireDate[25];
VARCHAR filename[255];
int value;
EXEC SQL END DECLARE SECTION;
#define SQLSTMT "\
DECLARE \n\
cwblah cw$checklist_items.cw$ind_type; \n\ record cw$checklist_items.cw$row_type; \n\ BEGIN \n\
record.datetime := to_date(:expireDate, \n\
'YYYYMMDDHH24MI'); \n\
record.file_name := :filename; \n\
cwblah.id := :value; \n\
\n\
cw$checklist_items.ins(record, cwblah); \n\ END;" strcpy( sqlstmt.arr, SQLSTMT );
sqlstmt.len = strlen( sqlstmt.arr );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C USING :expireDate, :filename, :value;
}
By using dynamic sql, you can avoid this.... Also, you won't need SQLCHECK=SEMANTICS USERID=u/p to precompile (makes the precompile go lots faster...)
>Pro*C complains that the line
>
>cwblah.id := :value;
>
>is an invalid reference to variable "cwblah".
>
>So the situation is I can declare cwblah as a cw$ind_type, but I can't
>assign any values to it. This is a problem, since CAPI/TAPI sets the
>indicator columns in the cw$ind_type to FALSE by default. The "ins"
>stored procedure will interpret this as though I am passing in a record
>with all null values (which I am not). The distinction we discovered is
>that the cw$row_type is a PL/SQL record type that uses actual table
>column types to define its columns. The cw$ind_type is purely a PL/SQL
>record type that does not use table columns to define its columns.
>
>We tried compiling this same module with a Windows Pro*C, Version 8.0
>precompiler, and it accepted the above code.
>
>We are considering upgrading our SCO box to Oracle 7.3.4, so that we can
>compile the above code, and other modules that use the CAPI/TAPI stored
>procedures. The thing is, we don't know if doing this will actually fix
>the bug I am encountering. Does anyone know if this bug has been fixed
>in Oracle 7.3.4?
>
>Also, is it possible to upgrade just the Pro*C precompiler? In other
>words, is it possible to leave Oracle 7.3.2 as it is, and just install
>over the old version of Pro*C? Will the new Pro*C still work with
>7.3.2? Are there incompatibilities to consider?
>
>Thanks,
>
>---Mark
>mmiller_at_nyx.net
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Fri Nov 20 1998 - 22:33:59 CET