3 Replies Latest reply: Oct 14, 2013 9:16 AM by Mike Kutz RSS

    hr tag not working in a function

    CharlieMack

      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

          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

            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

              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;"