PL/SQL Error 06502 with ODAC 9.7.28.0

[WZehntner]

Hello,
since upgrading to ODAC 9.7.28.0 we are experiencing PL/SQL-Error 06502
No change to our SQL-statements was made.
The same code executes without error when using ODAC 9.7.26.0

We are compiling with RAD Studio Berlin 10.1 Update 2
Our Oracle database is 11g Version 11.2.0.4.0 64bit
We are connecting to the database in direct mode.
The full Oracle error message is in German as follows:
ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer zu klein
ORA-06512: in Zeile 19

It seems to be caused by Parameter-conversion ".asString"
We also encounter the same error when using ".asFloat"
The Database-field "VKKL_ID" is of type "NUMBER"

A sample SQL-Statement Delphi source code is as follows (it happens with many other similar statements):

SVKKL := TOraSQL.Create(nil);
try // 1
SVKKL.session := OraSession;
SVKKL.SQL.Add ( 'DECLARE ' );
SVKKL.SQL.Add ( ' oVKKL verkaufskladde%ROWTYPE; ' );
SVKKL.SQL.Add ( ' vbenu_id benutzer.benu_id%TYPE; ' );
SVKKL.SQL.Add ( 'BEGIN ' );
SVKKL.SQL.Add ( 'select benu_id into vbenu_id from benutzer ' );
SVKKL.SQL.Add ( ' where benu_login = upper(:BENUTZER); ' );
SVKKL.SQL.Add ( 'pck_benutzer.setbenu_id (vbenu_id); ' );
SVKKL.SQL.Add ( 'oVKKL.FEHLERNUMMER := :FEHLERNUMMER; ' );
SVKKL.SQL.Add ( 'oVKKL.FEHLERTEXT := :FEHLERTEXT; ' );
SVKKL.SQL.Add ( 'pck_webshop.initkladde (oVKKL); ' );
SVKKL.SQL.Add ( 'oVKKL.BENUTZER := :BENUTZER; ' );
SVKKL.SQL.Add ( 'oVKKL.KUNDENNR := :KUNDENNR; ' );
SVKKL.SQL.Add ( 'pck_webshop.fuellekladde (oVKKL); ' );
SVKKL.SQL.Add ( 'oVKKL.LIEFERADR := :LIEFERADR; ' );
SVKKL.SQL.Add ( 'pck_webshop.insertkladde (oVKKL); ' );
SVKKL.SQL.Add ( 'COMMIT; ' );
SVKKL.SQL.Add ( ':FEHLERTEXT := oVKKL.FEHLERTEXT; ' );
SVKKL.SQL.Add ( ':FEHLERNUMMER := oVKKL.FEHLERNUMMER; ' ); // Line 18
SVKKL.SQL.Add ( ':SVKKL_ID := oVKKL.VKKL_ID; ' ); // Line 19 this causes Error with ODAC 9.7.28.0
// Executes successfully with ODAC 9.7.26.0
SVKKL.SQL.Add ( 'END; ' );

SVKKL.ParamByName ( 'BENUTZER').AsString := lBenutzer;
SVKKL.ParamByName ( 'KUNDENNR' ).AsString := lKunde;
SVKKL.ParamByName ( 'LIEFERADR' ).AsString := lLieferAdr;
SVKKL.ParamByName ( 'sVKKL_ID' ).AsString := ''; // causes Error
SVKKL.ParamByName ( 'FEHLERTEXT' ).AsString := '';
SVKKL.ParamByName ( 'FEHLERNUMMER' ).AsInteger := 0;

try // 2
SVKKL.Execute; // exception is thrown with above Errormessage
except // 2

We found a workaround, but do not want to analyse hundreds of SQL-statements.
The workaround would be to declare an integer and convert it back to string after execution.
Example:
var iVKKL_ID : int64;
...
// then change Line 19 of SQL to:
SVKKL.SQL.Add ( ':iVKKL_ID := oVKKL.VKKL_ID; ' ); // No Error Version 9.7.28, workaround
...
SVKKL.ParamByName ( 'iVKKL_ID' ).AsLargeInt := iVKKLID; // No Error Version 9.7.28
...
after SVKKL.Excecute extract the field as following:
sVKKLID := SVKKL.ParamByName ('iVKKL_ID' ).AsString; // No Error Version 9.7.28

If you need further information, please let me know.

[WZehntner]

Additional information:
The initial string sVKKL_ID is an empty string like:
sVKKL_ID := '';
The return value of the database-field VKKL_ID is a 9-digit number.
When we initialize the string sVKKL to a string with minimum length of 9 like this:
sVKKL_ID := '123456789';
then no error occurs.

Please let me know when you start looking into this issue.

[MaximG]

We have checked functionality of ODAC 9.7.28 when using string parameters and couldn't reproduce the issue. For further investigation, please compose a full small sample reproducing the issue and send it to us, including a script for creating database objects used in the sample.

[WZehntner]

We further looked at our Code and found following:

On Oracle side create a procedure like this:

CREATE OR REPLACE PROCEDURE testcase (p_text IN OUT NOCOPY VARCHAR2)
IS
BEGIN
p_text := 'This is a sample text';
END;

With Delphi we use following code:

function NEWNAK_TestCase ( const OraSession: TOraSession;
var ErrorMessage: string ): Integer;
var
SVKKL: TOraSQL;
ErrorCode: Integer;
tmpVar: Variant;

begin
ErrorCode := 0;
ErrorMessage := '';

SVKKL := TOraSQL.Create(nil);
try // 1
SVKKL.session := OraSession;
try // 2
SVKKL.SQL.Add ( 'DECLARE ' );
SVKKL.SQL.Add ( ' P_TEXT VARCHAR2(255); ' );
SVKKL.SQL.Add ( 'BEGIN ' );
SVKKL.SQL.Add ( 'P_TEXT := :FEHLERTEXT; ' );
SVKKL.SQL.Add ( 'TESTCASE ( P_TEXT ); ' ); // <= this procedure is on Oracle
SVKKL.SQL.Add ( ':FEHLERTEXT := P_TEXT; ' );
SVKKL.SQL.Add ( 'COMMIT; ' );
SVKKL.SQL.Add ( 'END; ' );

// the following line is needed with ODAC Version 9.7.28.0
// SVKKL.ParamByName ( 'FEHLERTEXT' ).ParamType := ptInputOutput;
// above line is not needed ODAC Version 9.7.26.0

SVKKL.ParamByName ( 'FEHLERTEXT' ).AsString := '';
SVKKL.Execute;

// Extrahiere Ergebnis
tmpVar := SVKKL.ParamValues ['FEHLERTEXT'];
if (VarIsNull(tmpVar)=False) then
begin // Fehlertext ist gesetzt
ErrorMessage := tmpVar;
StringReplace ( ErrorMessage, '"', '*', [rfReplaceAll] );
ErrorMessage := ' TestCase Error(1): ' + ErrorMessage;
end
else
begin // Fehlertext ist NULL
ErrorMessage := '';
end;

except // 2
on E: exception do
begin
ErrorCode := 3;
ErrorMessage := 'OraSession LastError: '
+ IntToStr ( OraSession.LastError ) + E.Message;
result := ErrorCode;
exit;
end;
end; // 2

finally // 1
SVKKL.Free;
result := ErrorCode;
end; // 1
exit;
end;

On "SVKKL.Execute;" an exception is thrown with following Errormessage:
6502ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer zu klein
ORA-06512: in Zeile 6

You can say that we used sloppy programming code,
but we can say that it used to work in ODAC Version 9.7.26.0
May be the default ParamType was ptInputOutput in Version 9.7.26.0 and has now been changed to ptInput ?

[MaximG]

Thank you for the information. We investigated the current ODAC behavior when working with parameters compared to the previous versions. We will inform you about the results in the near future.

[MaximG]

We have investigated the current ODAC behavior when working with parameters compared to the previous versions. In the latest and previous ODAC versions you should excplicitly specify the parameter type :

Code: Select all

 SVKKL.ParamByName('FEHLERTEXT').ParamType := ptInputOutput; 

Try to change the following statement :

Code: Select all

 SVKKL.SQL.Add(':FEHLERTEXT := P_TEXT || ''same value '';'); 

In this case the provided query execution will also lead to an error if you do not specify the 'FEHLERTEXT' parameter type.