Re: beginner: cursors

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 10 Apr 2007 12:51:23 -0700
Message-ID: <1176234677.449649@bubbleator.drizzle.com>

Mr.Baha wrote:
> 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.

The above code example is a horror story with so many things wrong I'm not sure where to start.

I would suggest pushing back away from the keyboard and getting both Tom Kyte's books and a class in beginning PL/SQL. The c.d.o. groups are not an on-line tutorial.

On your list of things to learn should be the following syntax: OPEN <refcursor> FOR

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Apr 10 2007 - 14:51:23 CDT