Re: PL/SQL structure which enables selecting into and referencing by integers

Victor Engmark wrote:

> Hello,
>
> I am having a slight problem in being able to keep my PL/SQL readable: I
> have to select a single row from a table with more than 200 columns, and
> then print them in an HTML table (over several rows).
>
> The first solution involved selecting into a table of VARCHAR2s, but
> then the "SELECT ... INTO ..." part became huge because I had to specify
> the the VARCHAR2 table with indexes, as in "SELECT a, b, ..., aaa, ...
> INTO l_cols(1), l_cols(2), ..., l_cols(200), ... This solution also
> created another problem: I reached some size boundary for the SELECT
> statement, so it had to be split into three parts to even execute.
>
> The second (present) solution is to select everything into a %ROWTYPE
> variable, but I have not been able to find a simple way of extracting
> the column data:
> - Data in records do not seem to accessible using numbered indexes
> - I believe the FORALL structure only works for tables, not records(?)
> - I cannot seem to be able to use a variable containing a column name to
> reference a column, as in "l_record.l_colname"
> - I have no idea of how to "convert" a record into a number indexed
> construct without explicitly specifying the column names
>
> Any help would be greatly appreciated.

If you have a table with more than 200 columns the problem is not your SQL ... the problem is the table. Take a serious look at Cobb and Date's rules of normalization and act on them.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)