Re: Variable fieldname in PL/SQL

stuefer_at_halli-data.at said...
> I have to read an old, imported dBase-File with the following structure:
>
> DESC flugstat
>
> Name Type
> ---------- ------------
> FLUG VARCHAR2(8)
> SN1 NUMBER(8)
> SN2 NUMBER(8)
> SN3 NUMBER(8)
> SN4 NUMBER(8)
> SN5 NUMBER(8)
> SN6 NUMBER(8)
> SN7 NUMBER(8)
> SN8 NUMBER(8)
> SN9 NUMBER(8)
> SN10 NUMBER(8)
> SN11 NUMBER(8)
> SN12 NUMBER(8)
>
>
> I must read the whole table with a cursor and don't want check every of the
> 12 fields (SN1 to SN12) separately, but with variable fieldname. How can I
> do this in the IF-statement in the following PL/SQL-script ?
>
> DECLARE
> sNameVariable VARCHAR2(4);
>
> BEGIN
> FOR recFlugstat IN (SELECT * FROM flugstat) LOOP
> FOR i IN 1 .. 12 LOOP
> sNameVariable := 'SN' || TO_CHAR(i);
>
> IF ("Value from sNameVariable") > 0 THEN
> ...
> END IF;
> END LOOP;
>
> END LOOP;
>
> END;
>
>
> Many thanks for your help !
>
> Thomas
>

I think you need to use dynamic sql. See the following Ask Tom document (url will wrap), where he shows you how to dynamically change the column name:

http://asktom.oracle.com/pls/ask/f?p=4950:8:1090762::NO::F4950_P8 _DISPLAYID,F4950_P8_CRITERIA:1348402900879,

-- 
/Karsten
DBA > retired > DBA