records not returned

[chphang]

hi! i've the following codes:
...
For i in 1..4 loop
gp:='pollquestion_rec.Option'| |i ;
htp.p(gp);
htp.p('
');
end loop;
...

the output result is:
pollquestion_rec.Option1
pollquestion_rec.Option2
pollquestion_rec.Option3
pollquestion_rec.Option4

which is not what i wanted.

but if i actually type in
htp.p(pollquestion_rec.Option1);
...
htp.p(pollquestion_rec.Option4);

the actual output is the records, which is what i wanted.

my table contain the fields question,Option1, Option2, Option3, Option4, and in future may be up to Option10. that's why i'm thinking of using a loop to output.

kindly advise.

thx.

 

[Robertio]

I'm assuming that pollquestion_rec.Option1 is not of type string. In which case gp should not be either, the type of gp should be the same as pollquestion_rec.Option1.

You will probably have to use something like findcomponent to find the component (each time in your loop) where the components name is 'pollquestion_rec.Option'| |i

 

[chphang]

the pollquestion_rec is declared as cursor%rowtype.

so how should i declare the gp?

thx

 

[Marco Kalter]

I don't think you can do this in a loop. You have to explicitly address each specific OptionX field.

------------------
Marco Kalter
Allround Automations

 

[chphang]

thx for the advice.

 

[jpickup]

You could always use dbms_sql or execute immediate.

e.g.

For i in 1..4 loop
gp:='select pollquestion_rec.Option' | | i | | ' from dual';
execute immediate gp into v;
htp.p(v);
htp.p('
');
end loop;

 

[chphang]

hi! thx for the advice. tried your codes and return the error upon running as portlet (error page if run normally):

Error: ORA-00904: invalid column name (WWV-11230)
The preference path does not exist: ORACLE.WEBVIEW.PARAMETERS.14469512450 (WWC-51000)
The preference path does not exist: ORACLE.WEBVIEW.PARAMETERS.14469512450 (WWC-51000)

if i change the ' from dual' to 'from pollquestion.rec' it returns a pl/sql numeric error.

rgds.

 

[jpickup]

The example given is clearly not complete. For one thing you would need to declare a variable V of the appropriate type based on your table definition. The technique does work, perhaps you should familiarise yourself with it using some simpler examples.

Your database design should be improved so that it can cope with expected future requirements without changes to the schema.

Using dynamic SQL does have its place. However in this case it sounds like you are wanting to use it to cover flaws in a bad design.

 

[chphang]

hi! thx for the advice. your guess is somewhat correct- the max Option is probably 5 or 6, thus i was wondering should i open another table just for that etc. - should i?

i've declared the v as varchar2, is that wrong?

thx.

 

[jpickup]

it'll probably be easier not to use a cursor in this situation (I assume you've got a cursor with something like 'select * from options'). If instead you dynamically build 'select option1 from options', etc. in a loop then the earlier example should work.

But, I would create another table so you don't need the dynamic SQL. This will certainly be a lot easier for others to understand in future and should be much easier to maintain.

 

[chphang]

thx a lot for the advice.