VarChar2 Limits

[Greg Stevenson]

Trying to determine what the limits are getting a VarChar2 "result" from a procedure or function. Up to 32K can be passed between procedures on 8i but it seems like OCI 8.x limits this to 4K (2K for 7x). Is this indeed the case or can a client app get a 32K Varchar2 value from a procedure or function?

 

[Marco Kalter]

The String variable type (otString) represents a SQL string, which is limited to 2K or 4K. If you want to pass longer strings to stored procedures or functions, use the PL/SQL String variable type (otPLSQLString) instead.

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

 

[Greg Stevenson]

Marco,

Does DOA automatically handle procedures/functions returning strings greater than 2/4K. I tried this in PL/SQL Developer and kept getting errors when exceeding 2/4K. Do I need to declare the out variables as something other than a VarChar2?

 

[Greg Stevenson]

Marco,
Another question. What is the impact of just using otPLSQLString in place of otString?

 

[Marco Kalter]

By default Direct Oracle Access assumes that varchar2 variables are SQL strings, and therefore limited to 2/4KB. As far as I know there is no impact using the otPLSQLString data type, other than increased client side memory usage. Each variable instance will take up 32KB.

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