From my book "Effective Oracle by Design"

<quote>

Why PL/SQL?

A question that I field frequently and is a great lead-in for this first section is, "Since Oracle supports Java, PL/SQL, and C in the database, why would you use PL/SQL?"

It should speak volumes about PL/SQL's capabilities that entire products are written in the language. Here are a few examples:

Oracle's advanced replication was implemented in PL/SQL entirely in version 7.1.6 of the database. Over the years, parts of it have been internalized in C for the performance edge that language has. However, major portions of the replication functionality remain in PL/SQL.

Oracle's Application Suite (HR, Financial, ERP, and so on) was written in PL/SQL. Today, Java is being used as well for middle-tier GUI processing, but originally, the whole suite was in PL/SQL and today most all of the data processing still is.

Oracle's Workflow engine, which lives in the database, is written in PL/SQL.

The administrative interface to the database is written in PL/SQL. These include DBMS_STATS, UTL_OUTLN, and DBMS_RESOURCE_MANAGER, just to name a few.


Despite its proven power and versatility, many developers still tend to overlook the language. They work hard to implement a feature in a different language, when using PL/SQL would have been much quicker and more effective.

All you need to use PL/SQL is a good text editor and SQL*Plus. It is not very hard to get started with PL/SQL.

PL/SQL Is the Most Efficient Language for Data Manipulation

If you are manipulating data, PL/SQL is quite simply the most productive language to use. Consider a simple routine to fetch data from the database and perform some process against it. The PL/SQL routine might look like this:


Create or replace procedure process_data( p_inputs in varchar2 )
As
Begin
For x in ( select * from emp where ename like p_inputs )
Loop
Process( X );
End loop
End;


Here, we can make the following observations:

PL/SQL datatypes are SQL datatypes. There is no conversion between our programming language types and the database; they are the same.

The tight coupling between the language and the database is visible in the ease with which we can commingle SQL and PL/SQL. Consider the line For x in ( select ). The compiler knows how to define x, creates a record type for us implicitly, and does all of the work to get the data from the query into our implicitly defined record.

We don't need to do things like open and close queries; they are taken care of for us.

We are protected from many changes in the database. If we were to add or remove a column in the table, this procedure would remain unchanged. We would not need to revisit this piece of code at all.


Something you don't see, but that is there nevertheless, is the implicit cursor caching performed by PL/SQL. If we were to call that procedure 1,000 times with different inputs, we would discover that the SELECT statement is parsed once and executed 1,000 times. PL/SQL understands the importance of "parse once, execute many" for performance and scalability, and that approach is burned into the language itself.


Now, compare that with the equivalent Java/JDBC code:

static PreparedStatement pstmt = null;

public static void process_data( Connection conn, String inputs )
throws Exception
{
int empno;
String ename;
String job;
int mgr;
String hiredate;
int sal;
int comm;
int deptno;

if ( pstmt == null )
pstmt = conn.prepareStatement
("select * from emp where ename like ? " );

pstmt.setString( 1, inputs );
ResultSet rset = pstmt.executeQuery();

while( rset.next() )
{
empno = rset.getInt(1);
ename = rset.getString(2);
job = rset.getString(3);
mgr = rset.getInt(4);
hiredate = rset.getString(5);
sal = rset.getInt(6);
comm = rset.getInt(7);
deptno = rset.getInt(8);
// process( empno, ename, job, mgr, hiredate,
// sal, comm, deptno );
}
rset.close();
}

None of the items on our previous list apply anymore:


Java types are not SQL types and, in fact, we must take care to choose our Java types wisely. An Oracle NUMBER column can hold 38 digits. What Java type should we use in order to retrieve the NUMBER column and not lose precision or overflow it?

There is no coupling between the SQL language and Java/JDBC. It is a totally procedural API. We must manually control everything procedurally.

We are not protected from changes in the database. If we add, modify, or drop a column, our Java code will need to be revisited and changed.

We must perform statement caching manually (the PreparedStatement, pstmt, in the example).

This is not a criticism of Java, or C for that matter (C is one of my favorite
programming languages). The corresponding C code would look very similar to the example here in its verbosity, and it would have the same issues. These are great languages. It is just that if our goal is to process data, using a lot of SQL with a little bit of procedural work mixed in, PL/SQL is by far the most productive language to use, both in terms of speed and programmer productivity.


PL/SQL Is Portable and Reusable


Here's my contention: PL/SQL is more portable and reusable than virtually any other language out there. Now, this raises a few eyebrows every time (probably yours are raised right now), so let me explain my logic.


Sure, Java and C are portable, reusable languages-within reason. However, Java is predominantly reusable by Java, and C is predominantly reusable by C. While it is possible for Java, C, and other languages to interoperate, to invoke each other, it is not natural. With PL/SQL, on the other hand, anything that can connect to the database in any way, shape, or form can invoke PL/SQL. It is true that technologies such as web services or CORBA allow the same for Java or C, but these technologies introduce their own extra levels of complexity at times. Of course, there is no reason your web service should not be written in PL/SQL! If your service is doing data manipulation, then writing it in PL/SQL might be the most efficient, secure way to do it.


The bottom line is that if you can connect to the database, you can use existing PL/SQL. If you look at the database itself, the predominant API to configure, adjust, and use the database is a PL/SQL-based API. Oracle uses it for replication, workflow, job scheduling, resource management planning, OLAP functionality, data mining, and so on. PL/SQL is the universal language you can use to talk to the database.


As with any language, however, you can write good code, you can write sufficient code, and you can write some of the worst, most miserably performing pieces of code humanly possible. PL/SQL is not magic. You can make all of the same mistakes in it you can make in many other languages. You can code infinite loops (but the database does allow you to set up resource profiles that would abort the offending code without bringing down the database). You can code logic errors. You can make many of the same errors you could in Java, C, Visual Basic, and the like. But you will find that many errors you might make in those languages are hard, if not impossible, to make in PL/SQL. Here are some examples:


Bind variable usage In PL/SQL, it is impossible to not use bind variables with static SQL. It is only possible to not use bind variables when you do dynamic SQL in PL/SQL, and even then, it is easier to code using bind variables than it is to code not using bind variables in PL/SQL. Many times, other languages make it not only possible, but easier to skip using bind variables. And, as you've learned in previous chapters, bind variable usage is key to database scalability and performance.


Parse once, execute many In PL/SQL, it is impossible to overparse a statement when using static SQL. You need to program using dynamic SQL in order to achieve this state (a state you do not want to be in). PL/SQL automatically caches statements for us transparently, providing its parse once, execute many capability. It is true that by using (or overusing) dynamic SQL, you can achieve the dubious goal of parsing as many times as you execute a statement, but done correctly, even dynamic SQL can parse once, execute many in PL/SQL.


SELECT * In PL/SQL it is safe to use SELECT *. It might not be the best approach as far as performance goes (it is always best to select only those columns you actually need). But in all other languages, SELECT * is something that you cannot afford to put into any programs at all! Simply re-creating a table with the columns defined in a different order, adding a column, or dropping a column will break your application (but not in PL/SQL; PLSQL will fix itself when properly coded).

Database schema changes Suppose that your application modifies a COMMENTS column from VARCHAR2(80) to VARCHAR2(255). All existing applications that are not written in PL/SQL must be reprogrammed to accommodate this. Correctly written PL/SQL applications will not be affected.

I didn't know you were using that This error happens due to lack of dependency management tracking. Someone just didn't know you used a particular object and felt free to change it. With PL/SQL, the linkage between who uses what and where is stored right in the data dictionary! The opportunities to claim ignorance ("I didn't know") are very much reduced.


Not only is PL/SQL a more productive environment for creating data-oriented routines to be called from a variety of languages, it is a safer one as well. It is quite simply harder to make the most common programming mistakes in Oracle if you are using PL/SQL.


Now that we've gone over the reasons to use PL/SQL, the rest of this chapter is devoted to the ten most important things regarding PL/SQL programming/development. Some of it is general programming advice, such as write as little as you can. Other items are specific to the PL/SQL language itself, such as use %TYPE and %ROWTYPE. They are not in any specific order; they are all equally relevant.


Note: A technique that is not listed here explicitly, but is sort of a theme of this book is, to benchmark. Use tools such as TKPROF, DBMS_PROFILER, and Runstats in order to evaluate how you are doing, how well your code is performing, and how different approaches to the same problem stack up against each other.
</quote>



You will definitely see the SQL in V$SQL and everywhere else -- it might not look exactly like you coded it (plsql has to preprocess it, set up bind variables). SQL_TRACE can be used to see what SQL is actually executed easily.

Have you looked at Jdeveloper?

integrated plsql IDE (code sensitive, code completion, etc...)

builtin source code debugger, watch variables, etc

It (jdev) has been doing that for a couple of years now, far above the capabilities of the rather old plsql developer (part of forms from client server days)

"it seemed"


perhaps query 1 did tons of physical IO, that query run 2 benefited from.
perhaps query 1 ran at peak processing time, whereas query 2 ran when most had gone home.


tkprof will shed a great deal of light on this situation. trace it, tkprof it, see what is happening.