Re: Pro*C problems

>
> Dear Oraclites
> Here is a problem encountered by one of my friends while using
> Pro *C
> The ORACLE version being used is V7.0.13.1.0
> Pro*C version 1.5.7.0.1
> PL/SQL version 2.0.15.1.0
>
> PROBLEM:
> 'X' rows to be retrieved from table1. 'X' rows to be retrieved from table2.
> 'X' rows to be retrieved from table3 ( Master-Detail Query ).
>
> A. Which of the following options will give better performance in terms of
> execution time,error handling and dynamic SQL methods.In addition to these,
> any other option exist for better performance?.
>
> Note 'X' is greater 5000.
>
> Option 1
> Declaring three cursors and using FOR loops 'X' times.
>
> Option 2
> Using host arrays of dimension 'X' for each column and
> fetching in a batch.
>

        Option 2 is always better than Option 1.  Even if there is any
        restriction in the array size, arrays of the maximum allowed
        size can be repeatedly used till all the records are fetched.

        Since it is a master-detail query, you can consider using a single
        SQL statement that joins the tables and use host array for the
        fetch.



>   Option 3

>         Using a PL/SQL block inside Pro*C program. This option


>   can be split in two ways:

>         a. Declaring a PL/SQL table for each column and equating

>   with host array.

>         b. Declaring three cursors and using FOR loops for 'X' times.

        No gain in using a PL/SQL block.  PL/SQL blocks are mainly used
        to combine a set of SQL statements and pass it on to the server
        in one pass thereby reducing traffic.  Since your requirements are
        only simple queries, PL/SQL will not help much I guess.

> Option 4
>
> Calling a PL/SQL stored procedure from Pro*C and retrieving all the
> column values using PL/SQL tables as parameters of the stored
 procedure.
>

        Using a stored procedure also does not reduce the work load in this
        situation and might actually add some more overheads.

> B. Is there any limitation in the dimension of the host array while
> passing it to a PL/SQL stored procedure?
>
>

         I don't have any knowledge of the limitation of host array while
         passing it to a PL/SQL procedure.  But an Oracle Bulletin reports
         the maximum dimension of host array allowed in any host program is
         32,767.


     Best of Luck Sridhar,
     Rukmani.