rewriting sql query to pl/sql in oracle apex

Question

I have an application in oracle apex and there is a section which is called search_result and it returns this sql query

select
  "TITLE" CARD_TITLE,
  "POSTER_PATH" CARD_TEXT,
  "PRODUCTION_COMPANY" CARD_SUBTEXT,
  apex_string.get_initials("TITLE") CARD_INITIALS,
  'https://www.imdb.com/title/'||IMDB_ID CARD_LINK,
  null CARD_MODIFIERS,
  null CARD_COLOR,
  null CARD_ICON,
  "GENRE",
  "COUNTRY",
  "PRODUCTION_COMPANY",
  "RUNTIME",
  "BUDGET",
  "VOTE_AVERAGE",
  "TITLE",
  "POSTER_PATH"
from  "MOVIES"
order by vote_average desc

But I need to rewrite this query with pl/sql function body and I've created package with function

    CREATE OR REPLACE PACKAGE PRINTS AS
FUNCTION MOVIES_LIST RETURN SYS_REFCURSOR;
FUNCTION ORDER_VOTE RETURN SYS_REFCURSOR;
END PRINTS;


CREATE OR REPLACE PACKAGE BODY PRINTS AS
FUNCTION ORDER_VOTE
RETURN SYS_REFCURSOR
is 
    r_movie sys_refcursor;
BEGIN
open r_movie for select
  "TITLE" CARD_TITLE,
  "POSTER_PATH" CARD_TEXT,
  "PRODUCTION_COMPANY" CARD_SUBTEXT,
  apex_string.get_initials("TITLE") CARD_INITIALS,
  null CARD_MODIFIERS,
  null CARD_COLOR,
  null CARD_ICON,
  "GENRE",
  "COUNTRY",
  "PRODUCTION_COMPANY",
  "RUNTIME",
  "BUDGET",
  "VOTE_AVERAGE",
  "TITLE",
  "POSTER_PATH"
from  "MOVIES"
order by vote_average desc;
return r_movie;
END ORDER_VOTE;
END PRINTS;

Then I've tried to return this cursor in pl/sql returning sql query block:

declare 
   movie_cur sys_refcursor;
   a_movie   movies%rowtype; 
begin

   movie_cur := PRINTS.ORDER_VOTE;            -- call the movie function

   loop
      fetch movie_cur into a_movie;
      exit when movie_cur%notfound; 
   end loop; 
   return a_movie;
end;

but it throws this error: ORA-06550: line 12, column 11: PLS-00382: expression is of wrong type

what I did wrong and how should I fix it.

My db and some screenshots:

Column Name Data Type   Nullable    Default Primary Key
ID  NUMBER  No  "WKSP_DBMSPROJECT1"."ISEQ$$_111377832".nextval  1
TITLE   VARCHAR2(255)   Yes -   -
GENRE   VARCHAR2(50)    Yes -   -
COUNTRY VARCHAR2(50)    Yes -   -
PRODUCTION_COMPANY  VARCHAR2(255)   Yes -   -
RUNTIME NUMBER  Yes -   -
RELEASE_DATE    DATE    Yes -   -
OVERVIEW    VARCHAR2(4000)  Yes -   -
TAGLINE VARCHAR2(255)   Yes -   -
BUDGET  NUMBER  Yes -   -
REVENUE NUMBER  Yes -   -
POPULARITY  NUMBER  Yes -   -
VOTE_AVERAGE    NUMBER  Yes -   -
VOTE_COUNT  NUMBER  Yes -   -
POSTER_PATH VARCHAR2(255)   Yes -   -
TMDB_ID NUMBER  Yes -   -
IMDB_ID VARCHAR2(50)    Yes -   -

IMDB DB

page design

page itself

Belayer Belayer · Accepted Answer · 2021-04-29T17:07:46

Your code is mostly correct. However you have written it an an anonymous block, which unfortunately cannot return results. You need to convert either to a function that returns a reference cursor or a procedure with an out parameter of a reference cursor.

create or replace 
function movies_list
  return sys_refcursor 
is
    r_movies sys_refcursor;
begin
   open r_movies for 
        select * from movies;
    return r_movies;
end movies_list;

Since it would seem your current code processes a column list instead of a select *. In that case you can the select with the exact query you have.

Processing:

declare 
   movie_cur sys_refcursor;
   a_movie   movies%rowtype; 
begin
   dbms_output.enable; 
   movie_cur := movies_list;            -- call the movie function
   dbms_output.put_line("Movie List');      
   loop
      fetch movie_cur into a_movie;       
      exit when movie_cur%not_found; 
      dbms_output.put_line(a_movie.title); 
   end loop; 
end;

The function is compiled and stored in the database (a stored procedure). THE ref-cursor is the result set of the query - just as your Apex section does not return the query, by the result set of the query.

How to use ref cursor fully. (Well mostly). In response to wrong type exception.

This error is the result of your query returning different columns than defined in the table but then trying to fetch into a variable defined as movies%rowtype; Now that definition creates a structure that exactly match the tables column definition. However, your query has at least 4 columns that are not part of the table definition and perhaps 3 others. The columns Title, Production_Company, and Poster_Path are each used twice in query. Even though aliased they still represent an additional column in the result set. There is the function call, and the null selections. Further there seems additional columns in the table that are not in the query. This does not make the query bad, just that it cannot match the movies%rowtype declaration.

Two solutions come to mind. Just "select * from movies" and fetch into the movies%rowtype declaration and derive the additional columns after the table data is retrieved. The other is to set up an appropriate cursor definition in the package spec. Where it can be references externally to the package. The issue being that you need to basically repeat that query in the body when you actually open the reference cursor.

Assuming that you actually need those columns and you have table columns you want to avoid selecting since you do not need them (I like that part), I put together a full demo. Since I do not have your table definition and I have absolutely no idea what the function apex_string.get_initials even is, I just made something up for them. But they seem reasonable, and you should problems replacing with the actual.

rewriting sql query to pl/sql in oracle apex

2 Answers