Re: PLEAsE PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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 Received on Sun Dec 16 2001 - 06:33:06 CST