Database talk: why use one over the other?

[VPescado]

Oh, OK. Yes, the flavour of SQL used by Oracle is called PL/SQL, the "PL" bit just stands for "Procedural Language" (if my recollection is correct), which means it stands for nothing at all when you think about it. Stored Procedures are written in a procedural language... who'd have guessed?

PL/SQL is a knock-off of ADA, so when you're going for those big missile defence contracts you can say you've written ADA code if you ever wrote stored procs for Oracle.

SQL Server and Sybase uses a flavour of SQL called T-SQL, the "T" stands for "Transact" so it doesn't mean much either.

There is some much incorrect information in the above, I really don't know where to start . . .but off the top of my head:

PL/SQL is not the flavor of SQL used by Oracle: PL/SQL is a procedural language built around SQL. SQL itself is a query language. In reality, there is a large fraction (possibly the majority) of SQL executed in Oracle databases is not embedded in PL/SQL (e.g. SQL scripts, java via jdbc, pro*C and other precompilers, etc.)

PL/SQL can be used for stored procedures, but also for non-stored ones (anonymous blocks). PL/SQL is not a knock off of ADA although they share some similarities. It would be a very bad idea to pass off PL/SQL experience as ADA experience.

 

[Arkan_Wolfshade]

Perhaps it would be easier to say PL/SQL is Oracle's implementation of ANSI SQL 92 as T-SQL is Microsoft's implementation of the same. They both support ANSI SQL 92 but add proprietary functionality on top if it.

 

[Iconoclast]

There is some much incorrect information in the above, I really don't know where to start . . .but off the top of my head:

PL/SQL is not the flavor of SQL used by Oracle: PL/SQL is a procedural language built around SQL. SQL itself is a query language. In reality, there is a large fraction (possibly the majority) of SQL executed in Oracle databases is not embedded in PL/SQL (e.g. SQL scripts, java via jdbc, pro*C and other precompilers, etc.)

OK fine, I was trying to keep it general and simple. Your distinction that "PL/SQL is not the flavor of SQL used by Oracle: PL/SQL is a procedural language built around SQL" is irrelevent when one is actually writing Oracle queries. At least it never mattered to me.

That you can use languages other than SQL to program against Oracle is not relevent to a discussion about SQL.

PL/SQL can be used for stored procedures, but also for non-stored ones (anonymous blocks).

I never said anything to the contrary, I was merely making a joke about naming.

PL/SQL is not a knock off of ADA although they share some similarities. It would be a very bad idea to pass off PL/SQL experience as ADA experience.

Again, it's a joke Joyce.

bigred was under the impression that Oracle didn't use SQL at all, I was simply explaining that it did indeed, and I apologise for not being rigorous enough in my use of terms.

 

[bigred]

PL/SQL is a knock-off of ADA,

Yer kidding.

It lives! Noooooooooooooooooooooo

v/r
Old AF Programmer Guy

 

[VPescado]

OK fine, I was trying to keep it general and simple. Your distinction that "PL/SQL is not the flavor of SQL used by Oracle: PL/SQL is a procedural language built around SQL" is irrelevent when one is actually writing Oracle queries. At least it never mattered to me.

That you can use languages other than SQL to program against Oracle is not relevent to a discussion about SQL.

You are correct, however using pro*C or jdbc is not using a language other than SQL. It is using another language to deliver SQL to the database.

SQL is a query language. Other languages have facilities to deliver sql queries to the db. I repeat: PL/SQL is absolutely not an implementation of SQL. When you compile a pl/sql package body the pl/sql compiler hands off all encountered sql to the sql parser. (An interesting aside: When creating/replacing a pl/sql package via sql*plus, three levels of parsing occur: The script is handled by the sql*plus parser, who hands off the "Create Package . . ." statement to the pl/sql complier who hands of the embedded sql to the SQL engine.)

Similarly at runtime, There is a very real interface between the sql and pl/sql - and this can cause problems (particularly when dealing with binding to large types like CLOBS where SQL and PL/SQL have different ideas of how big is big enough).

In short - PL/SQL is entirely seperate from SQL.

 

[bigred]

I'm not sure I should even "go here" but....

So when using PL/SQL you don't use SQL commands (eg CREATE, JOIN, INSERT, etc)?

I hate misnomors like that. It's like "Java" vs "Javascript" - logically you'd think they were tightly related, when in fact they're very different things.

 

[VPescado]

I'm not sure I should even "go here" but....

So when using PL/SQL you don't use SQL commands (eg CREATE, JOIN, INSERT, etc)?

I hate misnomors like that. It's like "Java" vs "Javascript" - logically you'd think they were tightly related, when in fact they're very different things.

In the course of normal plsql programming you will be typing in those sql statements (e.g. Select, Create, update, insert, delete, etc.) - but they will be embedded in the pl/sql code and technically not a part of it. Although it is possible to write pl/sql code that contains no sql - usually there are better languages to do whatever you would be trying to accomplish (although sometimes it happens when you are coding a cover over another pl/sql procedure or function).

Additionally, there are many ways of executing sql other than using pl/sql (sql*plus, pro*c (and other language) precompilers, java, etc.).

 

[peptoabysmal]

You meant to say there isn't anything that even comes close to TOAD for Oracle that you know of, right?

Fair enough. I'll giove a try to the product you recommend. I didn't mean to come off sounding so snobbish about TOAD. TOAD is very expensive, and I'm open to less expensive alternatives that give close to the same functionality. I actually kind of like Aqua Data Studio which is even cheaper yet, and because it is Java and plays on most of the platforms I use. Aqua doesn't come close to TOAD for heavy duty PL/SQL, optomizing, reverse engineering and all that good stuff.

PL/SQL Developer by Allround Automations in the Netherlands is an Oracle development tool that I highly recommend for anyone developing against Oracle. In every case where I've told a developer who was using TOAD free about the PL/SQL Developer product, he's gone out and bought himself a copy after playing with it for a day or two.

You can download the full version from their site and play with it for a month for free, and at US$180 for the full version it's a bargain compared to the commercial edition of TOAD.

TOAD free is not a valid comparison. It is way out of date. You can download the full versions of TOAD from Quest also limited to 30 days.

I checked the site you point to and it looks like a good PL/SQL tool and suffers from the same limitation as TOAD in that it only runs on Windows.

This part made me laugh out loud, though:

Command Window
To develop and execute SQL scripts you can use PL/SQL Developer's Command Window. This window has the same look and feel as SQL*Plus, and additionally has a built-in script editor with corresponding syntax highlighting. Now you can develop your scripts without the "edit script / save it / switch to SQL*Plus / run script" cycle, and without leaving the comfortable PL/SQL Developer IDE.

oooooo the same look and feel as SQL*Plus... I can't wait