1

Well, I want to insert data from a table into a nested table.

    SET SERVEROUTPUT ON;
DECLARE
  TYPE employee_type IS TABLE OF employees.salary%TYPE;
  emps employee_type;
  i NUMBER(5) := 100;
  sal employees.salary%TYPE;

BEGIN
LOOP
SELECT salary INTO sal FROM employees WHERE employees.employee_id = i;
    emps := employee_type(sal);
    i := i + 1;

EXIT WHEN i > 110;
END LOOP;
SYS.DBMS_OUTPUT.PUT_LINE(emps.count);

This is my code but when I do SYS.DBMS_OUTPUT.PUT_LINE(emps.count); I only get 1 but it should be 10 actually

1
  • This is not a nested table; it as simply a collection. A nested table is when you have a collection stored (nested) within an SQL table. Commented Oct 11, 2020 at 19:09

1 Answer 1

2

This:

emps := employee_type(sal);

initializes the collection with a single element containing the salary. Every time you do this, you erase the existing collection and replace it with a new one consisting of one element only again.

Instead, initialize the collection once and then extend it element by element:

DECLARE
  TYPE employee_type IS TABLE OF employees.salary%TYPE;
  emps employee_type := employee_type(); -- initializing the array right away
  i NUMBER(5) := 100;
  sal employees.salary%TYPE;
BEGIN
  LOOP
    SELECT salary INTO sal FROM employees WHERE employees.employee_id = i;
    emps.EXTEND(1); -- add one element
    emps(emps.COUNT) := sal; -- fill that element
    i := i + 1;
    EXIT WHEN i > 110;
  END LOOP;

  SYS.DBMS_OUTPUT.PUT_LINE(emps.count);
END;

It is much easier though to bulk collect the values into the array (which also implicitly initialized the array).

DECLARE
  TYPE employee_type IS TABLE OF employees.salary%TYPE;
  emps employee_type;
  i NUMBER(5) := 100;
BEGIN
  SELECT salary BULK COLLECT INTO emps 
  FROM employees 
  WHERE employee_id BETWEEN i AND 110;

  SYS.DBMS_OUTPUT.PUT_LINE(emps.count);
END;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.