title: Replacing DBMS_OUPUT in a PL/SQL test LOOP to return a collection
body: <p>So I have this assignment where I have to create a stored procedure to search for movies in an Oracle DataBase. </p>
  
  <p>The search string follow the following logic :</p>
  
  <ol>
  <li><p>It looks for the year of the movie in parenthesis <br> 
  Ex. (1992)</p></li>
  <li><p>It looks for a range of year in brackets <br>
  Ex. [1992,2000]</p></li>
  <li><p>It looks for a word contained in the title, country, realisator, genre, actors or scenarists.</p></li>
  <li><p>Any of the above can be combined multiple times. <br>
  Ex. : The Lord Of the Ring Ian McKellen Christopher Lee [1992,2000]</p></li>
  </ol>
  
  <p>The logic used to solve this problem was to make a giant query to group all the required data then use a cursor to loop through the result set with a cursor to check if every word of the search string is valid. </p>
  
  <p>I managed to make a procedure that works as expected, but the only way I found to return the results was to use DBMS_OUTPUT. Now the problem is that when I plug this is Hibernate the DBMS_OUTPUT is not sent over to the client. I've read some way to force the output by setting DBMS_OUTPUT.enable, but I feel that this is not the proper way to do it.</p>
  
  <p>So here are my questions :</p>
  
  <ol>
  <li><p>Is my logic flawed? Is there a simpler way to archive this with a single select or something?</p></li>
  <li><p>Is there a way to dynamically push data inside a cursor and return it?</p></li>
  <li><p>Am I really supposed to trick the DBMS_OUTPUT so it is sent over to hibernate?</p></li>
  </ol>
  
  <p>Here's my code :</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE p_SearchFilm(searchString IN VARCHAR2) IS
      IsValid BOOLEAN;
      y1 INTEGER;
      y2 INTEGER;
      subStrArray apex_application_global.vc_arr2;
      term VARCHAR(100);
  
      CURSOR films IS 
              Select FilmId, Titre, real.Prenom||' '||real.nom as Realisateur, anneeSortie, ListPays, ListGenres,
                     ListScenaristes, ListActeurs, langueOrigine
                  from Film 
                  natural left join 
                      (select FilmId, listagg(p.Nom, ',') within group (Order By p.nom) ListPays from Film
                          natural join Film_vs_pays
                          natural join Pays p
                          Group by FilmId)
                  natural left join 
                      (select FilmId, listagg(g.Nom, ',') within group (Order By g.nom) ListGenres from Film
                          natural join Film_vs_Genre
                          natural join Genre g
                          Group by FilmId)
                  natural left join 
                      (select FilmId, listagg(p.Prenom||' '||p.Nom, ',') within group (Order By p.nom) ListScenaristes from Film
                          natural join Scenariste s
                          join Personne p on s.personneId = p.personneId
                          Group by FilmId)
                  natural left join 
                      (select FilmId, listagg(p.Prenom||' '||p.Nom, ',') within group (Order By p.nom) ListActeurs from Film
                          natural join Personnage perso
                          join Personne p on perso.personneId = p.personneId
                          Group by FilmId) 
                  left join Personne real on real.personneId = realisateurId;
  BEGIN
      &lt;&lt;FILM_LOOP&gt;&gt;
      FOR film IN films LOOP
          subStrArray := apex_util.string_to_table(searchString, ' ');
          FOR i in 1..subStrArray.count LOOP
              IsValid:= FALSE;
              term:= subStrArray(i);
              IF REGEXP_LIKE(term, '\(\d{4}\)') THEN
                  IF film.anneeSortie = TO_NUMBER(regexp_substr(term, '\d{4}')) THEN
                      IsValid:= TRUE;
                  END IF;
              ELSIF REGEXP_LIKE(term, '\[\d{4},\d{4}\]') THEN
                  y1:= regexp_substr(term, '\d{4}', 1, 1);
                  y2:= regexp_substr(term, '\d{4}', 1, 2);
  
                  IF film.anneeSortie BETWEEN y1 AND y2 THEN
                      IsValid:= TRUE;
                  END IF;
              ELSE
                  IF UPPER(film.Titre||film.Realisateur||film.ListActeurs||film.ListScenaristes||film.ListGenres||film.ListPays||film.langueOrigine)
                       LIKE '%'||UPPER(term)||'%' THEN
                      IsValid:= TRUE;
                  END IF;
              END IF;
  
              IF NOT IsValid THEN
                  CONTINUE FILM_LOOP;
              END IF;
  
          END LOOP;
  
          DBMS_OUTPUT.put_line(film.FilmId||'|'||film.Titre);
      END LOOP;
  END;
  </code></pre>
  
  <p>A small disclaimer here :</p>
  
  <ul>
  <li><p>I saw some similar questions that addressed this issue, but the ones that used cursors were returning a complete select, not hand picked rows. </p></li>
  <li><p>The question about DBMS_OUTPUT and Hibernate stated that it should be avoided.</p></li>
  <li><p>The questions using piped rows seamed to work only with functions (Changing the procedure for a function called by the procedure could be a valid work around, I'd like to know if something else is possible before tho).</p></li>
  </ul>