Re: beginner: cursors

From: Mr.Baha <mattbaha_at_gmail.com>
Date: 10 Apr 2007 10:36:22 -0700
Message-ID: <1176226582.744817.255430@q75g2000hsh.googlegroups.com>

On Apr 10, 10:15 am, DA Morgan <damor..._at_psoug.org> wrote:
> matt wrote:
> > On Apr 10, 1:41 am, "dombrooks" <dombro..._at_hotmail.com> wrote:
> >> Replace 'DECLARE' with 'CREATE OR REPLACE PROCEDURE <your_proc_name>
> >> AS'
> >> and you're pretty much done.
>
> > I attempted to do this and receive a Procedure Created with
> > Compilation Errors.
>
> > Here is what I altered from the initial post above:
>
> > CREATE OR REPLACE PROCEDURE sp_seniority(
> > name OUT empbb02.ename%TYPE,
> > seniority OUT number,
> > TYPE cursor_var IS REF CURSOR,
> > myCursorVar OUT cursor_var;) AS
> > BEGIN
>
> > Does that TYPE cursor_var also need to be specified as an OUT?
>
> CREATE OR REPLACE TYPE or declare it in a package header.
>
> Look at these examples:http://www.psoug.org/reference/ref_cursors.html
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Here's the modifications i've made to get the original plsql into a function:

SQL> DECLARE
  2 TYPE cursor_var IS REF CURSOR;
  3 myCursorVar cursor_var;
  4
  5 FUNCTION get_seniority(name empbb02.ename%type, seniority number) RETURN cursor_var IS
  6 name_list empbb02.ename%type;
  7 seniority_list number;
  8
  9 BEGIN
10 OPEN myCursorVar FOR SELECT E.ename, TRUNC(MONTHS_BETWEEN(sysdate,E.hiredate)) FROM empbb02 E; 11 LOOP

 12      FETCH myCursorVar INTO name,seniority;
 13      EXIT WHEN myCursorVar%NOTFOUND;
 14     RETURN (name_list , seniority_list);
 15      DBMS_OUTPUT.PUT_LINE(name||'  '||seniority);

16 END LOOP;
17 CLOSE myCursorVar;
18 END get_seniority;
19 /
END get_seniority;

It seems to complain about the end-of-file on line#18. Received on Tue Apr 10 2007 - 12:36:22 CDT