CODEWITHSUNDEEP
oracle-databaseplsql
Potato
Potato

Reputation: 172

how to add varchar to array pl sql

I have a problem with putting variables into an array in PL/SQL

Is it possible to put it in that way? I just want to put it like in C++ or other language just iterating loop and adding values dynamically?

DECLARE
  type grades is varray(5) of integer;
  marks grades;
BEGIN
  FOR i in 1 ..10  LOOP
    marks(i):=grades(i);
    dbms_output.put_line(' Marks: ' || marks(i));
  END LOOP;
END;

I have another question, is it possible to add strings (words) to array?

DECLARE
  type namesarray is varray(10) of varchar2(10);
  names namesarray;
BEGIN
  FOR i in 1 ..10  LOOP
    marks(i):=namesarray('client' || i);
    dbms_output.put_line(' Marks: ' || marks(i));
  END LOOP;
END;

Is it possible to add to name of client + iterated number to get in result something like ("client1", "client2",..., "client10")?

Upvotes: 2

Views: 10010

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11591

Well - you've got choices here - but in all cases, if you have an array of integers, you have to assign integers into the array elements

Original

SQL> DECLARE
  2    type grades is varray(5) of integer;
  3    marks grades;
  4  BEGIN
  5    FOR i in 1 ..10  LOOP
  6      marks(i):=grades(i);
  7      dbms_output.put_line(' Marks: ' || marks(i));
  8    END LOOP;
  9  END;
 10  /
    marks(i):=grades(i);
              *
ERROR at line 6:
ORA-06550: line 6, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored

Corrected (I've capped the loop at 5, because your array is size 5)

SQL>
SQL> set serverout on
SQL> DECLARE
  2    type grades is varray(5) of integer;
  3    marks grades := grades();
  4  BEGIN
  5    FOR i in 1 ..5  LOOP
  6      marks.extend;
  7      marks(i):= i*20;
  8      dbms_output.put_line(' Marks: ' || marks(i));
  9    END LOOP;
 10  END;
 11  /
Marks: 20
Marks: 40
Marks: 60
Marks: 80
Marks: 100

PL/SQL procedure successfully completed.

If you want an unlimited size, you can use a "nested table" type as below

SQL>
SQL>
SQL> set serverout on
SQL> DECLARE
  2    type grades is table of integer;
  3    marks grades := grades();
  4  BEGIN
  5    FOR i in 1 ..20  LOOP
  6      marks.extend;
  7      marks(i):= i*20;
  8      dbms_output.put_line(' Marks: ' || marks(i));
  9    END LOOP;
 10  END;
 11  /
Marks: 20
Marks: 40
Marks: 60
Marks: 80
Marks: 100
Marks: 120
Marks: 140
Marks: 160
Marks: 180
Marks: 200
Marks: 220
Marks: 240
Marks: 260
Marks: 280
Marks: 300
Marks: 320
Marks: 340
Marks: 360
Marks: 380
Marks: 400

PL/SQL procedure successfully completed.

or alternatively, there is an "associative array" where you nominate the data type of the array index

SQL>
SQL>
SQL> set serverout on
SQL> DECLARE
  2    type grades is table of integer index by pls_integer;
  3    marks grades;
  4  BEGIN
  5    FOR i in 1 ..20  LOOP
  6      marks(i):= i*20;
  7      dbms_output.put_line(' Marks: ' || marks(i));
  8    END LOOP;
  9  END;
 10  /
Marks: 20
Marks: 40
Marks: 60
Marks: 80
Marks: 100
Marks: 120
Marks: 140
Marks: 160
Marks: 180
Marks: 200
Marks: 220
Marks: 240
Marks: 260
Marks: 280
Marks: 300
Marks: 320
Marks: 340
Marks: 360
Marks: 380
Marks: 400

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Hope that helps

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

I think you are trying for something like this.

SET SERVEROUTPUT ON
DECLARE
  type namesarray is varray(10) of varchar2(10); 
  type grades is varray(10) of integer;
  names namesarray := namesarray(); --initialization
  marks grades := grades(70,65,56,45,89,60,34,78,90,100);--initialization
BEGIN
  FOR i in 1 ..10  LOOP
    names.extend; -- append a null array element
    names(i) := 'client' || i;  --assign the value for name
    dbms_output.put_line('Client: '||names(i)||'| Marks: ' || marks(i));
  END LOOP;
END;
/

Client: client1| Marks: 70
Client: client2| Marks: 65
Client: client3| Marks: 56
Client: client4| Marks: 45
Client: client5| Marks: 89
Client: client6| Marks: 60
Client: client7| Marks: 34
Client: client8| Marks: 78
Client: client9| Marks: 90
Client: client10| Marks: 100


PL/SQL procedure successfully completed.

Upvotes: 0

Related Questions