On Apr 10, 12:51 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Haha..well put. I did back away from the keyboard...and the copy/
paste, for that matter. I had a false sense of thinking i was close.
I'll revisit the console after an hour or two of reading. Thanks.
Received on Tue Apr 10 2007 - 15:20:11 CDT
