The following function returns students names for a particular teacher. The function works fine; however, I want to create a horizontal ruled line after each student such as
Any ideas of how I can achieve my goal.
I'm using Apex 4.2.2
create or replace function sample (v_in_teacherID in schoolsrus%type , v_in_appid in number , v_in_appsession in number) return varchar2 is cursor c_curs is select * from v_students where teacherID = v_teacherID; v_retval varchar2(4000); begin v_retval := ''; for c_rec in c_curs loop if v_retval <> '' then v_retval := v_retval || '<HR>' ; end if; v_retval := v_retval || 'Student First Name: ' ||c_rec.s_firstname ||'<BR>'; v_retval := v_retval || 'Student Last Name: ' ||c_rec.s_lastname ||'<BR>'; end loop; return v_retval; exception when others then return ''; end;
There are a number of issues with this code.
Line 1: "sample" is an Oracle keyword and should not be used as an identifier.
Line 5: "v_teacherID" is not declared and the function therefore fails to compile. Presumably this should be "v_in_teacherID".
Lines 26 & 27: "when others" used in this way is a bug.
The cause of the problem you're posting about is in lines 8 and 10. The empty string ('') is treated as NULL in Oracle. Line 8 is therefore redundant as v_retval is initialized to NULL when it is declared in line 6. As NULL can only be evaluated using the "IS NULL" or "IS NOT NULL" operators, line 10 is never true, and the HR element is never included in the results string. Remove line 8, and change the expression in line 10 to "v_retval is not null" to get the results you are looking for.
However, I'd echo Nicolette's questions above:
- How and where are you using this function?
- What type of region?
- Template used by the region?
- Database version?
It's very likely that there are better ways in Oracle and/or APEX to meet the actual requirement, and the HTML this generates is not semantic and a non-conforming use of the BR element. This data would be better structured as a table, list, or description list.