manipulating non-normalized table data 3

[NuJoizey]

Hi, I made the below post in the MSSQL forum by accident, but I am wondering how best to handle the same situation in PL SQL in which I am a novice.

http://www.tek-tips.com/viewthread.cfm?qid=1551264&page=1

Any help/hints/resources would be greatly appreciated.

 

[SantaMufasa]

Joizey,

There are many ways to resolve your need in Oracle. There are methods using PL/SQL, as you mention, above, but there are other methods using straight (Oracle) SQL. One method is to use Oracle's extremely powerful Analytic Functions and the equally powerful sys_connect_by_path...start with...connect by prior. Not only are the features powerful, but they are certainly complex and anything but intuitive. (One could take a half-day class on either topic and still not understand all of the nuances of the features.)

Here is a solution using those features, preceded by a confirmation of the data, which I plagiarized from your SQL Server thread:

select * from tblmovie;

MOVIE                GENRE           DIRECTOR
-------------------- --------------- --------
Sleepless in Seattle Comedy          Jones
Sleepless in Seattle Romance         Jones
Die Hard2            Action          Smith
When Harry Met Sally Romance         Jones

select movie "MovieTitle", substr(max(sys_connect_by_path(genre,'/')),2) genre, director
  from (select movie
              ,genre
              ,director
              ,row_number() over (partition by movie order by genre) rn
          from tblmovie)
 start with rn=1
connect by prior rn = rn - 1
       and prior movie = movie
 group by movie, director
 order by movie, director
/

MovieTitle           GENRE           DIRECTOR
-------------------- --------------- --------
Die Hard2            Action          Smith
Sleepless in Seattle Comedy/Romance  Jones
When Harry Met Sally Romance         Jones

After you have had a chance to inspect, cogitate upon, and research these features, please follow up with additional questions you may have.

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.”

 

[NuJoizey]

thank you for your helpful and thorough response, and your willingness to help me understand this. I think i need some time to wade through it!

PL/SQL, as you mention, above, but there are other methods using straight (Oracle) SQL

What is the difference between PL/SQL and straight Oracle SQL? - I thought PL/SQL and Oracle SQL were one in the same - that PL/SQL "was" the "flavor" of SQL implemented by Oracle DBs. But you make a distinction, so apparently my perception is not accurate!

 

[SantaMufasa]

Oracle SQL is Oracle's flavor of SQL. As you know, SQL is a Fourth Generation Language (4GL) in which you specify simply WHAT you want for output, not HOW to gather the results.

PL/SQL is Oracle's Prodecural Language environment, which is a Third Generation Language (3GL) in which you specify not only WHAT you want, but also HOW (procedurally) you want to gather and manipulate data. Oracle's PL/SQL is, syntactically, a knock-off of Ada, a language that the U.S. Department of Defense bases/based much of its programming.

I shall post a solution later today (when I'm not just heading out the door to the office) that uses PL/SQL as a key component of your solution. You will see:[ul][li]the difference in the coding syntax,[/li][li]that PL/SQL can use SQL commands, and[/li][li]SQL can use PL/SQL structures.[/li][/ul]Therefore, SQL and PL/SQL complement one another, but they are extremely different languages syntactically.


Later,

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.”

 

[SantaMufasa]

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.

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

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.

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.”

 

[SantaMufasa]

Also, Joizey, My addition of the King Kong remake requires a slight code enhancement to my first code posting (from 2 Jun 09 0:26):

...,row_number() over (partition by movie[B][I][u], director[/u][/I][/B] order by genre) rn...

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.”

 

[NuJoizey]

coming from a few years of Microsoft's TSQL now, the P/L SQL portion looks somewhat familiar, and as you say, it does make the SQL portion look more readable.

I use MSSQL (TSQL programming) for work, and only have recently been introduced to Oracle for a graduate school class project, for which the Movie example is a simplification.

Since you provided me with this wonderful overview, I will mention that I am indeed using the SQL*Plus environment running on linux, so everything is command line, which is a big difference from what I am used to. Is this how Oracle is typically implemented in the commercial arena? That is to ask, is there ever a graphical component similar to SQL Server Management Studio that is typically used?

 

[SantaMufasa]

Yes, there are several GUI interfaces that provide SQL functionality at a graphical level. Amongst the more heavily used are TOAD, PL/SQL Developer, Oracle Enterprise Manager (OEM), and many, many others.

I, frankly, rely most heavily on SQL*Plus for Windows (a "GUI" version of SQL*Plus), which should allow you to connect from your PC to any database to which you have network access (regardless of operating system). The "GUI" version of SQL*Plus is a bit more graphical than the *nix command-line interface (but not by much <grin>). The reason that I use SQL*Plus for just about everything is that I have an inventory of 20+ years of Oracle SQL scripts that do everything (and more) than any GUI can offer, and I can easily share the scripts with Tek-Tipsters and others without worry of whether or not they can run on the recipients' environments.

If you are eager to use a GUI for Oracle, I would ask amongst your Oracle colleagues/instructor/et cetera which GUI they prefer, and whether it is freely available to Oracle users or is it a for-fee product.

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.”

 

[NuJoizey]

this has been hugely informative. again, i am truly wow-ed and grateful for tipsters like you who go out of their way to share.

 

[SantaMufasa]

Where this was your first thread posting on one of the Oracle forums here on Tek-Tips, I (and the other Oracle-ites here), welcome you, NuJoizey, and wish you a long and worthwhile future here.

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.”

 

[NuJoizey]

well, thanks to you, I was able to derive a solution to my problem.

The thing is, now that I've got the data returning correctly in SQLPLUS command line, the screen formatting and flow is atrocious. I tried fiddling with SET linesize to X, but to no avail.

So, what are some of the commands or controls in SQLPLUS that I should know about in order to better control the way the results look in the command window?

 

[carp]

NJ -
There's actually quite a bit that can be done with SQL*Plus - to include HTML output if you so desire!
Here's a link to the SQL*Plus documentation chapter that addresses most of the formatting options.

http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch7.htm#1005594

Like SantaMufasa, I tend to use SQL*Plus for most of my utility work because it will work anywhere I need it to.

 

[NuJoizey]

thank you!