Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<2s4p1u03f9rojusghfkprs4v9vm80bbm73_at_4ax.com>...
> On 16 Dec 2001 06:43:00 GMT, kdankwah_at_aol.comnojunk (KDankwah) wrote:
>
> >I have this function below and unfotunately when I test it I get the error
> >message below what am I doing wrong. Could someone please tell me.
> >=======================================================
> >CREATE OR REPLACE FUNCTION Students_Per_Enrolldate
> > (p_enroll_date IN ENROLLMENT.enroll_date%TYPE)
> > RETURN STUDENT%ROWTYPE
> >AS
> >CURSOR c_student IS
> > SELECT DISTINCT s.first_name , s.last_name, e.enroll_date
> > FROM ENROLLMENT e, STUDENT s
> > WHERE e.student_id = s.student_id
> > AND e.enroll_date = p_enroll_date
> > ORDER BY s.last_name, s.first_name, e.enroll_date;
> > TYPE type_student_tab IS TABLE OF c_student%ROWTYPE
> > INDEX BY BINARY_INTEGER;
> > tab_student type_student_tab;
> >BEGIN
> > FOR l_student IN c_student
> > LOOP
> > tab_student(NVL(tab_student.last,0)+1):= l_student;
> > END LOOP;
> > DBMS_OUTPUT.PUT_LINE
> > ('Enroll DATE Student Name');
> >DBMS_OUTPUT.PUT_LINE
> > ('====================================================');
> >FOR e_enroll IN tab_student.first .. tab_student.last
> > LOOP
> > DBMS_OUTPUT.PUT_LINE
> > ( tab_student(e_enroll).enroll_date||' '||
> > tab_student(e_enroll).first_name || ' '||
> > tab_student(e_enroll).last_name || ' ');
> > END LOOP;
> > END;
> >/
> >
> >================== TEST===========================
> > 1 select students_per_enrolldate(TO_CHAR('&ENROLL_DATE', 'DD-MON-YYYY'))
> > 2* From DUAL
> >SQL> /
> >Enter value for enroll_date: 30-JAN-1999
> >select students_per_enrolldate(TO_CHAR('30-JAN-1999', 'DD-MON-YYYY'))
> > *
> >ERROR at line 1:
> >ORA-00902: invalid datatype
> >
> >
> >Thanks a lot
>
> 1 You're using a function, where that could have been a procedure
> 2 Your function doesn't RETURN anything (ie you have no RETURN
> statement in your function)
> 3 PL/SQL functions can't return Table%ROWTYPE, unless it has been
> defined as a TYPE before
> 4 I don't know why you want to replace a simple select by calling a
> function in a select from dual
>
> You do you have access to PL/SQL manuals, do you? Looks like some old
> sqlserver habits need to be washed out.
>
> Regards
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Can you please tell me what to do, since I am a newbie. Thanks for
your comments though.
Received on Sun Dec 16 2001 - 13:13:31 CST
