String size constraint in TPLSQLTable

[Zoltan Szabo]

I have to pass varchar2 string arrays with 32000 characters in stored procedures. I found an old topic saying the limit is 4000 (it is from 2002). Does this constraint still apply?

I use DOA 4.7.0.1 Object, Delphi7 and Oracle 9.2.

Thanks,
Zoltan

 

[Devil]

Yes

 

[Marco Kalter]

This constraint has never applied. You can use a PL/SQL String to pass large string values to a PL/SQL stored program unit. The data type name is otPLSQLString, instead of otString, which is an SQL string with a limit of 4000 bytes.

 

[Zoltan Szabo]

Thanks for the answer. However, the Package Wizard generates code which uses otString type for the string arrays. The stored procedure looks something like:

...
Type TStringArray Is Table Of Varchar2 (32000) Index By Binary_Integer ;
...
Procedure USE_STRING_ARRAY (StringArray In Out TStringArray) ;
...

The generated code:
...
OCPQuery.DeclareVariable('STRINGARRAY', otString);
OCPQuery.DimPLSQLTable('STRINGARRAY', Stringarray.TableSize, Stringarray.StringSize);
OCPQuery.SetVariable('STRINGARRAY', Stringarray.ValueArray);
...

If I put more than one value to the string array, the values after the first one are empty in the stored procedure (it is correct when calling 'Execute' on the client).

If I change the generated code manually (replace otString with otPLSQLString) I get an error:

"Only PL/SQL Tables of strings, integers, floats and dates supported"

 

[Marco Kalter]

Sorry, my mistake. For PL/SQL Tables strings are indeed limited to SQL strings of 4000 bytes.

 

[Zoltan Szabo]

Do you plan to change this?