function to return rowtype is it possible [message #12943] |
Sat, 12 June 2004 17:04  |
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 #12961 is a reply to message #12948] |
Mon, 14 June 2004 10:24   |
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  |
 |
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
|
|
|