Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

forum.gupta.bugreport (2005-2010)
Eric

Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 26 Oct 2005, 05:34

 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

Mahesh Ramakrishnan

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Mahesh Ramakrishnan » 26 Oct 2005, 18:38

 Posted by:  Mahesh Ramakrishnan 

Eric, Are you sure, it has nothing to do with 9i? Does this work correctly
with 8.0.5? Can you write an anon. stored procedure to call this GET_COUNTRY
by passing a varchar2(32000) variable to see what you are getting back from
this procedure is correct?

I do not have 9i and 3.1, but interested in finding this out.

Eric

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 27 Oct 2005, 04:48

 Posted by:  Eric 

Hi Mahesh,

It has nothing to do with 9i. I arrived at this conclusion because when I
wrote a VB app calling the same stored proc, I got back a string of 3
characters. Yes, the Centura app was getting a 3-character string from
Oracle 8.0.5 database. The only workaround we see right now is to change
all Oracle %type parameters to varchar2.

Alfredo Monasi

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Alfredo Monasi » 27 Oct 2005, 15:51

 Posted by:  Alfredo Monasi 

Hi Erick, the same happened in my company, we were force to change all of
our Stored Procedures in order to keep working :(.

Mahesh Ramakrishnan

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Mahesh Ramakrishnan » 27 Oct 2005, 16:05

 Posted by:  Mahesh Ramakrishnan 

That is not good!!!. I wonder why no one else ever any complaint??? Did
you log a bug?

Niels Allerheiligen

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Niels Allerheiligen » 27 Oct 2005, 16:29

 Posted by:  Niels Allerheiligen 


The Oracle router from earlier versions of Team Developer searched for a
file named ociw32.dll. IIRC, there was an old version distributed with
TD 1.5.1 (don't know about other versions).

So, make sure that you have no old dll from oracle (i.e. ociw32.dll) in
your TD runtime. Otherwise, weird things may happen...

-Niels-

Thomas Lauzi

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Thomas Lauzi » 27 Oct 2005, 16:58

 Posted by:  Thomas Lauzi 

Hi Eric,
there are some basic rules for working with TD1.51(about higher versions of
TD I can´t make a statement) and Oracle (8,9,10)
which we collected through the years and prevent us now from "Surprises".

- no %TYPE in parameters
- A procedure must at least have ONE parameter. eg. SqlPLSqlCommand(
hSql, " pa_test.Do_action() ") won´t work
- Never work with varchar2 parameters > 2000 chars (altthough >2000 chars
is allowed), it could make your app instable.

Regards,
Thomas L.

StefanM
Germany
Posts: 679
Joined: 10 Aug 2018, 15:57
Location: Bavaria

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by StefanM » 27 Oct 2005, 21:00

 Posted by:  Stefan Misch 

Eric and Thomas,

I created a small test case and it works for me. This is my test procedure
(COL1 of table TEST is a char(3) data type):

create or replace procedure TestProc (
p1 in out TEST.COL1%type
) is
begin
p1 := upper(p1);
end TestProc;

and here is the SAL function:

Function: Test
Description:
Returns
Parameters
Static Variables
Local variables
Boolean: blOk
String: slTest
Sql Handle: hlSql
Actions
If SqlConnect( hlSql )
Set slTest = 'abc'
Set blOk = SqlOraPLSQLPrepare( hlSql, 'TestProc(:slTest)' )
Set blOk = blOk and SqlOraPLSQLExecute( hlSql )
If blOk
Call SalMessageBox( 'Passed: len=' || SalNumberToStrX( SalStrLength(
slTest), 0 ) || ' ', 'Calling TestProc', MB_Ok |
MB_IconAsterisk )
Else
Call SalMessageBox( 'Failed !', 'Calling TestProc', MB_Ok |
MB_IconExclamation )
Call SqlDisconnect( hlSql )
Return blOk

Note that the string slTest comes back with a length of 32k.

Also, I must reject Thomas' basic rules:

(1) no %type in parameters:
This is against good PL/SQL programming style.

(2) procedure must have at least one parameter
You can prepare/execure an anonymous PL/SQL block using
SqlOraPLSQLPrepare( hlSql, 'begin pa_test.Do_action(); end;' )

(3) instable for varchar2 with length > 2000
we have some tables with varchar2(4000) fields in our database also some
LONGs (<=32k, just text), one table with a graphic (jpg, also <= 32k) but we
do not experience any instabilities (Ora9R2, TD 3.1-PTF2). But I would not
bet on that there are no bugs here, so I can't really reject this one.

Stefan

BTW: I tested with Oracle9iR2 (9.2.0.4.0) as backend and TD3.1-PTF2. The
version of ociw32.dll is 9.2.0.1.0 (SQLNet client from 9iR2).

Alfredo Monasi

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Alfredo Monasi » 27 Oct 2005, 21:10

 Posted by:  Alfredo Monasi 

Because I'm working in other local and here we use oracle8i so I never had
any need to log this bug.

But we're planning to move to oracle 10G so I will use and app that a friend
did to change the procedures. With oracle 9.0.2 works fine, the problem
begins with 9.2

Will be great if GTD check this problem.

Regards

Alfredo Monasi

Eric

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 28 Oct 2005, 04:34

 Posted by:  Eric 

Yes I did log the bug but haven't received a response yet. It's been over a
week now. :(

Eric

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 28 Oct 2005, 04:41

 Posted by:  Eric 

Hi Stefan

I'm a bit confused. Why do you say that it works for you but then the
string that was returned to Gupta is a 32k string? Did the slTest string
equal to 'ABC____________.........' (32000 bytes) ? Please confirm ASAP.

Thanks!

Eric

Eric

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 28 Oct 2005, 04:46

 Posted by:  Eric 

I only have 1 copy of the ociw32.dll which came from the Oracle 9i
client....

Eric

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by Eric » 28 Oct 2005, 04:50

 Posted by:  Eric 

I also forgot to mention to you guys that most of our Oracle tables are
actually VIEWS pointing to a DB2 database. So all our %type parameters are
based on Oracle views that are based on DB2 tables. Maybe the issue here is
between Oracle and DB2 and not between Gupta and Oracle. This may be a good
lead.

Tomorrow, I will create a dummy Oracle 9i table and create a dummy function
with OUT parameters defined as %type. If this works, then we
have to focus our attention to the Oracle-DB2 link.

I'll keep you guys posted. Thanks for all your responses!!!

StefanM
Germany
Posts: 679
Joined: 10 Aug 2018, 15:57
Location: Bavaria

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by StefanM » 28 Oct 2005, 12:56

 Posted by:  Stefan Misch 

Eric,

if you write a ORACLE procedure that returns a varchar2 or char data type
you cannot specify the length, i.e.

create or replace procedure Foo (p1 in out char(3))

is not allowed. Instead you must code like this:

create or replace procedure Foo (p1 in out char)

This also means that when you use %type like in

create or replace procedure Foo(p1 in out SOME_TABLE.SOME_COL%type)

where SOME_TABLE.SOME_TYPE is a char(3) only the data type but not the
length is used for the parameter declaration.

So you will get a CHAR with unspecified length back and Gupta will treat
this as a 32k CHAR.
The same is true with functions that return strings. Here we always code
like this:

select substr(foo(some_param),1,255) from DUAL into :sSomeString

If we would call foo like this:

select foo(some_param) from DUAL into :lsSomeLongString

then lsSomeLongString must be of SAL type "Long String"

If the parameter p1 of procedure TestProc from my last post would be of type
varchar2 then the message box would display:

Passed: len=3

This is why we disallow the use of CHAR. Instead VARCHAR2 must be used.

Stefan

StefanM
Germany
Posts: 679
Joined: 10 Aug 2018, 15:57
Location: Bavaria

Re: Passing Centura strings to Oracle 9i stored procs with parameters declared as %type

Post by StefanM » 28 Oct 2005, 13:08

 Posted by:  Stefan Misch 

Just to make it clear (because you wrote that the tables are actually on a
DB2 and you acess only views in Oracle) as I don't know if DB2 makes a
difference between CHAR and VARCHAR (or VARCHAR2 in case of ORACLE).

In ORACLE a CHAR is a fixed length type, i.e. if a column COL1 is of type
CHAR(3) and you store 'A' in it COL1 actually contains 'A '.
So out parameters of type CHAR of a procedure will return a CHAR(32000).

Stefan

Return to “gupta.bugreport”

Who is online

Users browsing this forum: [Ccbot] and 0 guests