Looping through elements of a record in PL/SQL

[b0nkers]

I'm trying to write a generic proc that can loop through the elements in a single record, concatenating each to a variable. An example will help :

TYPE T_OUTPUT_ROW IS RECORD (
v_Field_01 CHAR(3),
v_Field_02 CHAR(50));
r_OutputRow T_OUTPUT_ROW;
...
LOOP
FETCH c_Select
INTO r_OutputRow;
...
FOR i in 1.. XXX LOOP
v_Line := v_Line || r_OutputRow.YYY;
END LOOP;

Can this be done? - ie. the XXX and YYY "bits" above. I want to be able to specify my record in on place - in the type declaration (this will change across lots of similar procs), and then not have to change anything else in the procs, so loop through all elements in the record (the XXX above) and somehow reference the field name or number to spit it out into the OutputLine.

Am I going about this in the wrong way? Any thoughts would help - I know what I want to do but can't find a way of actually making it happen!

cheers,
George

 

[vc123]

No, it cannot be done. You can access record fields only by name.

Rgds.

 

[SantaMufasa]

B0nkers and VC,

What b0nkers wants certainly can occur (writing a generic procedure to concatenate all columns of any table together to output as a concatenated string without hard coding the name of the table or its columns). In fact, you can get the output either strung together horizontally or printed vertically (with column labels).

The reason I cannot provide you with the code presently is because I am scheduled to dress up as "Santa Clause"/"Father Christmas"/"Papa Noel"/"St. Nicholas" in about 3 hours, so I must get ready for that. So, BOnkers, if you can wait until later (this evening my time, or tomorrow) I can provide you the code you want.

Please confirm precisely how you want the output to appear by posting a SIMPLE table definition with sample input and sample resulting output. The resulting code, however, will work for any table with any columns. [LONG columns, however, are a bit more of a hassle, but can be done with additional coding; please forgo LONGs if not necessary for your application.]

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:47 (11Dec03) GMT, 11:47 (11Dec03) Mountain Time)

 

[vc123]

Well, I am sorry to say but you are wrong:

1. His specific question was whether he can iterate over the record fields (as he showed in his pseudo-code). The answer to this question is NO, Orace does not offer any syntax, in PL/SQL, to loop over a record's fields.

2. Now , if he wants to describe a generic recordset, the answer is YES, he can use the dbms_sql package. An outline would be:

declare
l_cursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000) default 'select * from ' || <table_name>;
l_colCnt number := 0;
l_descTbl dbms_sql.desc_tab;

begin
dbms_sql.parse( l_cursor, l_query, dbms_sql.native );
dbms_sql.describe_columns(l_cursor, l_colCnt, l_descTbl);

for i in 1 .. l_colCnt loop
dbms_sql.define_column( l_cursor, i, l_columnValue, 4000 );
end loop;
....
l_status := dbms_sql.execute(l_cursor);
while ( dbms_sql.fetch_rows(l_cursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value(l_cursor, i, l_columnValue );
... do something with l_columnValue
end loop;
end loop;
........
dbms_sql.close_cursor(l_cursor);
end;

But, that's an entirely different question, right ?

Rgds.

 

[SantaMufasa]

VC,

I generally try to use my crystal ball to devine a poster's objective and solve &quot;want&quot; rather than specifically answer what may be an incomplete question. You are certainly correct that there is no &quot;on-board&quot; syntax to do precisely what he is SAYING, but as you very capably showed, there is syntax to do what he is WANTING. That's because you remembered in SQL*Plus to &quot;set dwimnwis on&quot;...That's the &quot;Do What I Meant, Not What I Said&quot; option.

Well done !

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:52 (11Dec03) GMT, 13:52 (11Dec03) Mountain Time)

 

[vc123]

Sorry, no offence meant -- just wanted to answer the specific question.

My apologies and all that...

Rgds.

 

[SantaMufasa]

VC,

No offense taken; no apology needed. This stuff is too much fun to ever take it too seriously.

Best Holiday wishes,

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:24 (11Dec03) GMT, 16:24 (11Dec03) Mountain Time)

 

[b0nkers]

Awesome stuff - thanks guys!

I may not have made my question open enough - VC's solution does exactly what I want.

cheers!