First,
Joizey, I'll assert that part of the confusion that you experienced regarding the Oracle products is[ul][li]Shared by an enormous percentage of other Oracle neophytes, and[/li][li]Made worse by Oracle's extremely unfortunate choice of product names.[/li][/ul]
In Oracle's early days, they chose to name most of their products with names that were both a) industrially unfortunate and b) unable to be protected (since "SQL" was a term already protected by IBM Corporation)...Oracle thought that they would capitalize on the "new wave" of enthusiasm for IBMs
SQL language, and try to become the pre-eminent provider of SQL-related products. So, they started naming virtually
everything that they released as a product as
SQL*This and
SQL*That.
For example, Oracle named its shell environment (from which we can
run SQL commands, but which contains as native commands absolutely
no SQL), as
SQL*Plus...Again, although SQL*Plus can
run any SQL command, the SQL commands are SQL commands, and not
native SQL*Plus commands. Native SQL*Plus commands exist to modify the
environment in which we run our SQL commands (e.g., SET LINESIZE..., SET PAGESIZE..., et cetera)
That brings us next to
PL/SQL...another unfortunate product name. (Oracle should have named the product Oracle Ada, IMHO.) Not only does the name PL/SQL imply that the language is a form of SQL (which it really isn't...it only can
execute SQL DML directely and non-DML SQL indirectly with special "execute immediate" packaging), but notice the spelling of this product compared to the spelling of another unfortunately named Oracle product, SQL*Plus: The two products share nearly 75% of the letters that spell the product names, thus confusing those of us that are either mildly or profoundly dyslexic, and the resulting confusion is dangerous since the two products are virtually incompatible with one another...You cannot run any SQL*Plus commands within a PL/SQL code block, and if you are running PL/SQL within SQL*Plus, it is because SQL*Plus has performed an exit to the PL/SQL execution environment.
So, to summarize, Oracle's SQL, SQL*Plus, and PL/SQL
are three completely separate environments. Each environment has completely separate reasons for living, and the syntax and commands for each enviroment are as different from one another as are English and Chinese. But as a result of Oracle's unfortunate naming of these products, there is extreme confusion amongst neophytes.
Now, on to the hybrid SQL + PL/SQL solution that I promised you in my earlier posting. The first code block, below, is the definition of a PL/SQL user-defined function, "GET_GENRES", that has as arguments the "Movie" and "Director" (since re-makes of the same movie title [e.g. "King Kong"]) are certainly different movies and typically have different directors. The return value from the PL/SQL function is the (consolidated) genre list that you wanted. The second code block is the Oracle SQL that invokes GET_GENRES and produces the desired output.
Code:
create or replace function get_genres (title_in varchar2, director_in varchar2) return varchar2 is
hold_genre varchar2(2000);
begin
for x in (select genre from tblmovie where title_in = movie and director_in = director) loop
hold_genre := hold_genre||'/'||x.genre;
end loop;
return ltrim(hold_genre,'/');
end;
/
Function created.
Invocation and results follow...
Code:
col genre format a30
select movie "MovieTitle", get_genres(movie,director) genre, director
from tblmovie)
group by movie, director
order by movie, director
/
MovieTitle GENRE DIRECTOR
-------------------- ------------------------------ --------
Die Hard2 Action Smith
King Kong Action/Adventure/Horror Cooper
King Kong Adventure/Romance Jackson
Sleepless in Seattle Comedy/Romance Jones
When Harry Met Sally Romance Jones
5 rows selected.
I ran both code sets, above, from a SQL*Plus session. Notice that SQL*Plus recognizes validly constructed code from these three different
languages:[ul][li]
PL/SQL -- "Create or Replace function GET_GENRES...".[/li][li]
SQL*Plus -- "col genre format a30".[/li][li]
SQL -- "SELECT...".[/li][/ul]Notice how much simpler the SQL portion of the code is, in this case, if we package the "heavy lifting" into a PL/SQL user-defined function. Also notice that the "heavy lifting" logic in PL/SQL
looks absolutely nothing like the SQL-only code in my earlier posting.
If you have questions about any of this, please post.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services:
www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
