Re: Retrieving multiple rows into a Pro*C program

In article <CyM1I6.4Lr_at_wtc35a.DaytonOH.NCR.COM>, Craig L Hollister <Craig.Hollister_at_DaytonOH.NCR.COM> writes:
|> >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;

Pro*C 2.0 allows you to do exactly this. It is released with Oracle 7.1.

|>
|> 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.
|>

Something like this will be available (I believe) with Oracle 7.2. Received on Thu Nov 03 1994 - 00:43:10 CET