Replacing DBMS_OUPUT in a PL/SQL test LOOP to return a collection

Question

So I have this assignment where I have to create a stored procedure to search for movies in an Oracle DataBase.

The search string follow the following logic :

1. It looks for the year of the movie in parenthesis
   Ex. (1992)

2. It looks for a range of year in brackets
   Ex. [1992,2000]

3. It looks for a word contained in the title, country, realisator, genre, actors or scenarists.

4. Any of the above can be combined multiple times.
   Ex. : The Lord Of the Ring Ian McKellen Christopher Lee [1992,2000]

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.

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.

So here are my questions :

1. Is my logic flawed? Is there a simpler way to archive this with a single select or something?

2. Is there a way to dynamically push data inside a cursor and return it?

3. Am I really supposed to trick the DBMS_OUTPUT so it is sent over to hibernate?

Here's my 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
    <<FILM_LOOP>>
    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;

A small disclaimer here :

• I saw some similar questions that addressed this issue, but the ones that used cursors were returning a complete select, not hand picked rows.

• The question about DBMS_OUTPUT and Hibernate stated that it should be avoided.

• 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).

Answer 1

The use of the DBMS_OUTPUT package is pretty much bounded to developer executions of anonymous blocks, and hence not suited for your intended communication with the Hibernate framework.

If you already have a stored procedure to apply your filter and determine your positive results, a solution could be populate a temporary table with those positives, and then return an open cursor which will only have the data from that temporary table, something like:

create global temporary table movie_results( movie varchar2(200) ) on commit preserve rows;

Of course your temp table can have more columns, but let me leave it like this, just to illustrate my solution.

   CREATE OR REPLACE PROCEDURE p_SearchFilm(searchString IN VARCHAR2, movies out SYS_REFCURSOR) 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
        <<FILM_LOOP>>
        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);
            insert into movie_results( movie )
            values film.FilmId||'|'||film.Titre;

            commit;
        END LOOP;

        open movies for
           select *
           from movie_results;
    END;

Now your parameter 'movies' has all the positive results derived from your procedure, and all you'll have to do is read the cursor in Hibernate.

Note that, once you close your connection, the temporary table looses all the data and will be ready to use again when another session begins (always remember to close your Cursors/Connections).

Answer 2

The first part of this can probably be accomplished with just a query. You can define your search terms like this: How to declare variable and use it in the same SQL script? (Oracle SQL)

I'll leave the basic query writing to you (e.g. joining appropriate data), but your script will in effect look something like this:

DEFINE var_year1 = 1992;
DEFINE var_year2 = 1994;
DEFINE var_country = null;
DEFINE var_title = 'Lord Of The Rings';
DEFINE var_realisator = null;
DEFINE var_genre = null;
DEFINE var_actors = 'Ian';
DEFINE var_scenarists = 'Lee';

SELECT film_id, title, year
FROM ...
WHERE year BETWEEN &var_year1 AND &var_year2
 AND UPPER(title) LIKE UPPER('%&var_title%')
 AND UPPER(country) LIKE UPPER('%&var_country%')
 AND UPPER(realisator) LIKE UPPER('%&var_realisator%')
 AND UPPER(genre) LIKE UPPER('%&var_genre%')
 AND UPPER(actors) LIKE UPPER('%&var_actors%')
 AND UPPER(scenarists) LIKE UPPER('%&var_scenarists%');
/

The reason I only select film_id, title, and year is because the title and year will be distinct values to the film_id. There are a couple of ways to skin this cat, but it all revolves around using a LIKE statement with variables that you have defined. If you do not want to explicitly define the values in the script, you can use the ACCEPT command. Or, you can interface it with something else that applies those values into the variables.

With this approach, it was kind of a shot in the dark on how you really have your data laid out. But keep in mind good relational database management practices, and you should be able to turn this into a useable query.

Additional Method:

You can also use a series of subqueries to find the same data above using the like statements, then INNER JOIN them together to figure out which ones are in common. Example:

WITH sub_title AS (SELECT film_id FROM Film WHERE UPPER(title) LIKE UPPER('%&var_title%')),
...
sub_actor AS (SELECT film_id FROM (...) WHERE UPPER(actor) LIKE UPPER('%&var_actor%'))
SELECT film_id
FROM sub_title t
     INNER JOIN sub_actor a ON a.film_id = t.film_id;
/

If you had searched for 'star war' in that, you would have returned for example

1   Star Wars A New Hope
2   Star Wars The Empire Strikes Back
3   Star Wars Return of the Jedi

Then in the second subquery if you had searched 'Donald Glover' you would have gotten

2   Star Wars The Empire Strikes Back
3   Star Wars Return of the Jedi

Then, the INNER JOIN would have given you ID numbers 2 and 3 because those were the only ones where all of the criteria were met. Repeat the process for your other search terms. I hope this helps.