Board index » delphi » Does Oracle stored procedures truncates strings (255 chars) ?

Does Oracle stored procedures truncates strings (255 chars) ?

Hi !

        I use :
        - Oracle 7.2.2 (PL/SQL 2.2)
        - Delphi 3.0 (just installed)
        - BDE 4.0    (just installed)

        I have a "test" stored procedure (inside a package) with one input
parameter and one output parameter. It only moves the input to the
output parameter. JUST THAT.

        When I run it with SQL*Plus it goes ok. I send a string with 500
characters and it comes back in the output parameter. If I send ONLY 255
CHARS to the output parameter it goes ok.

        When I run it with Delphi, I get an error :
        "ORA-06502: PL/SQL: numeric or value error".

        Any clue ?

        Thanks in advance

        Bye

        Fran?a

P.S. : There go both the procedure and the delphi code :

        In Oracle :

PROCEDURE RETORNA_STRING( TIPO_OBJETO IN VARCHAR2, TEXTO_SELECT2 OUT
VARCHAR2 ) IS
BEGIN
TEXTO_SELECT2 := TIPO_OBJETO;
END RETORNA_STRING;

        In Delphi :

procedure TForm1.FormShow(Sender: TObject);
var string01, string02 : AnsiString;
begin

SetLength(String01, 500);
SetLength(String02, 500);
{ puts 500 chars into String01 )
String01 :=
'....*....1....*....2....*....3....*....4....*....5....*....6....*....7....*....8'
+
'....*....9....*....0....*....1....*....2....*....3....*....4....*....5....*....6'
+
'....*....7....*....8....*....9....*....0....*....1....*....2....*....3....*....4'
+
'....*....5....*....6....*....7....*....8....*....9....*....0....*....1....*....2';

DataModule2.StoredProc1.ParamByName('TIPO_OBJETO').AsString := String01;
DataModule2.StoredProc1.ExecProc;
 ===========> HERE I GET THE ERROR MESSAGE (ORA-06502) <============

String02 :=
DataModule2.StoredProc1.ParamByName('TEXTO_SELECT2').AsString;

Application.MessageBox( Pchar(String02), 'Ttulo', MB_OK );

end;

 

Re:Does Oracle stored procedures truncates strings (255 chars) ?


FYI, I get the same error with D4UP2, BDE5.01, Net8.0.4, O8.0.4.  I was
going to tell you to updated your BDE because 4 is ancient history, but it
doesn't look like that would help.  You should probably update anyway on
general principal (it is free on web site).

V/R
Russell L. Smith

Quote
Sergio Franca wrote in message <365C1380.21D70...@sir.inpe.br>...
> - Oracle 7.2.2 (PL/SQL 2.2)
> - Delphi 3.0 (just installed)
> - BDE 4.0    (just installed)

> When I run it with Delphi, I get an error :
> "ORA-06502: PL/SQL: numeric or value error".

>PROCEDURE RETORNA_STRING( TIPO_OBJETO IN VARCHAR2, TEXTO_SELECT2 OUT
>VARCHAR2 ) IS
>BEGIN
>TEXTO_SELECT2 := TIPO_OBJETO;
>END RETORNA_STRING;

Re:Does Oracle stored procedures truncates strings (255 chars) ?


Hi,

        Thanks Russell...

        Did you find any workaround other than split the string in two ? not
quite elegant, but it works...   :-)

        Bye

        Fran?a

Quote
Russell L. Smith wrote:

> FYI, I get the same error with D4UP2, BDE5.01, Net8.0.4, O8.0.4...

Re:Does Oracle stored procedures truncates strings (255 chars) ?


I found no workaround.  I also found a boundary condition error in the BDE
O8 driver where the length of the ftString input parameter is exactly 255
characters:  Memory fault.  254 characters works fine, and 256 characters
gives me ORA-06502.

It looks like input ftString parameters greater than 255 chars are
automatically handled as BlobParams, but output parameters strike out.

Quote
Sergio Franca wrote in message <365ED2BA.22953...@sir.inpe.br>...
>Hi,

> Thanks Russell...

> Did you find any workaround other than split the string in two ? not
>quite elegant, but it works...   :-)

> Bye

> Fran?a

>Russell L. Smith wrote:

>> FYI, I get the same error with D4UP2, BDE5.01, Net8.0.4, O8.0.4...

Re:Does Oracle stored procedures truncates strings (255 chars) ?


On Fri, 27 Nov 1998 13:36:00 -0500, "Russell L. Smith"

Quote
<Russell_L._Sm...@mail.amsinc.com> wrote:
>I found no workaround.  I also found a boundary condition error in the BDE
>O8 driver where the length of the ftString input parameter is exactly 255
>characters:  Memory fault.

I can tell you for sure this is a limitation of Delphi in general.  I
have a stored procedure that originally was just one in and one in/out
that is now 16 ins and 1 in/out due to the limitation.  We were
sending large lists of clients to Oracle, and the only way was to chop
it up into a bunch of pieces.  Not very elegant, but it works.  The
other work around was to send them to a temporary object, and execute
from there...

Mark
-+-+-+-+-
Mark W. Lind
markl...@pacbell.net

Other Threads