PL/SQL procedure - Use of "variables"

From: Birte <birtemi_at_hotmail.com>
Date: Mon, 26 Apr 2004 06:59:55 +0200
Message-ID: <eH0jc.213$Yc.1999@news4.e.nsc.no>

Hi
I have a definition table (DEF) which defines which columns to get from an input table. I am using a cursor to read from the input table (INPUT). Is there any way to get hold of the correct data from the INPUT table?

create table DEF ( cDEFINITIONNAME Varchar2(32) not null,

        nFILEFIELDNUMBER NUMBER not null,
        cFILEFIELDNAME   Varchar2(30) not null,
        cTABLEFIELDNAME  Varchar2(30) not null);

insert into
DEF(cDEFINITIONNAME,nFILEFIELDNUMBER,cFILEFIELDNAME,cTABLEFIELDNAME) values
('ABC',3,'MEDLNR','Field1 to get');

insert into
DEF(cDEFINITIONNAME,nFILEFIELDNUMBER,cFILEFIELDNAME,cTABLEFIELDNAME) values
('ABC',2,'LEILNR','Field2 to get);

The nFileFieldNumber is correspondent to INPUT table cValueX, here cValue3 and cValue2

Create table INPUT( nCounter number not null,

                                 cValue1  varchar2(255) null,
                                 cValue2  varchar2(255) null,
                                 cValue3  varchar2(255) null,
                                 cValue4  varchar2(255) null,
                                 cValue5  varchar2(255) null);

id1 OUTPUT.id1%TYPE;
id2 OUTPUT.id2%TYPE;

  CURSOR INDEF IS
    Select * from DEF where cDEFINITIONNAME='ABC';

  CURSOR Tempdata IS

     SELECT * FROM INPUT; BEGIN /* I will find the columnnames to use to select from the table IN */

    For Definisjon IN INNLESINGDEF LOOP

       IF Definisjon.cTABLEFIELDNAME = 'Field1 to get' THEN
          lcID1 := 'inn.cValue'|| Definisjon.nFILEFIELDNUMBER;
       ELSIF Definisjon.cTABLEFIELDNAME = 'Field2 to get' THEN
          lcID2 := 'inn.cValue'|| Definisjon.nFILEFIELDNUMBER;
       END IF;

    END LOOP; /* Then I have a loop reading the IN table and I need to check in another table for existing row as welll as insert the row into the OUTPUT table */

    For inn IN TEMPDATA LOOP
    BEGIN         /* I will not have the string 'inn.cValue1' but the value the curson inn is pointing to of the field cValue1 */

        Select field1 into lnContract from Checktable where field2=lcID1 and field3= lcID2;

       /* What I want: Select field1 into lnContract from Checktable where field2=inn.cValue3 and field3= inn.cValue2;

Is This possible? The reason for doing it that way is that the inport file format may change and also be different for different types. And to only have one routine for updating the database I though of doing it this way.

Any suggestions for a solution?

Any help would be greatly appreciated Received on Sun Apr 25 2004 - 23:59:55 CDT