This discussion is archived
3 Replies Latest reply: Oct 14, 2013 7:16 AM by Mike Kutz RSS

hr tag not working in a function

CharlieMack Newbie
Currently Being Moderated

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

 

Charles

Mack

----------------------------------

Danny

Brown

--------------------------------------

Shane

McDonald

 

Any ideas of how I can achieve my goal.

 

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;  

 

 

I'm using Apex 4.2.2

  • 1. Re: hr tag not working in a function
    Nicolette Expert
    Currently Being Moderated

    CharlieMack

     

    How and where are you using this function?

    What type of region? Template used by the region?

     

    Could you demonstrated your problem on apex.oracle.com.

    If you can't use your students table try it with the available employees table.

     

    What browser are you using?

     

    Nicolette

  • 2. Re: hr tag not working in a function
    fac586 Guru
    Currently Being Moderated

    CharlieMack wrote:

     

    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

     

    Charles

    Mack

    ----------------------------------

    Danny

    Brown

    --------------------------------------

    Shane

    McDonald

     

    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.

  • 3. Re: hr tag not working in a function
    Mike Kutz Expert
    Currently Being Moderated

    CharlieMack wrote:


         

    exception         

      when others then return '';         

     

     

    REMOVE THIS PART OF THE CODE ASAP!!!

    This is very bad coding!

    Never EVER program:  "when others then null;"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points