CODEWITHSUNDEEP
oracle-databasetypesplsql
Curious
Curious

Reputation: 921

plsql table type with index of is complaining

Pl/SQL: Intent: My intent was to access employee tuple object defied as cursor below by using key as the employee_id.

Problem: I created a cursor - *l_employees_cur* and want to create type table as below type *l_employees_t*, as below but the compiler is complaining saying that PLS-00315 implementation restriction unsupported table index type. 

CURSOR l_employees_cur
  IS
    SELECT employee_id,manager_id,first_name,last_name FROM employees;
type l_employees_t
IS
  TABLE OF l_employees_cur%rowtype INDEX BY employees.employee_id%TYPE;

The definition of employees.employee_id is:

EMPLOYEE_ID    NUMBER(6) NOT NULL

why can't I do this ? or Am I doint something wrong.

Upvotes: 3

Views: 8308

Answers (3)

Richard Pascual
Richard Pascual

Reputation: 2021

Storing and Retreiving SQL Query Output in a PL/SQL Collection

The example in the OP looks a lot like Oracle's new sample HR data schema. (For those old-timers who know, the successor to the SCOTT-TIGER data model). This solution was developed on an Oracle 11g R2 instance.

The Demo Table Design - EMP

EMP table design

Demonstration Objectives

This example will show how to create a PL/SQL collection from an object TYPE definition. The complex data type is derived from the following cursor definition:

 CURSOR l_employees_cur IS
    SELECT emp.empno as EMPLOYEE_ID, emp.mgr as MANAGER_ID, emp.ename as LAST_NAME
      FROM EMP;

After loading the cursor contents into an index-by collection variable, the last half of the stored procedure contains an optional step which loops back through the collection and displays the data either through DBMS_OUTPUT or an INSERT DML operation on another table.

Stored Procedure Example Source Code

This is the stored procedure used to query the demonstration table, EMP.

 create or replace procedure zz_proc_employee is

      CURSOR l_employees_cur IS
         SELECT emp.empno as EMPLOYEE_ID, emp.mgr as MANAGER_ID, emp.ename as LAST_NAME
           FROM EMP;
 
      TYPE employees_tbl_type IS TABLE OF l_employees_cur%ROWTYPE INDEX BY PLS_INTEGER;  

      employees_rec_var   l_employees_cur%ROWTYPE;
      employees_tbl_var   employees_tbl_type;

      v_output_string     varchar2(80);
      c_output_template   constant   varchar2(80):= 
         'Employee: <<EMP>>; Manager: <<MGR>>; Employee Name: <<ENAME>>'; 

      idx       integer;
      outloop   integer;

 BEGIN
      idx:= 1;
      OPEN l_employees_cur;
      FETCH l_employees_cur INTO employees_rec_var;

         WHILE l_employees_cur%FOUND LOOP
        
         employees_tbl_var(idx):= employees_rec_var;

         FETCH l_employees_cur INTO employees_rec_var;        

         idx:= idx + 1;
      END LOOP;

      CLOSE l_employees_cur;

 -- OPTIONAL (below) Output Loop for Displaying The Array Contents
 -- At this point, employees_tbl_var can be handed off or returned
 -- for additional processing.

 FOR outloop IN 1 .. idx LOOP
      -- Build the output string: 

      v_output_string:= replace(c_output_template, '<<EMP>>', 
          to_char(employees_tbl_var(outloop).employee_id));

      v_output_string:= replace(v_output_string, '<<MGR>>', 
          to_char(employees_tbl_var(outloop).manager_id));

      v_output_string:= replace(v_output_string, '<<ENAME>>', 
          employees_tbl_var(outloop).last_name);

      -- dbms_output.put_line(v_output_string);

      INSERT INTO zz_output(output_string, output_ts)
          VALUES(v_output_string, sysdate);

      COMMIT;

 END LOOP;

 END zz_proc_employee;

​

I commented out the dbms_output call due to problems with the configuration of my server beyond my control. The alternate insert command to a output table is a quick way of visually verifying that the data from the EMP table found its way successfully into the declared collection variable.

Results and Discussion of the Solution

Here is my output after calling the procedure and querying my output table:

EMP Collection Extracted Output Results

While the actual purpose behind the access to this table isn't clear in the very terse detail of the OP, I assumed that the first approach was an attempt to understand the use of collections and custom data types for efficient data extraction and handling from structures such as PL/SQL cursors.

The portion of this example procedure is very reusable, and the initial steps represent a working way of making and loading PL/SQL collections. If you notice, even if your own version of this EMP table is different, the only place that requires redefinition is the cursor itself.

Working with types, arrays, nested tables and other collection types will actually simplify work in the long run because of their dynamic nature.

Upvotes: 2

wieseman
wieseman

Reputation: 176

I think that your mistake is the declaration of the plsql table.

Why don't you try the next one:

type l_employees_t
IS
  TABLE OF l_employees_cur%rowtype INDEX BY pls_integer;

I also have a question for you: What is the meaning of EMPLOYEE_ID NOT NULL NUMBER(6) in your code above?

Greetings Carlos

Upvotes: 2

OldProgrammer
OldProgrammer

Reputation: 12169

From the Oracle Documenation:

Associative Arrays

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

Upvotes: 5

Related Questions