Tablesize in plsqltables

[GormKruse]

Hi,

I have been working a bit with plsqltables in stored procedures and the package wizard. I'm using the tables to post and fetch data.

I am experiencing problems when the DefaultPLSQLTableSize is too (?) small for a result set. As I understand the documentation it really is required to know the size of the result set up front (opposed to every other concept of relational databases).

For various reasons I have to work with procedures and plsqltable as fetch methods.

Questions:
1: Am I all wrong?
2: Could the package wizard be made to handle these tablesizes a little more dynamically (it doesen't help me much if I have to change the code too much)?
3: Are there other methods to get result sets as an output parameter from a stored procedure?

I'm running DOA 3.4.5.1 and the wizard is version 1.0.3. (Going for Delphi7 and DOA 3.4.6.4 soon)

 

[Marco Kalter]

1: Am I all wrong?

No.

2: Could the package wizard be made to handle these tablesizes a little more dynamically (it doesen't help me much if I have to change the code too much)?

No. You can control the size, but the table has to be pre-allocated on the client before making the call.

3: Are there other methods to get result sets as an output parameter from a stored procedure?

There are various alternatives. The best are:

1. Use a cursor variable if the table represents a SQL result set.
2. Use a collection table object.

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

 

[GormKruse]

Originally posted by mkalter:

1: Am I all wrong?

No.

2: Could the package wizard be made to handle these tablesizes a little more dynamically (it doesen't help me much if I have to change the code too much)?

No. You can control the size, but the table has to be pre-allocated on the client before making the call.

3: Are there other methods to get result sets as an output parameter from a stored procedure?

There are various alternatives. The best are:

1. Use a cursor variable if the table represents a SQL result set.
2. Use a collection table object.

Hi,

I was tuning in on the cursor solution.

Thanks Marco