Home » SQL & PL/SQL » SQL & PL/SQL » function to return rowtype is it possible
function to return rowtype is it possible [message #12943] Sat, 12 June 2004 17:04 Go to next message
Roger Simms
Messages: 14
Registered: May 2004
Junior Member
I am trying to create a package that has functions that return a a row so that I can get any part of the row once I have passed it into a variable but I dont know if it is possible using

myvar := getrow.perid;

The funtion below erro's saying that the rowtype must be declared. Is there anyway around this

CREATE OR REPLACE FUNCTION get_row (client_id IN ROWTYPE) RETURN ROWTYPE IS

CURSOR c_test (c_id IN VARCHAR2) IS

SELECT * FROM TEST;

rec_test c_test%ROWTYPE;

cut out code

RETURN rec_test;

END;

any help would be greatly appreciated
Re: function to return rowtype is it possible [message #12948 is a reply to message #12943] Sun, 13 June 2004 13:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Without having the whole picture and knowing what you plan to use this for, it is hard to tell if you may be doing things the hard way. Or, is this just an academic question? In the following example, I have just done what you asked, created a function within a package, that returns a rowtype, retaining as much of your original code as possible.

scott@ORA92> -- test data:
scott@ORA92> SELECT * FROM test
  2  /

CLIENT_ID                                NAME       JOB
---------------------------------------- ---------- ---------
7369                                     SMITH      CLERK
7499                                     ALLEN      SALESMAN
7521                                     WARD       SALESMAN


scott@ORA92> -- package:
scott@ORA92> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    FUNCTION get_row
  4  	 (client_id IN VARCHAR2)
  5  	 RETURN test%ROWTYPE;
  6  END your_pkg;
  7  /

Package created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    FUNCTION get_row
  4  	 (client_id IN VARCHAR2)
  5  	 RETURN test%ROWTYPE
  6    IS
  7  	 CURSOR c_test (c_id IN VARCHAR2) IS
  8  	 SELECT * FROM TEST
  9  	 WHERE	client_id = c_id;
 10  	 rec_test test%ROWTYPE;
 11    BEGIN
 12  	 OPEN c_test (client_id);
 13  	 FETCH c_test INTO rec_test;
 14  	 CLOSE c_test;
 15  	 RETURN rec_test;
 16    EXCEPTION
 17  	 WHEN NO_DATA_FOUND THEN RETURN NULL;
 18    END get_row;
 19  END your_pkg;
 20  /

Package body created.

scott@ORA92> SHOW ERRORS
No errors.


scott@ORA92> -- execution and results:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    myvar test%ROWTYPE;
  3  BEGIN
  4    FOR rec IN (SELECT empno FROM emp) LOOP
  5  	 myvar := your_pkg.get_row (rec.empno);
  6    DBMS_OUTPUT.PUT_LINE (rec.empno || ' ' || myvar.client_id || ' ' || myvar.name);
  7    END LOOP;
  8  END;
  9  /
7369 7369 SMITH
7499 7499 ALLEN
7521 7521 WARD
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.
Re: function to return rowtype is it possible [message #12961 is a reply to message #12948] Mon, 14 June 2004 10:24 Go to previous messageGo to next message
Roger Simms
Messages: 14
Registered: May 2004
Junior Member
Thanks Barbara thats helped me alot. Its not just an academic problem so I will try and explain. I have 80 documents where bookmarks get populated (OLE2). Some docs have 30 bookmarks but each document contains some cursors that are the same in all the other documents such as asking for persons name address and contact details. I wanted to created a package with some standard functions that I could call from each package that I need to create for each document.

in each package a call gets made to

add_data('BookmarkName', data);

To keep the packages small I thought I could call the function and passed the row into a variable and then do something similar to your dbms_output above that I know works
add_data('BookmarkName', rec.empno || ' ' || myvar.client_id || ' ' || myvar.name);

My example was a simple cursor can this be done where there could be many joins. How would you
RETURN many_tables%ROWTYPE

This would be far better than having 30 cursors in each package doing the same thing

Thanks for your Help
Re: function to return rowtype is it possible [message #12968 is a reply to message #12961] Mon, 14 June 2004 21:33 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
You can declare a record type in your package specification, then return that type. Please see the modified example below. I only used two tables and one join condition, but you can use as many tables and join conditions as you like.

scott@ORA92> -- test data:
scott@ORA92> SELECT * FROM test
  2  /

CLIENT_ID                                NAME           DEPTNO
---------------------------------------- ---------- ----------
7369                                     SMITH              20
7499                                     ALLEN              30
7521                                     WARD               30

scott@ORA92> SELECT * FROM dept
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


scott@ORA92> -- package:
scott@ORA92> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    TYPE your_rowtype IS RECORD
  4  	 (client_id test.client_id%TYPE,
  5  	  name	    test.name%TYPE,
  6  	  dname     dept.dname%TYPE);
  7    FUNCTION get_row
  8  	 (client_id IN VARCHAR2)
  9  	 RETURN your_rowtype;
 10  END your_pkg;
 11  /

Package created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    FUNCTION get_row
  4  	 (client_id IN VARCHAR2)
  5  	 RETURN your_rowtype
  6    IS
  7  	 CURSOR c_test (c_id IN VARCHAR2) IS
  8  	 SELECT t.client_id, t.name, d.dname
  9  	 FROM	test t, dept d
 10  	 WHERE	t.client_id = c_id
 11  	 AND	t.deptno = d.deptno;
 12  	 rec_test your_rowtype;
 13    BEGIN
 14  	 OPEN c_test (client_id);
 15  	 FETCH c_test INTO rec_test;
 16  	 CLOSE c_test;
 17  	 RETURN rec_test;
 18    EXCEPTION
 19  	 WHEN NO_DATA_FOUND THEN RETURN NULL;
 20    END get_row;
 21  END your_pkg;
 22  /

Package body created.

scott@ORA92> SHOW ERRORS
No errors.


scott@ORA92> -- execution and results:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    myvar your_pkg.your_rowtype;
  3  BEGIN
  4    FOR rec IN (SELECT empno FROM emp) LOOP
  5  	 myvar := your_pkg.get_row (rec.empno);
  6    DBMS_OUTPUT.PUT_LINE
  7  	 (rec.empno
  8  	  || ' ' || myvar.client_id
  9  	  || ' ' || myvar.name
 10  	  || ' ' || myvar.dname);
 11    END LOOP;
 12  END;
 13  /
7369 7369 SMITH RESEARCH
7499 7499 ALLEN SALES
7521 7521 WARD SALES
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
Previous Topic: how to list stored procedures?
Next Topic: shared memory in PL/SQL
Goto Forum:
  


Current Time: Tue Jul 22 07:02:17 CDT 2025