CODEWITHSUNDEEP
oracle-databasefor-loopplsqlconcatenation
tom
tom

Reputation: 215

Loop variable with concatenation in pl/sql

I hava a loop in pl/sql.I want to use loop variable with string as below actually array value

     FETCH REFCUR BULK COLLECT INTO MY_ARRAY;
     FOR indx IN 1 .. MY_ARRAY.COUNT LOOP
         FOR cntr IN 1..3
          LOOP
           student_rec := student_type_wr(null, null, null, null);
           student_rec.invoice_date := MY_ARRAY(indx).invoice_date;
           student_rec.service_type := MY_ARRAY(indx).type || cntr; // it gives compile error
           student_rec.amount := MY_ARRAY(indx).amount || cntr;   // it gives compile error
           student_rec.gross := MY_ARRAY(indx).gross || cntr;     // it gives compile error        
           student_recs .extend();
           student_recs(student_recs.count()) := student_rec;
         END LOOP;

        END LOOP;

My array is like this :

   TYPE a_type IS RECORD (invoice_date DATE,
                     type1 VARCHAR2(50),
                     amount1 NUMBER,
                     gross1 NUMBER,
                     type2 VARCHAR2(50),
                     amount2 NUMBER,
                     gross2 NUMBER,
                     type3 VARCHAR2(50),
                     amount3 NUMBER,
                     gross3 NUMBER,);
   TYPE TABLETYPE IS TABLE OF a_type;
   MY_ARRAY TABLETYPE;

How can i concat loop variable with array fields? I want to read MY_ARRAY(indx).type1 with MY_ARRAY(indx).type || cntr

My error is :

Error(70,66): PLS-00302: component 'TYPE' must be declared
Error(71,60): PLS-00302: component 'AMOUNT' must be declared
Error(72,66): PLS-00302: component 'GROSS' must be declared

I know my mistake MY_ARRAY(indx).type is not defined but I have to use it.Do you have any idea?

Upvotes: 0

Views: 525

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

As far as I understand your code is that you want to call your columns dynamically while as far as I know about Oracle is that you cannot do so. So you have to go with the below code -

FETCH REFCUR BULK COLLECT INTO MY_ARRAY;
     FOR indx IN 1 .. MY_ARRAY.COUNT LOOP
           student_rec := student_type_wr(null, null, null, null);
           student_rec.invoice_date := MY_ARRAY(indx).invoice_date;
           student_rec.service_type := MY_ARRAY(indx).type1;
           student_rec.amount := MY_ARRAY(indx).amount1;
           student_rec.gross := MY_ARRAY(indx).gross1;
           student_recs .extend();
           student_recs(student_recs.count()) := student_rec;
           student_rec := student_type_wr(null, null, null, null);
           student_rec.invoice_date := MY_ARRAY(indx).invoice_date;
           student_rec.service_type := MY_ARRAY(indx).type2;
           student_rec.amount := MY_ARRAY(indx).amount2;
           student_rec.gross := MY_ARRAY(indx).gross2;
           student_recs .extend();
           student_recs(student_recs.count()) := student_rec;
           student_rec := student_type_wr(null, null, null, null);
           student_rec.invoice_date := MY_ARRAY(indx).invoice_date;
           student_rec.service_type := MY_ARRAY(indx).type3;
           student_rec.amount := MY_ARRAY(indx).amount3;
           student_rec.gross := MY_ARRAY(indx).gross3;
           student_recs .extend();
           student_recs(student_recs.count()) := student_rec;
         END LOOP;

1 thing here worth trying is to use EXECUTE IMMEDIATE statement like below -

FETCH REFCUR BULK COLLECT INTO MY_ARRAY;
     FOR indx IN 1 .. MY_ARRAY.COUNT LOOP
         FOR cntr IN 1..3
          LOOP
           student_rec := student_type_wr(null, null, null, null);
           student_rec.invoice_date := MY_ARRAY(indx).invoice_date;
           EXECUTE IMMEDIATE 'student_rec.service_type := MY_ARRAY(indx).type' || cntr;
           EXECUTE IMMEDIATE 'student_rec.amount := MY_ARRAY(indx).amount' || cntr;
           EXECUTE IMMEDIATE 'student_rec.gross := MY_ARRAY(indx).gross' || cntr;
           student_recs .extend();
           student_recs(student_recs.count()) := student_rec;
         END LOOP;

        END LOOP;

Though I have never tried this before yet my knowledge about says this wouldn't work.

Upvotes: 1

Related Questions