Posted by: Eric
We are currently migrating our Oracle DB from 8.0.5 to 9.2 (9i).
We are using Gupta Team Developer 3.1. Here's a major issue that we're
facing:
We have lots of calls to Oracle stored procs and we use the
SqlPLSQLCommand() command to execute them. Most of our Oracle stored procs
have parameters defined as table.column%type (see example below):
PROCEDURE GET_COUNTRY (p_sCountry OUT MV_COUNTRY.COUNTRY_CODE%TYPE)
BEGIN
END
Note: MV_COUNTRY.COUNTRY_CODE%TYPE returns char(3).
When we call the above stored procedure from the Gupta code, we also supply
the corresponding parameter/s and these parameters are variables declared
within the Gupta code of course. Going back to the example above, the Gupta
variable we pass is of type STRING. After the call to the above stored
procedure, the length of the returned string value becomes 32,000 bytes
(instead of 3 characters). We did not have this problem with Oracle version
8. We installed the latest patch, PT4, but it didn't solve the problem.
Running out of options, we experimented on changing the data type of the
p_sCountry parameter to a varchar2 and that fixed the problem. This
obviously is a workaround but it is not practical for us to do it since we
have hundreds and hundreds PL/SQL procedures/functions that have parameters
declared as %type. This would entail lots of testing, which for sure would
force us to redo our project schedule. There seems to be a bug within the
SQLORA32.DLL but PT4 didn't fix the problem. Is this a known bug? I'm
surprised that no one has reported this problem.
Any feedback would be greatly appreciated. Thanks!
Eric