Truncated result of Oracle System Function

[belitski]

Hello,
I am not sure if this is a bug or our misunderstanding.
We use Oracle Database and need to call Oracle SYS Function DBMS_APPLICATION_INFO.SET_MODULE.
The following code

String: sModule = "MODUNAME"
String: sAction = "action"
String: sSql = "DBMS_APPLICATION_INFO.set_module(sModule, sAction)"
If SqlPLSQLCommand(hSql, sSql)
Call SqlPrepareAndExecute(hSql,'COMMIT')

is successfully executed and results in some entry in Oracle system tables, that can be queried over system view v_$session.
Unfortunately, the query "select module from v_$session" returns truncated value "MODU" instead of "MODUNAME".
This is so only in TD60.

regards,
Vladimir

P.S. Two Ora Logs excerpts can be helpful:
Ora Log from TD60:
The log is more detailed, but shows for function parameter only the first letter "M".
Actually, the value "MODU" was used as resulting query on v_$session confirm it (see post):

[set timeout] -1
[describe] name = MODULE_NAME ,data size = 0, datatype
= 1
[describe] name = ACTION_NAME ,data size = 0, datatype
= 1
[bind] name = A0, buffer size = 32767, datatype = 1
[bind] name = A1, buffer size = 32767, datatype = 1
[execute]
[0] M
[0]
[compile] COMMIT
[execute]
[commit]

Ora Log from CTD151:
It is not so detailed, but parameter value is correct both in log and in the result:

[set timeout] -1
[execute]
[0] MODUNAME
[0]
[compile] COMMIT
[execute]
[commit]
[execute]

P.P.S.
There was a similar issue 4 years ago in TD51. It was fixed and there is no this problem in TD52.
In TD60 it reappeared again.

P.P.P.S.
Generall point: it is really inconvenient that for all paramter values in log only the first character is shown.
This concerns all TD versions besides the oldest one CTD1.5.1.

Last evidence:
This is Oracle Trace. It confirms, that TD60 sends a wrong value in the procedure (see mod='MODU'):

PARSING IN CURSOR #2 len=54 dep=0 uid=31 oct=47 lid=31 tim=13644059421 hv=2913779128 ad='665bfe3c'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:a0,:a1); END;
END OF STMT
PARSE #2:c=0,e=735,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=13644059412
APPNAME mod='MODU' mh=3711830254 act='' ah=4029777240
EXEC #2:c=0,e=6260,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=13644071060
XCTEND rlbk=0, rd_only=1

[Jeff Luther]

We use Oracle Database...

Is the DB enabled for Unicode/double-byte characters? And what version of Oracle DB and what's the Oracle client version?

See this page: http://www.guptatechnologies.com/Produc ... atrix.aspx
for which DBs and clients have been certified with TD v6.

[belitski]

Thank You for answering.
We test with Oracle 9i and it is not in the matrix. However, in the meantime I have found another post, two month old,

http://newforum.com/phpBB3/viewtopic.php?f=47&t=7172

with the same problem under Oracle 11.
I have contatced the authour of this post and he told me that he has got a patch from Unify
that fixed the issue.
If you mean it cannot work with Oracle 9i (TD52 is also not in the matrix and it does work with 9i),
than we'll try to test under Oracle 11. But this is somehow contradictory, because the author of the
post referenced above already did it and he had exactely the same problem as we have.
Regars,
Vladimir

[belitski]

Hello Jeff Luther,
since there were no any further answers to my question, I have installed Oracle 11g client which is definitely in the matrix of supported Oracle versions.
Unfortunately, the result is exactly the same as with Oracle 9i. This is actually what I have expected, because TD52 is also Unicode and does not have this problem.
To my mind, either
the bug was somehow re-introduced into TD60 (that is indirectly supported by
http://newforum.com/phpBB3/viewtopic.php?f=47&t=7172
where the same problem was observed)
or
the TD60 in the download space on Unify web site is corrupted.
Please, tell us what we are doing wrong.
Regards,
Vladimir

[belitski]

Hello,
it seems there is nobody who can give us any hint - that is really a pity.
Attached is very-very simple example. In the apt-file the value of sParam is set to 'testtest' and is compared within Oracle procedure with 'test'. Since half of the sParam value is lost on the way, the comparisson is successful. But if you set sParam to 'test' - it does not work. At least it does not work by us under any combination of Oracle server and client versions with TD60 SP3.
Why nobody from Unify say anything? If we are doing some stupid error - please, tell us about that.

Thank you very much,
Vladimir

[klaus@easypro]

Hi Vladimir,

I've tried your testcase and I have to say that it works for me. Submitted the string "testtest" you procedure results in error as it is not equal to "test". I've extended your table to save the parameter and so I saw, that the string "testtest" was correctly received by the procedure.

My configuration / Tested against:
Team Developer 6.0 SP 3, EMP 5371
Oracle DB: 11.1.0.6.0 (Non-Unicode Charset: WE8ISOMSWIN1252)
Oracle DB: 11.1.0.6.0 (Charset: AL32UTF8)
Both times Oracle Instant Client (I'm not sure, but I think, its Instant Client 10g)

I'm afraid, but I don't see any troubles at your code, especially within this simple test case.

What about your SQL.ini? Do you have any special properties set?
Have you already tried the SqlOraPLSQLPrepare + SqlOraPLSQLExecute commands?

kind regards
klaus

[belitski]

Hello Klaus,
thank you very much for answering.
We have checked it with SqlOraPLSQLPrepare + SqlOraPLSQLExecute and it works.
However, it does not work with SqlPLSQLCommand although it should.
The version we use for test:
Version 6.0-SP3 Build 25471
Could it be that this build still has the bug?
To my mind there is nothing special in our sql.ini - same as for TD52 where it works fine.
Regards,
Vladimir

[klaus@easypro]

Hi Vladimir,

great to hear that the sqlora... functions work.
Btw. my TD with emp 5371 has the build number 26632.

Maybe you have a chance to try it with this emp.
Do you know, to which emp your build number corresponds?

kind regards
klaus

[belitski]

Hello Klaus,
so you have a newer version than we have.
But this is the one on the download site of Unify.
Where can the EMP number be checked?

best regards,
Vladimir

[Jeff Luther]

Vladimir:

Unify management decided not to post v6 EMP releases after EMP5368: http://newforum.com/phpBB3/viewtopic.php?f=66&t=7263
so EMP5371 is not generally available to everyone.

Here's some info. as to why the EMPs stopped being generally released. Here is info. on why:
http://newforum.com/phpBB3/viewtopic.php?f=66&t=7344


You had also commented about Version 6.0-SP3 Build 25471

That build # is for the original release of v6 SP3 "RTM" (Release To Manufacturing) meaning the original SP3 before any EMP patches had been released.

EMP5368 is build 26160, which you can download on that link page shown at the top of this comment. Mike also attached a zip of the README for that EMP. I suggest you apply that patch and see if your issue is resolved. Each EMP is 'cumulative' - meaning it contains fixes for all the previous EMPs, BTW.

[belitski]

Hello Jeff,

thank you very much for detailed answer and link to EMP5368 - the truncation
problem was resolved in this version (although i didn't find reference to it in the list of fixed issues).
As far as we could find from the test the key change was done within sqlora32.dll.

Best regards,
Vladimir

[Jeff Luther]

EMP5368 - the truncation problem was resolved in this version

We have a winner! Glad to hear this, Vladimir, thanks for letting us know.

although i didn't find reference to it in the list of fixed issues

Did you also look in the longer list further down the txt file? That top list is those bugs fixed *for* this EMP; lower list is a cumulative list of all fixes for all prior EMPs. (Maybe TD-15968 fixed your problem too? Just a guess...)

[klaus@easypro]

EMP5368 - the truncation problem was resolved in this version

We have a winner! Glad to hear this, Vladimir, thanks for letting us know.

that's great to hear!

regards
klaus

[belitski]

Many thanks to all participants