PLSQL Table in stored procedure

[AndreyB]

I have a stored procedure with PLSQL Table as a parameter;
type BigInt_Tab is table of number(20);
procedure test(AParam in BigInt_Tab);

In Delphi to call that procedure:
Query.SQL.Text := 'begin testAParam); end;'
Because in Delphi I cannot use Integer or Float type to store that big (precision 20) number I declare AParam variable as otString, otherwise the value is just truncated. Then DimPLSLTable(AParam, 3, 20);
On Query Execute I've got bind array param type mismatch error.

The only solution I can see is alter procedure and type definition to make
type BigInt_Tab is table of varchar2(20);
But it's kind of lame IMHO, is there any other more elegant way to do this.
Thanks

 

[Marco Kalter]

There is no other way to do this. Direct Oracle Access uses Integer or Double, and 20 digits precision exceeds both these data types. A character string representation is the best alternative.

------------------
Marco Kalter
Allround Automations

 

[AndreyB]

Thanks.
But if I don't have access to the procedure code and therefore cannot modify procedure parameter definition. How should I proceed? I bet there should be some other way or at least some enhancement in incoming DOA version to work around of such a problem.

 

[Marco Kalter]

As a workaround you could write a little PL/SQL Block where you have a local PL/SQL Table of number(20) that is passed to the program unit. This local table can be copied from/to a varchar2(20) table that is defined as a bind variable. This bind variable can be accessed as usual from your application.

------------------
Marco Kalter
Allround Automations