×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Oracle Supplied Packages

How can I return array like structures from functions or stored procedures by Swany
Posted: 21 Jun 01

If you need to return dynamic amounts of data, you probably want to use PL/SQL tables.  A PL/SQL table is similar to an array, but it can have only one bound.  

PL/SQL tables are sparse.  That is if you write to index 1 and index 10, only two values are stored in memory.  Oracle doesn't store NULL values for indexes 2 through 9.  If you reference an index that has not been stored yet, you will get a NO_DATA_FOUND exception.

You need to define a TYPE to hold your data before you can use a table.  You do that with the oracle TYPE statement.
<type_name> IS TABLE OF ORACLE_DATA_TYPE INDEX BY BINARY_INTEGER;

You can declare the type anywhere declarations are valid.  You can put them in a package header or in a DECLARE section.  Putting them in a package header makes them sharable between packages and easily reusable.  I always create a package header that defines useful table structures an I call it TABLE_TYPES

CREATE OR REPLACE PACKAGE TABLE_TYPES
AS
  TYPE tNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE tString IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
END;

If you put the table definitions into their own package you don't have to worry about cluttering up all your other packages with useless table definitions.  Notice, I use the TABLE_TYPES package in the package example below.

When you create a table variable, you can place data into it sparsely, as I said before..  Take this anonymous block for example.

DECLARE
  tab TABLE_TYPE.tNumber;
BEGIN
  tab(1) := 30;
  tab(30) := 1;
  tab(2) := tab(30) - tab(1);

  DBMS_OUTPUT.ENABLE();
  DBMS_OUTPUT.PUT_LINE('Index two contains: ' || tab(2));
END;


If you run the above anonymous block you should get the output:

Index two contains: -29

PL/SQL procedure successfully completed.

if you only see the last line, type set serveroutput on before running the anonymous block.

The COUNT property of a table returns the total number of rows that are found in that table.  If the table is defined sparesly, unfilled rows ARE NOT INCLUDED.  In the above anonymous block tab.COUNT will be THREE.



1. CREATE OR REPLACE PACKAGE EXAMPLE_PKG AS
2. FUNCTION myFunction() RETURN TABLE_TYPES.tNumber;
3. PRAGMA RESTRICT_REFERENCES(myFunction,WNDS);
4. END;
5. /

And here is the packge body

1.  CREATE OR REPLACE PACKAGE BODY EXAMPLE_PKG AS
2.  FUNCTION myFunction() RETURN tNumber
3.  -- this function returns a table of numbers 1 to 10
4.  IS
5.    example_table TABLE_TYPES.tNumber;
6.  BEGIN
7.    FOR indx IN 1 .. 10
8.    LOOP
9.      example_table(indx) := i;
10.   END LOOP;
11. END;
12. /


Finally, we'll create an anonymous block that calls the myFunction function, gets a table of results and displays them on the screen.


1. DECLARE
2.  tab TABLE_TYPES.tNumber;
3. BEGIN
4.   tab := EXAMPLE_PKG.myFunction();
5.   DBMS_OUTPUT.ENABLE();
6.   for i in 1 .. tab.COUNT
7.   LOOP
8.      DBMS_OUTPUT.PUT_LINE('Row ' || i || '. value = ' || tab(i));  
9.  END LOOP;
10.END;
  

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close