This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Mar 31, 2007 12:20 AM by 568919 RSS

SQL*Plus vs. PL/SQL

568919 Newbie
Currently Being Moderated
Folks, greetings:

When should a person use SQL*Plus and in what situation would they use PL/SQL?.

Please detail as much as you can :-)

Thank you,
-a
  • 1. Re: SQL*Plus vs. PL/SQL
    569588 Newbie
    Currently Being Moderated
    SQL*Plus is an application that you use to connect to Oracle databases.

    PL/SQL is a programming language that works very well with SQL. You
    can write SQL statements in SQL*Plus. You can write PL/SQL programs
    in SQL*Plus.
  • 2. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    user566585, thank you.

    As you pointed out, SQL*plus is used to connect to database and so provide a SQL prompt for PL/SQL blocks.

    Are there situations at all where SQL*plus is preferable or where PL/SQL cannot be used at all?.

    Thanks a lot,
    -a
  • 3. Re: SQL*Plus vs. PL/SQL
    247514 Expert
    Currently Being Moderated
    You are not comparing apple to apple.

    As other mentioned, SQL*Plus is an Oracle utility, PL/SQL is a programing language.
  • 4. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    yingkuan, thanks, yes as you can see i'm no expert. Reason for asking :-)

    Whe we say that "SQL*Plus is an Oracle utility, PL/SQL is a programing language" are we saying that we can do things either with SQL*plus or PL/SQL, so we choose as we please, or that there are things we can do only with SQL*plus or preferably so because they are more efficient? are PL/SQL and SQLplus overlapping? i.e. we can do things with Sql*plus but we can also do them with PL/SQL so it is our choice?.

    I have learned SQL*plus and im now trying to enter into the area of PL/SQL, and these questions started to get into my mind. One example I found striking is the use of cursors: I can formulate a statement with SQL+ to retrieve data but can also achieve the same thing with PL/SQL cursors?. Then i noticed a heated debate between programmers as to which is more efficient.

    It was explained to me that with sqlplus you retrieve data all at once while with PL/SQL i can retrieve row by row and, therefore, can exert control over these rows. I did not get a clear example of how i can exert and what???

    This led me to think that SQLplus and PL/SQL are probably more in competition than working together.

    I couldn't think of other things, apart from cursors, that i could do both with SQLplus and PL/SQL.

    See? a bit of confusion here, and as you said, i seem to be comparing aplpes with elephants :-)

    -a
  • 5. Re: SQL*Plus vs. PL/SQL
    502493 Newbie
    Currently Being Moderated
    I think you have learned SQL and executed your SQL statements in SQL*Plus. Using SQL*Plus you can execute both SQL and PL/SQL!
  • 6. Re: SQL*Plus vs. PL/SQL
    388131 Explorer
    Currently Being Moderated
    Hi -a (nice name by the way ;)),

    What exactly do you mean whith your statement 'I learned SQL/PLUS'. As posted before, it is a tool. When you have learned to use it, you should know about
    - SQL/PLUS commands (SET...)
    - SQL statements (SELECT...)
    - PL/SQL blocks (BEGIN...- END...)
    - Execution of scripts (can contain PL/SQL blocks and SQL statements).

    Your comparison is like 'should I use MS Word or the alphabet?'. Answer: It depends on what you want to achieve. If you just need to do SQL: SQL/PLUS is the tool of your choice. If you're about to produce complex business logic inside of server side procedures and / or functions: you have to use PL/SQL.

    One golden rule that I like to follow: Don't use PL/SQL when the task can be done in SQL.

    Rgds,
    Guido
  • 7. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    hi guido,

    your golden rule 'Don't use PL/SQL when the task can be done in SQL' is nice one; i may follow it too.

    What i meant by learning SQLplus is that i learned how to use it as a stand alone query language, meaning NO [Begin ... END] blocks were involved. This means that i can retrieve data using select statement, update records, create tables etc. but these statements were not wrapped up with PL/SQL.

    Seems that i need to do bit more research about 'complex business logic inside of server side procedures' as the answer appears to be within that statement.

    Thanks for your explanations,
    -a
  • 8. Re: SQL*Plus vs. PL/SQL
    247514 Expert
    Currently Being Moderated
    -a,

    I see, your mistaken SQL*PLUS with SQL.
    Like said, SQL*PLUS is a tool used to connect to database so you can run SQL statement or call PL/SQL blocks or procedures/functions etc.

    The key difference between SQL and PL/SQL is the PL. PL/SQL is procedural language. The PL/SQL language is tightly integrated with SQL, it's an extension of SQL. Because with SQL statement you can only do one task at a time, either SELECT or INSERT or UPDATE etc.

    Check more information here,
    http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref72
  • 9. Re: SQL*Plus vs. PL/SQL
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Another way of looking at this:

    SQLPlus is equivalent to TOAD, JDeveloper, SQLDeveloper, and so on. These can all run SQL and PL/SQL

    PL/SQL is equivalent to VB, C#, Java, PERL, and so on. The can all call SQL.
  • 10. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    yingkuan - thank you very much indeed; the link turned out to be helpful in clearing things up.

    Your statement "Because with SQL statement you can only do one task at a time, either SELECT or INSERT or UPDATE etc" almost eliminated all the other questions i had. The only one left comes from a book i'm reading:

    This says the only difference between a procedure and a function is that the function returns a value while a procedure doesn't. Return a value to where?. To the screen of a computer?.

    Can i not wrap any SQL, e.g. SELECT, statement either in procedure or function and both return values if i set the "set serveroutput on + DBMS_Output.put_line" on?.

    Thank you very much,
    -a
  • 11. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    Hans - thanks a lot for your clarification. I did experiment with TOAD and seems to provide some nicer work environment than sql*plus prompt.

    As for PL/SQL being equivalent to VB, C#, Java, PERL ( and now i guess PHP), i actually had the same question hanging for a while.

    The trigger for that question was a statement in a SQL book saying that there were rumors that Oracle might discontinue PL/SQL because Java was taking over. I said to myself these two must be doing the same thing then.

    Well, thanks for anticipating my question:-)

    and have a great Friday,
    -a
  • 12. Re: SQL*Plus vs. PL/SQL
    247514 Expert
    Currently Being Moderated
    This says the only difference between a procedure and a function is that the >>function returns a value while a procedure doesn't. Return a value to where?. To the >>screen of a computer?.
    You can return to wherever you wanted to. Depends on how you called the function, you can return the value to a variable or to the screen.
    Can i not wrap any SQL, e.g. SELECT, statement either in procedure or function >>and both return values if i set the "set serveroutput on + DBMS_Output.put_line" on?.
    You can output line in both procedure and function and technically that's not called "return value". That's output to screen.

    Just read through the document and compose a few sample procedures and functions by yourself then you will get better idea.
  • 13. Re: SQL*Plus vs. PL/SQL
    568919 Newbie
    Currently Being Moderated
    yingkuan - thank you very much for the explanations.

    Agree, practice makes perfect. In fact i am upgrading my laptop, it has only 128 mb of RAM. I once downloaded Oracle and suddenly could see nothing but few leaves from a tree and the other half of my screen was all dark black. This dark matter was supposed to be the rest of the tree on my laptop's wallpaper :-) everything froze. Was advised to have at least some 1GB of memory when working with Oracle stuff...

    need to do some work on loops and arrays too; i started to like them :-))

    Thanks for help,
    -a
  • 14. Re: SQL*Plus vs. PL/SQL
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    The trigger for that question was a statement in a
    SQL book saying that there were rumors that Oracle
    might discontinue PL/SQL because Java was taking
    over. I said to myself these two must be doing the
    same thing then.
    They do exactly the same kind of thing.

    They are slightly different in the way they do the job. Because of the strengths of PL/SQL and Java, I see both staying around for a long time.


    You may have noticed that you can buy hammers with several different shapes - some will a ball on one end, others with a claw, and others with a rubber or plastic end. Each one can do the basic job of hammering stuff, but they also have their won strength. A professional will probably have several.
1 2 Previous Next