Re: Variable fieldname in PL/SQL

On Thu, 12 Jun 2003 14:11:28 +0200, "Thomas Stuefer" <stuefer_at_halli-data.at> wrote:

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

The best solution would be using the copy and paste functionality of your editor, or setting up a table with a VARRAY instead of this denormalized design. Dynamic sql is support in sql statements only there is no dynamic pl/sql

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jun 12 2003 - 12:57:53 CDT