Re: Retrieving multiple rows into a Pro*C program

>In article <CyLxtI.3Lo_at_wtc35a.DaytonOH.NCR.COM> Dana Schoonover writes:
>What is the best way for a Pro*C program to retrieve an entire row(s) from a
>table with a large number of columns(36)?

Some RDBMS pre-compilers (Ingres and Teradata for example) allow this sort of embedded select:

    EXEC SQL SELECT *
    INTO :single_host_variable_that_is_a_structure_you_defined     FROM table;

I have not seen it mentioned in the Oracle documentation (v1.5). The practice is frowned on in general because your code can needlessly break if new columns are added to the table, or if columns that you don't actually use are deleted. Also, it relies on there being a defined order to the columns of a table. This is true in reality, but it offends the relational purist.

I think the best way, as dreary as it seems, is:

    EXEC SQL SELECT col2, col2, ... col36     INTO :host1, :host2, ... :host36
    FROM table;

If you don't know the number of columns you are interested in at compile time, then you must resort to an SQL descriptor area (SQLDA) and the infamous method 4 dynamic SQL.

If you want to bring back multiple rows, each of the host variables can be declared as arrays.

>We've considered using a stored procedure, but haven't found the correct
>host-variable datatype to use as a parameter to receive a cursor%ROWTYPE.

I would claim that you haven't found the correct host-variable datatype because there isn't one. Received on Tue Nov 01 1994 - 23:37:17 CET