Re: pl/sql engine doubt

From: Bjørn Engsig <bjorn_at_miracleas.dk>
Date: Mon, 24 Mar 2003 16:00:33 +0100
Message-Id: <24765.322894@fatcity.com>

--------------060606040904080408030205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

PL/SQL can be seen as a stand-alone interpreted language and can as such exist in various environments. "Various envrionments" does in real life mean the PL/SQL engine is found in both the Oracle server and in the Oracle Forms tool. If an application needs to execute some PL/SQL it should (at least in principle) be able to execute this in any available environment where the PL/SQL engine is present, in practice, however, this is only the case in the database server and in Oracle Forms (plus probably Oracle Reports, I really don't know, but it's beside the point). In practical terms, if you are coding forms (plus probably reports), you have PL/SQL available in the tool and in the database server; in all other cases, it's only in the database server. Whenever PL/SQL needs to execute some SQL statements, it will have to go to the Oracle server; if your PL/SQL happens to already execute there, it is a simple internal context switch inside the server, if your PL/SQL happens to execute in Oracle Forms (or reports), it will have to go over your SQL*Net connection to execute the SQL code.

To confuse things somewhat, PL/SQL actually has a SQL parser; hence, PL/SQL can verify SQL statements during parse without actually talking to the database. This has some interesting side effects in version 8, where the SQL parser inside PL/SQL tend to not have adopted all the latest SQL features, i.e. there is valid SQL (in e.g. 8.1.7), that you cannot use in PL/SQL without using dynamic SQL (either DBMS_SQL or native dynamic SQL).

/Bjørn.

sam d wrote:

>Hi List,
>I was going thru Oracle "PL/SQL User's Guide and
>Reference".
>http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962
>
>Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
>in the doc) confused me little.
>
>para1 ---
>"These two environments are independent. PL/SQL is
>bundled with the Oracle server but might be
>unavailable in some tools. In either environment, the
>PL/SQL engine accepts as input any valid PL/SQL block
>or subprogram. Figure 1-4 shows the PL/SQL engine
>processing an anonymous block. The engine executes
>procedural statements but sends SQL statements to the
>SQL Statement Executor in the Oracle server."
>
>
>para2--
>"In the Oracle Database Server:
>Application development tools that lack a local PL/SQL
>engine must rely on Oracle to process PL/SQL blocks
>and subprograms. When it contains the PL/SQL engine,
>an Oracle server can process PL/SQL blocks and
>subprograms as well as single SQL statements. The
>Oracle server passes the blocks and subprograms to its
>local PL/SQL engine."
>
>
>Now my doubt is:
>1.In para1 - Does pl/sql engine only processes
>the procedural statements and always passes SQL
>statements to SQL Statement Executor ?
>
>2.In para2 sentence "When it contains ..." says
>Oracle pl/sql engine can process SQL statements.
>
>Statements in both paragraphs seems ambiguous.
>Pls shade some light.
>
>Thanks
>Sam
>
>
>__________________________________________________
>Do you Yahoo!?
>Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
>http://platinum.yahoo.com
>
>

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk


--------------060606040904080408030205
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
PL/SQL can be seen as a stand-alone interpreted language and can as such
exist in various environments. &nbsp;"Various envrionments" does in real life
mean the PL/SQL engine is found in both the Oracle server and in the Oracle
Forms tool. &nbsp;If an application needs to execute some PL/SQL it should (at
least in principle) be able to execute this in any available environment
where the PL/SQL engine is present, in practice, however, this is only the
case in the database server and in Oracle Forms (plus probably Oracle Reports,
I really don't know, but it's beside the point). &nbsp;In practical terms, if
you are coding forms (plus probably reports), you have PL/SQL available in
the tool and in the database server; in all other cases, it's only in the
database server. &nbsp;Whenever PL/SQL needs to execute some SQL statements, it
will have to go to the Oracle server; if your PL/SQL happens to already execute
there, it is a simple internal context switch inside the server, if your
PL/SQL happens to execute in Oracle Forms (or reports), it will have to go
over your SQL*Net connection to execute the SQL code.<br>
<br>
To confuse things somewhat, PL/SQL actually has a SQL parser; hence, PL/SQL
can verify SQL statements during parse without actually talking to the database.
&nbsp;This has some interesting side effects in version 8, where the SQL parser
inside &nbsp;PL/SQL tend to not have adopted all the latest SQL features, i.e.
there is valid SQL (in e.g. 8.1.7), that you cannot use in PL/SQL without
using dynamic SQL (either DBMS_SQL or native dynamic SQL).<br>
<br>
/Bj&oslash;rn.<br>
<br>
sam d wrote:<br>
<blockquote type="cite"
 cite="midF001.005707F6.20030324012839_at_fatcity.com">
  <pre wrap="">Hi List,
I was going thru Oracle "PL/SQL User's Guide and
Reference".
<a class="moz-txt-link-freetext" href="http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962">http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962</a>

Paragraphs below (near the "Figure 1-4 PL/SQL Engine"
in the doc) confused me little.

para1 ---
"These two environments are independent. PL/SQL is
bundled with the Oracle server but might be
unavailable in some tools. In either environment, the
PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4 shows the PL/SQL engine
processing an anonymous block. The engine executes
procedural statements but sends SQL statements to the
SQL Statement Executor in the Oracle server."


para2--
"In the Oracle Database Server:
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks
and subprograms. When it contains the PL/SQL engine,
an Oracle server can process PL/SQL blocks and
subprograms as well as single SQL statements. The
Oracle server passes the blocks and subprograms to its
local PL/SQL engine."


Now my doubt is:
1.In para1 - Does pl/sql engine only processes 
the procedural statements and always passes SQL 
statements to SQL Statement Executor ?

2.In para2 sentence "When it contains ..." says 
Oracle pl/sql engine can process SQL statements.

Statements in both paragraphs seems ambiguous. 
Pls shade some light.

Thanks
Sam


__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
<a class="moz-txt-link-freetext" href="http://platinum.yahoo.com">http://platinum.yahoo.com</a>
  </pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
 Bj&oslash;rn Engsig, Miracle A/S <br>
 Member of Oak Table Network <br>
 <a class="moz-txt-link-abbreviated" href="mailto:Bjorn.Engsig@MiracleAS.dk">Bjorn.Engsig@MiracleAS.dk</a> - <a class="moz-txt-link-freetext" href="http://MiracleAS.dk">http://MiracleAS.dk</a> <br>

Received on Mon Mar 24 2003 - 09:00:33 CST