Solution of ORA-06502

When you work on associative arrays in Oracle you must consider key lengths. When you create an associative arrays with 4 chars length, it means that you canuse key names up to 4 letters.If you convert keys expilicitly with to_char built-in function, you must trim to eliminate spaces; otherwise you are a potential person that get ORA-06502 error as i demonstrated below:
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> set serverout on 5000
SQL>
SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(3);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := to_char(i, '000');
  9    vt_List(k) := 'Test item...';
 10    dbms_output.put_line(vt_List(k));
 11  END;
 12  /

DECLARE
  i NUMBER;
  k VARCHAR2(3);
  TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  vt_List t_StringHashTable;
BEGIN
  i := 1;
  k := to_char(i, '000');
  vt_List(k) := 'Test item...';
  dbms_output.put_line(vt_List(k));
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(1000);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := to_char(i, '000');
  9    dbms_output.put_line(length(k));
 10    dbms_output.put_line('k is "' || k || '"');
 11    vt_List(k) := 'Test item...';
 12    dbms_output.put_line(vt_List(k));
 13  END;
 14  /

4
k is " 001"

DECLARE
  i NUMBER;
  k VARCHAR2(1000);
  TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  vt_List t_StringHashTable;
BEGIN
  i := 1;
  k := to_char(i, '000');
  dbms_output.put_line(length(k));
  dbms_output.put_line('k is "' || k || '"');
  vt_List(k) := 'Test item...';
  dbms_output.put_line(vt_List(k));
END;

ORA-06502: PL/SQL: numeric or value error: associative array key violates its type constraints
ORA-06512: at line 11

SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(3);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := TRIM(to_char(i, '000'));
  9    vt_List(k) := 'Test item...';
 10    dbms_output.put_line(vt_List(k));
 11  END;
 12  /

Test item...

PL/SQL procedure successfully completed

SQL> DECLARE
  2    i NUMBER;
  3    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  4    vt_List t_StringHashTable;
  5  BEGIN
  6    i := 1;
  7    vt_List(i) := 'Test item...';
  8    dbms_output.put_line(vt_List(i));
  9  END;
 10  /

Test item...

PL/SQL procedure successfully completed

SQL> 

On Associative Arrays, Nested Tables and Varrays In Oracle

Oracle has three different types of collections. I mentioned them in one of my previus posts. You can take more information there.
Now, i want to give examples how to use them:

SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(16);
  3    occupations occupation_table;
  4    k           VARCHAR2(16);
  5  BEGIN
  6    occupations('One') := 'Architecture';
  7    occupations('Two') := 'Engineer';
  8    k := occupations.FIRST;
  9    WHILE k IS NOT NULL LOOP
 10      dbms_output.put_line('occupations(''' || k || ''') is ' || occupations(k));
 11      k := occupations.NEXT(k);
 12    END LOOP;
 13  END;
 14  /
occupations('One') is Architecture
occupations('Two') is Engineer
PL/SQL procedure successfully completed
SQL>

2. Varrays
SQL> create type t_telephone_numbers is varray(2) of varchar2(10);
Type created
SQL> create table workers( name varchar2(16), telephone_list t_telephone_numbers );
Table created
SQL> INSERT INTO workers VALUES('Mennan',t_telephone_numbers('2122122122', '2122221133') );
1 row inserted
SQL> SELECT * FROM workers;
NAME             TELEPHONE_LIST
---------------- --------------
Mennan           [object]
SQL> SELECT * FROM table(SELECT telephone_list FROM workers WHERE name = 'Mennan'  );
COLUMN_VALUE
------------
2122122122
2122221133
SQL>

3. Nested Tables
SQL> create type t_hobbies is table of varchar2(16);
  2  /
Type created
SQL> create table workers( name varchar2(16), hobbies t_hobbies )
  2    nested table hobbies store as workers_hobbies;
Table created
SQL> SELECT table_name, nested FROM user_tables WHERE table_name like 'WORKERS%';
TABLE_NAME                     NESTED
------------------------------ ------
WORKERS                        NO
WORKERS_HOBBIES                YES
SQL> INSERT INTO workers VALUES('Mennan',t_hobbies('Reading Book', 'Listening') );
1 row inserted
SQL> INSERT INTO workers VALUES('Ali',t_hobbies('Swimming', 'Coding', 'Football') );
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM workers;
NAME             HOBBIES
---------------- -------
Mennan           [object]
Ali              [object]
SQL> SELECT * FROM workers_hobbies;
SELECT * FROM workers_hobbies
ORA-22812: cannot reference nested table column's storage table
SQL> SELECT * FROM table(SELECT hobbies FROM workers WHERE name = 'Mennan'  );
COLUMN_VALUE
----------------
Reading Book
Listening
SQL> INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') );
INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') )
ORA-12899: value too large for column "HR"."WORKERS_HOBBIES"."COLUMN_VALUE" (actual: 20, maximum: 16)
SQL> alter type t_hobbies modify element type varchar2(8) cascade;
alter type t_hobbies modify element type varchar2(8) cascade
ORA-22324: altered type has compilation errors
ORA-22328: object "HR"."T_HOBBIES" has errors.
PLS-00729: only widening of the collection element type is allowed
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
SQL> alter type t_hobbies modify element type varchar2(64) cascade;
Type altered
SQL> INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') );
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM table(SELECT hobbies FROM workers WHERE name = 'Ayse'  );
COLUMN_VALUE
----------------------------------------------------------------
Reading Horror Books
SQL>

Bulk Processing Performance Analysis On Oracle

Analyzing performance statistics is very common procedure for oracle programmers. You can always do the same work with various ways. At this point you have to choose the most suitable one for your business. Especally this suitability stands on performance. Your boss wants to you for making prosceses faster ans faster. So, you have to analyze some performance statistics when doing some jobs.
If you are a good oracle programmer, you will always make your code "faster". The boss will pay more for this velocity. If you are always do your work "faster" you will never loss your job :)
Bulk processing comes in action when you are doing insert or "select" heavily. For instance if you are inserting 10000 row in pl/sql, there will be a context switching between two engines, PL/SQL and SQL engines. A context switch has a time payload. For this reason, to get more performant programs, you have to consider this.
For bulk processing you have to work with collections.
Below i demonstrated a simple example to show performance effects of bulk and nonbulk process. The demonstration was repeated with 50000, 100000 and 250000 rows.
The result is

	Bulk Insert	Insert	%	Bulk Select	Select	%
50000	0,321	4,026	1154,21	0,11	0,17	54,55
100000	0,821	7,521	816,08	0,191	0,27	41,36
250000	0,941	21,891	2226,35	0,431	0,651	51,04

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create type number_list is table of number;
  2  /
Type created
Executed in 0,251 seconds
SQL> create table numbers(i number );
Table created
Executed in 0,091 seconds
SQL> BEGIN
  2    FOR i IN 1 .. 50000 LOOP
  3      INSERT INTO numbers values(i);
  4    END LOOP;
  5  END;
  6  /
PL/SQL procedure successfully completed
Executed in 4,026 seconds
SQL> commit;
Commit complete
Executed in 0,24 seconds
SQL> DECLARE
  2    total NUMBER := 0;
  3  BEGIN
  4    FOR rec IN (SELECT i FROM numbers) LOOP
  5      total := total + rec.i;
  6    END LOOP;
  7    dbms_output.put_line('Total is ' || total);
  8  END;
  9  /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,17 seconds
SQL> drop table numbers;
Table dropped
Executed in 0,04 seconds
SQL> create table numbers(i number);
Table created
Executed in 0,05 seconds
SQL> DECLARE
  2    number_arr number_list;
  3  BEGIN
  4    number_arr := number_list();
  5    number_arr.EXTEND(50000);
  6    FOR i IN 1 .. 50000 LOOP
  7      number_arr(i) := i;
  8    END LOOP;
  9    FORALL i IN number_arr.FIRST .. number_arr.LAST
 10      INSERT INTO numbers VALUES (number_arr(i));
 11 
 12  END;
 13  /
PL/SQL procedure successfully completed
Executed in 0,321 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> DECLARE
  2    total      NUMBER := 0;
  3    number_arr number_list;
  4  BEGIN
  5    number_arr := number_list();
  6    SELECT i BULK COLLECT INTO number_arr FROM numbers;
  7    FOR i IN number_arr.FIRST .. number_arr.LAST LOOP
  8      total := total + number_arr(i);
  9    END LOOP;
 10    dbms_output.put_line('Total is ' || total);
 11  END;
 12  /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,11 seconds

BULK COLLECT INTO Collection Via SQL Statements In Oracle

Getting all data once from a table with a bulk collect instead of cursor is more useful. Once you get all data and you work on it. When bulk collecting into collection type you must consider some points

• You must create a global type for collections. It is not possible to use local types in SQL statements.
• Oracle can not convert scala types to reference types. You must explicitly convert collection type when bulk collecting.

There is a simple demonstration to show this.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR

SQL>
SQL> drop type employee_tab;

drop type employee_tab

ORA-04043: object EMPLOYEE_TAB does not exist

SQL> drop type employee_obj;

Type dropped

Executed in 0,047 seconds

SQL> CREATE OR REPLACE TYPE employee_obj IS OBJECT
  2  (
  3    full_name       VARCHAR2(64),
  4    department_name VARCHAR2(32),
  5    job_name        VARCHAR2(32)
  6  );
  7  /

Type created

Executed in 0,031 seconds

SQL> CREATE OR REPLACE TYPE employee_tab IS TABLE OF employee_obj;
  2  /

Type created

Executed in 0,031 seconds

SQL> DECLARE
  2    all_employees employee_tab;
  3  BEGIN
  4    SELECT e.first_name || ' ' || e.last_name, d.department_name, j.job_title BULK COLLECT
  5      INTO all_employees
  6      FROM employees e, departments d, jobs j
  7     WHERE e.department_id = d.department_id
  8       AND e.job_id = j.job_id;
  9    dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
 10  END;
 11  /

DECLARE
  all_employees employee_tab;
BEGIN
  SELECT e.first_name || ' ' || e.last_name, d.department_name, j.job_title BULK COLLECT
    INTO all_employees
    FROM employees e, departments d, jobs j
   WHERE e.department_id = d.department_id
     AND e.job_id = j.job_id;
  dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
END;

ORA-06550: line 6, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> DECLARE
  2    all_employees employee_tab;
  3  BEGIN
  4    SELECT employee_obj(e.first_name || ' ' || e.last_name, d.department_name, j.job_title) BULK COLLECT
  5      INTO all_employees
  6      FROM employees e, departments d, jobs j
  7     WHERE e.department_id = d.department_id
  8       AND e.job_id = j.job_id;
  9    dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
 10  END;
 11  /

Employees Count : 106

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL>

Performance Tests On Iterating Collections In Oracle

There are some ways to iterate collections on Oracle. One is simple for loop, the other is FIRST-NEXT method. On performance metrics it is better to use for loop, it gains time. FIRST-NEXT method is simply linked list. You take first index and then you take the next index.

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    FOR j IN 1 .. 1000000 LOOP
  7      FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8        NULL;
  9      END LOOP;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed

Executed in 0,172 seconds

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    FOR j IN 1 .. 1000000 LOOP
  8      k := occupations.FIRST;
  9      WHILE k IS NOT NULL LOOP
 10        k := occupations.NEXT(k);
 11      END LOOP;
 12    END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 1,359 seconds

There are some situations you can not use simple for loop. When you delete in collection or collection subscripts are not incremented sequentially by one you have to use second way. And if your subscripts are not number, you again use second way.


SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    occupations.DELETE(2);
  7    FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8      dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  9    END LOOP;
 10  END;
 11  /

occupations(1) is Salesman

DECLARE
  TYPE occupation_table IS TABLE OF VARCHAR2(16);
  occupations occupation_table;
BEGIN
  occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  occupations.DELETE(2);
  FOR i IN occupations.FIRST .. occupations.LAST LOOP
    dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  END LOOP;
END;
ORA-01403: no data found
ORA-06512: at line 8

SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    occupations.DELETE(2);
  8    k := occupations.FIRST;
  9    WHILE k IS NOT NULL LOOP
 10      dbms_output.put_line('occupations(' || k || ') is ' || occupations(k));
 11      k := occupations.NEXT(k);
 12    END LOOP;
 13  END;
 14  /

occupations(1) is Salesman
occupations(3) is Engineer
occupations(4) is Teacher
occupations(5) is Architect

PL/SQL procedure successfully completed

Executed in 0,015 seconds

SQL>

Storing Collections In Oracle Database

Oracle nin SQL dili olan PL/SQL'de birçok özellik bulunmaktadır. Bunlardan biri de collection'lardır. En temel anlamda collection, dizi veya küme demektir. PL/SQL ile oluşturulan collectionları Oracle üzerinde nested table şeklinde saklayabilirsiniz. Bu, varolan bir tablo içinde başka bir tablo oluşturma anlamına gelmektedir. İsterseniz bu tabloyu, veritabanı içinde ayrı bir yerde isterseniz de o tablo içinde oluşturabilirsiniz.

Collection tiplerini veritabanında tutmak yerine ayrı bir tablo yapıp tutmak da isteyebilirsiniz. Bu, birçok programcının yaptığı durumdur. Bu şekilde DML işlemleirini daha zahmetsiz halledersiniz. Collection tiplerinin faydası, içinde tuttuğunuz verinin düzenini sağlamasıdır. Yani collection içinde ilk elemanınız neyse her zaman ilk elemanınız o olacaktır. Bundan başka collection tiplerinin single-statement fetching ile alınıp daha hızlı işlenceği de belirtilebilir.

Sonuç olarak bunu kullanmak veya kullanmamak sizin elinizde. PL/SQL'in bunu desteklediğini bilmeniz bile size fayda sağlayacaktır.


Aşağıda bir collection tipinin veritabanında saklanması olayının örnek bir senaryosu bulunmaktadır. Kod bloğu, bazı özelliker içermesi bakımından önemlidir:

drop işlemleri:
drop type employee_tab;
drop type employee_obj;
drop table company;

Nesne oluşturulması
CREATE OR REPLACE TYPE employee_obj IS OBJECT
(
  full_name       VARCHAR2(64),
  department_name VARCHAR2(32),
  job_name        VARCHAR2(32)
);  

Nesne dizisi(collection) oluşturulması
CREATE OR REPLACE TYPE employee_tab IS TABLE OF employee_obj;

Collection tipinde bir kolonu bulunan tablonun oluşturulması. Bu collection veritaanı içinde ayrı bir yerde saklanacaktır.
create table company( id number, open_date date, employees employee_tab)
nested table employees store as employees_nt;

Saklandığının gösterilmesi
SELECT * FROM user_objects WHERE object_name = 'EMPLOYEES_NT';

Ekleme işleminin yapılması. Normal ekleme şeklinde değil, collection ları kabul edecek şekilde eklenme
INSERT INTO company
VALUES
  (1,
   SYSDATE,
   employee_tab(employee_obj('Anrew Kill', 'HR', 'HR Director'),
                employee_obj('Maria Born', 'HR', 'HR Asistant'),
                employee_obj('Ted Borry', 'IT', 'IT Manager')));
INSERT INTO company
VALUES
  (2,
   SYSDATE,
   employee_tab(employee_obj('Mariana Polii', 'IT', 'IT Director')));

Tablonun select edilmesi
SELECT employees FROM company WHERE id = 2;
--Mariana Polii    IT    IT Director

Değişiklik yapılması
DECLARE
  ind            NUMBER;
  employees_list employee_tab;
  CURSOR employees_cur IS
    SELECT employees FROM company WHERE id = 2;
BEGIN
  OPEN employees_cur;
  FETCH employees_cur
    INTO employees_list;--Single-statament assignment
  CLOSE employees_cur;

  ind := employees_list.FIRST;--ilk elemanın indisi
  WHILE ind IS NOT NULL LOOP--elemanlar bitinceye kadar
    IF employees_list(ind).department_name = 'IT' THEN
      employees_list(ind).department_name := 'Information Tech';
    END IF;
    ind := employees_list.NEXT(ind);--sonraki elemanın indisi
  END LOOP;
  UPDATE company SET employees = employees_list WHERE id = 2;--güncelleme
END;

Değişikliğin kontrol edilmesi
SELECT employees FROM company WHERE id = 2;
--Mariana Polii    Information Tech    IT Director