CODEWITHSUNDEEP
oracle-databaseplsql
Sonic260
Sonic260

Reputation: 25

How do I display a nested table in Oracle - PL/SQL?

I have an assignment where I'm supposed to create a user-defined function that will return all employees after 1968. My code is as follows:

First I create a new object, called emp_dobs, to hold the employees' firstname, lastname, and date of birth, using the same data types as the original employee table:

CREATE OR REPLACE TYPE emp_dobs AS OBJECT (
    emp_fname VARCHAR2(20),
    emp_lname VARCHAR2(20),
    emp_dob DATE
);
/

Then I create emp_dobs_nested as a table of emp_dobs:

CREATE OR REPLACE TYPE emp_dobs_nested AS TABLE OF emp_dobs;
/

Lastly, I create a function that's supposed to return an emp_dobs_nested table:

CREATE OR REPLACE FUNCTION get_emp_dobs RETURN emp_dobs_nested
AS 
    dobs emp_dobs_nested;
BEGIN
    SELECT emp_dobs(firstname, lastname, birthdate) BULK COLLECT INTO dobs
    FROM employee
    WHERE birthdate < TO_DATE('01-JAN-1968', 'DD-MON-YYYY');
    RETURN dobs;
END;
/

There is a weird quirk with compiling emp_dob_nested, however, where Oracle SQL Developer will display a new tab labeled "Output Variables - Log," and only show EMP_FNAME and EMP_LNAME. Despite that, everything compiles.

Now I want to test the function and display its results to prove that it works, but when I try this:

DECLARE
    dobs emp_dobs_nested;
BEGIN
    dobs := get_emp_dobs;
    DBMS_OUTPUT.PUT_LINE(dobs);
END;
/

I get this error:

Error report -
ORA-06550: line 5, column 5:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

What am I missing here? Thanks.

Upvotes: 1

Views: 3555

Answers (4)

eifla001
eifla001

Reputation: 1157

why not a create a view instead?

CREATE VIEW emp_view
AS
SELECT firstname, lastname, birthdate
  FROM employee
 WHERE birthdate < TO_DATE('01-JAN-1968', 'DD-MON-YYYY');

You can also try this,

DECLARE
    dobs emp_dobs_nested;
    i NUMBER := 0;
BEGIN
    dobs := get_emp_dobs;
    LOOP
        i := dobs.NEXT(i);
        DBMS_OUTPUT.PUT_LINE(dobs(i).emp_fname||','||dobs(i).emp_lname||','||dobs(i).emp_dob);
        IF i = dobs.LAST THEN
           EXIT;
        END IF;
    END LOOP;
END;
/

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Another way of display is to use XML, try

DBMS_OUTPUT.PUT_LINE(XMLTYPE(dobs));

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

You can also use TABLE function to unnest a collection of objects into a relational resultset:

select * from table( get_emp_dobs )

Live demo: http://sqlfiddle.com/#!4/8cfb2/1

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You cannot pass the whole collection to DBMS_OUTPUT, rather you must loop through it and display individual columns at each index.

DECLARE
    dobs emp_dobs_nested;
BEGIN
    dobs := get_emp_dobs;
    FOR i IN 1..dobs.COUNT 
    LOOP
    DBMS_OUTPUT.PUT_LINE(dobs(i).emp_fname||','||dobs(i).emp_lname||','||dobs(i).emp_dob);
    END LOOP;
END;
/

Upvotes: 3

Related Questions