Pl/sql engine, PL-SQL Programming

Assignment Help:

Architecture

The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL blocks and the subprograms. The engine can be installed in an Oracle server or in an application development tool like Oracle Forms or Oracle Reports. Therefore, PL/SQL can reside in two environments:

(i) The Oracle server

(ii)Oracle tools

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

2408_engine.png

Figure: PL/SQL Engine

(i)In the Oracle Server

The Application development tools which lack a local PL/SQL engine should rely on Oracle to process the PL/SQL blocks and subprograms. Whenever it contains the PL/SQL engine, an Oracle server can process the PL/SQL blocks and subprograms as well as single the SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine.

Anonymous Blocks

The Anonymous PL/SQL blocks can be embedded in an Oracle Pre-compiler or the OCI program. At run time, the program, lacking a local PL/SQL engine, send these blocks to the Oracle server, where they are compiled and executed. Similarly, interactive tools like SQL Plus and Enterprise Manager, lacking a local PL/SQL engine, should send an anonymous block to the Oracle.

Stored Subprograms

The Subprograms can be compiled individually and stored permanently in an Oracle database, and is ready to be executed. A subprogram explicitly CREATED using an Oracle tool is known as stored subprogram. Once it is compiled and stored in the data dictionary, it is a schema object that can be referenced by any number of applications connected to that database.

The Stored subprograms defined within a package are known as packaged subprograms. Those defined independently are known as stand-alone subprograms. Those Stored subprograms defined within another subprogram or within a PL/SQL block are known as local subprograms, that cannot be referenced by other applications and exist only for the convenience of the enclosing block.

The Stored subprograms offer higher productivity, memory savings, better performance, application integrity, & tighter security. For e.g., by designing the applications around a library of stored procedures and functions, you can avoid the redundant coding and increase your productivity.

You can call stored subprograms from a database trigger, another stored subprogram, an OCI application, an Oracle Precompiler application, or interactively from SQL Plus or Enterprise Manager. For e.g., you might call the stand-alone procedure create_dept from SQL Plus as shown:

SQL> CALL create_dept('FINANCE', 'NEW YORK');

Database Triggers

A database trigger is a stored subprogram related with a table. You can have Oracle that automatically fire the database trigger before or after an INSERT, DELETE, or UPDATE statement that affects the table. One of the various uses of database triggers is to audit the data modifications. For e.g., the following database trigger fires whenever the salaries in the emp table are updated:

CREATE TRIGGER audit_sal

AFTER UPDATE OF sal ON emp

FOR EACH ROW   

BEGIN

INSERT INTO emp_audit VALUES ...

END;

You can use all SQL data manipulation statements and any procedural statement in the executable part of a database trigger.

(ii)In Oracle Tools

Whenever it contains the PL/SQL engine, an application development tool can process the PL/SQL blocks and subprograms. The tool that passes the blocks to its local PL/SQL engine. The engine executes all the procedural statements at the application site and sends only SQL statements to Oracle. And hence, most of the work is completed at the application site, not at the server site.

Moreover, if the block contains no SQL statements, the engine executes the whole block at the application site. This is very useful if your application can benefit from conditional and iterative control.

Often, the Oracle Forms applications use SQL statements merely to test the value of the field entries or to do simple calculations. By using the PL/SQL instead, you can avoid calls to the Oracle server. Furthermore, you can use PL/SQL functions to manipulate the field entries.


Related Discussions:- Pl/sql engine

Interesting properties of cross join - sql, Interesting properties of CROSS...

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

I need data entry conversion project, Project Description: This is stage...

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S

Row counterparts of table operators - sql, Row Counterparts of Table Operat...

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Extension and and in sql, Extension and AND in SQL The theory book giv...

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Oracle 9i features, Bitmap Join Indexes - This feature will increase th...

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur

Parameter & keyword description - functions, Parameter & Keyword Descriptio...

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

Wrapping and unwrapping in sql, Wrapping and unwrapping in SQL Operato...

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Cursor attributes in dynamic sql - pl sql, Using Cursor Attributes: Ev...

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Group and having query, Using a join on 2 tables, select all columns and 10...

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd