Oracle Stored Procedure 
Author Message
 Oracle Stored Procedure

Hi,
I am using ADO 2.6 to connect to Oracle 8i(8.1.6) database.
To call a stored proc, I have
    {call Test("?, {resultset 100, o_Test1, o_Test2, o_Test3})}

Is there anyway I can avoid hardcoding number of records expected(in this
case 100)? The reason is the stored proc is getting data which is dynamic
and if it exceeds 100, I get the error "PL/SQL: index for PL/SQL table out
of range for host language array ORA-06512". I thought of making a separate
call to get the count. But it proves very expensive.
Any ideas?
Thanks in advance,
Ramesh



Tue, 07 Sep 2004 14:45:35 GMT  
 Oracle Stored Procedure

Hi,
I am using ADO 2.6 to connect to Oracle 8i(8.1.6) database.
To call a stored proc, I have
    {call Test("?, {resultset 100, o_Test1, o_Test2, o_Test3})}

Is there anyway I can avoid hardcoding number of records expected(in this
case 100)? The reason is the stored proc is getting data which is dynamic
and if it exceeds 100, I get the error "PL/SQL: index for PL/SQL table out
of range for host language array ORA-06512". I thought of making a separate
call to get the count. But it proves very expensive.
Any ideas?
Thanks in advance,
Ramesh

Can't you just use RowNum in your sp to restrict the number of rows returned?


Microsoft MVP (Visual Basic)



Tue, 07 Sep 2004 22:38:48 GMT  
 Oracle Stored Procedure
Why don't you just use a ref cursor instead? Then, you don't have to
specify the number of rows to return at all!
Quote:

> Hi,
> I am using ADO 2.6 to connect to Oracle 8i(8.1.6) database.
> To call a stored proc, I have
>     {call Test("?, {resultset 100, o_Test1, o_Test2, o_Test3})}

> Is there anyway I can avoid hardcoding number of records expected(in this
> case 100)? The reason is the stored proc is getting data which is dynamic
> and if it exceeds 100, I get the error "PL/SQL: index for PL/SQL table out
> of range for host language array ORA-06512". I thought of making a separate
> call to get the count. But it proves very expensive.
> Any ideas?
> Thanks in advance,
> Ramesh



Sat, 18 Sep 2004 23:01:36 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Help? Accessing oracle stored procedure

2. Running an Oracle Stored Procedure from Access 8.0

3. Executing an oracle stored procedure from access 95

4. call Oracle stored procedure from Excel?

5. does MS support Oracle Stored Procedures?

6. oracle stored procedure

7. Calling an Oracle Stored Procedure

8. Calling Oracle Stored Procedure from VB

9. How to call an Oracle stored procedure from VB

10. Oracle Stored Procedures in VB6

11. syntax to access oracle stored procedure

12. Oracle stored Procedures from VB

 

 
Powered by phpBB® Forum Software