Overview of the PL/SQL -> SQL Interface

Submitted by dave on Fri, 02/19/2010 - 09:43

* Work in Progress! *

PL/SQL Blocks passes off SQL to the SQL engine.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/overview....

Static SQL from PL/SQL
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperat...

Shows the structure of the PL/SQL engine and that it passes of SQL to the Database Server.

So inside of PL/SQL we are looking at the following ways to know what is going on:
o Set Long Operations via PL/SQL API and check it via v$session_longops
o Set Program Unit and check it via v$session
o Profile it and analyze after runtime
o Custom logging (anonymous transactions?)
o Compile in debug mode and use an interactive debugger (One is provided with SQL Developer)

Hand off to SQL Engine? How to relate PL/SQL block to SQL Statements?
v$session? v$sql?

How can we attack the Opacity?