Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Alternatives to a PIPELINED table function

David Sumner-OracleJan 13 2012 — edited Jan 19 2012
I am trying to write a piece of code that will display student attendance on Monday through Friday for a given date. I supply the code with the date, it determines the dates for Monday, Tuesday....Friday of that week and then determines which days during the week that student was or wasn't present. My data includes two tables:

Students
-----------
student_id number,
first_name varchar2,
last_name varchar2,
campus_id number

Attendance
-------------
student_id number,
date_of_attendance date

I would like for the output of the tables to be in the following format:

student_id first_name||last_name mon tue wed thu fri
-------------------------------------------------------------------------------
123456 john doe 0 1 0 1 1
163452 jane doe 1 1 1 1 1

1 is if there is a record in the attendance table for that student on that day and 0 is there is not.

I tried doing this using a pipelined table function. Whenever I ran the query, I got the following error:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-801: internal error [hshuid:LU invalid]
06552. 00000 - "PL/SQL: %s"
*Cause:
*Action:
Error at Line: 14 Column: 12

I'm using Oracle 10gXE and according to support note 559786.1 this "should" be due to a bug that results from the type definition including a reserved word. I double-checked and I don't have any such words in my type definition. I tried running the same code in an 11g database and it worked. Unfortunately using 11g isn't an option for this task.

Can anybody suggest an alternative means for doing this?
This post has been answered by unknown-7404 on Jan 14 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 16 2012
Added on Jan 13 2012
12 comments
1,260 views