By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
-- create table (do not know yours, so just made it up - seems reasonable)
create table movies
( title varchar2(50)
, poster_path varchar2(50)
, production_company varchar2(50)
, genre varchar2(50)
, country varchar2(50)
, runtime integer -- minuets
, budget integer -- dollers
, vote_average number(5,2)
) ;
-- put some data in
insert into movies
( title
, poster_path
, runtime
, budget
, vote_average
)
select 'The Longest Day'
, 'http:://movieposters/thelangestday.jpg'
, floor(dbms_random.value(60,150))
, floor(dbms_random.value(100,300)) * 100000
, round(dbms_random.value() * 5.5 + 1.0,2)
from dual
union all
select '3days of the Condor'
, 'http:://movieposters/3daysofthecondor.jpg'
, floor(dbms_random.value(60,150))
, floor(dbms_random.value(100,300)) * 100000
, round(dbms_random.value() * 5.5 + 1.0,2)
from dual
union all
select 'A mid Summer''s Night Dream'
, 'http:://movieposters/amidsummerdnighdrearm.jpg'
, floor(dbms_random.value(60,150))
, floor(dbms_random.value(100,300)) * 100000
, round(dbms_random.value() * 5.5 + 1.0,2)
from dual
;
3 rows affected
select * from movies;
| TITLE | POSTER_PATH | PRODUCTION_COMPANY | GENRE | COUNTRY | RUNTIME | BUDGET | VOTE_AVERAGE |
|---|---|---|---|---|---|---|---|
| The Longest Day | http:://movieposters/thelangestday.jpg | null | null | null | 135 | 19100000 | 5.64 |
| 3days of the Condor | http:://movieposters/3daysofthecondor.jpg | null | null | null | 101 | 11800000 | 2.22 |
| A mid Summer's Night Dream | http:://movieposters/amidsummerdnighdrearm.jpg | null | null | null | 86 | 15700000 | 5.31 |
-- no idea what apex_string.get_initials is so just simulate something
create or replace function apex_string_get_initials(string_in varchar2)
return varchar2
is
full_string varchar2(256);
begin
full_string := regexp_replace(string_in, '[-\/$@*]',' ');
return substr(regexp_replace(initcap(full_string),'[^A-Z0-9]'),1,16);
end apex_string_get_initials;
/
-- not the main event. Notice Spec contains cursor
create or replace package prints as
function movies_list return sys_refcursor;
function order_vote return sys_refcursor;
cursor c_movie_list_detail is
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;
end prints;
/
select * from user_errors;
-- And the body. --Notice local function movie_details selects
create or replace package body prints as
-- local function to get full movie detail;
function movie_details
return sys_refcursor
is
r_movie_details sys_refcursor;
begin
open r_movie_details 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_details;
end movie_details ;
--- Public functions
function order_vote
return sys_refcursor
is
begin
return movie_details;
end order_vote;
declare
c_movie sys_refcursor;
movie_details prints.c_movie_list_detail%rowtype;
title_list movies.title%type;
begin
dbms_output.enable;
dbms_output.put_line ( 'Movie Titles');
c_movie := prints.movies_list;
loop
fetch c_movie into title_list;
exit when c_movie%notfound;
dbms_output.put_line( chr(09) || title_list);
end loop ;
close c_movie;
dbms_output.put_line ( 'Movie Cards');
c_movie := prints.order_vote;
loop
fetch c_movie into movie_details;
exit when c_movie%notfound;
dbms_output.put_line( chr(09) || ' Title: ' || movie_details.card_title);
dbms_output.put_line( chr(09) || ' Text: ' || movie_details.card_text);
dbms_output.put_line( chr(09) || ' Votes: ' || to_char(movie_details.vote_average, '990.00'));
dbms_output.put_line( chr(09) || 'Initials: ' || movie_details.card_initials);
dbms_output.put_line( chr(09) || 'Duration: ' || movie_details.runtime || ' minutes');
dbms_output.put_line( chr(09) || ' Budget: ' || to_char(movie_details.budget, '99G999G999') || ' USD');
dbms_output.put_line('');
end loop ;
close c_movie;
end ;
/
1 rows affected
dbms_output:
Movie Titles
The Longest Day
3days of the Condor
A mid Summer's Night Dream
Movie Cards
Title: The Longest Day
Text: http:://movieposters/thelangestday.jpg
Votes: 5.64
Initials: TLD
Duration: 135 minutes
Budget: 19,100,000 USD
Title: A mid Summer's Night Dream
Text: http:://movieposters/amidsummerdnighdrearm.jpg
Votes: 5.31
Initials: AMSSND
Duration: 86 minutes
Budget: 15,700,000 USD
Title: 3days of the Condor
Text: http:://movieposters/3daysofthecondor.jpg
Votes: 2.22
Initials: 3OTC
Duration: 101 minutes
Budget: 11,800,000 USD