Plsql Server Pages

PL/SQL Server Pages
Oracle PL/SQL Server Pages (PSP) is Oracle's PL/SQL dynamic server-side scripting solution for Web application development. Oracle PSP includes the PL/SQL Server Pages Compiler and the PL/SQL Web Toolkit. Oracle PSP enables PL/SQL users to develop Web pages with dynamic content by embedding PL/SQL scripts in HTML. PSPs separate application logic (embedded PL/SQL scripts) from the layout logic (HTML) making the development and maintenance of PL/SQL Server Pages easy. By using this method, content developers design the static portions of Web pages in HTML, then add scripts that generate the dynamic portions of the pages. In addition, PSP tightly integrates with the database so it is easy to retrieve, manipulate, and present data.
Advantages of Using PSP
PL/SQL Server Pages provide server-side scripting with traditional database operations and programming logic for developing Web-based applications. The advantages of using PL/SQL Server Pages include the following:
• Oracle PSP supports HTML and HTML authoring tools with added dynamic content or applications. You can start with an existing Web page or with an existing stored procedure to create dynamic Web pages that perform database operations and display the results.
• Typically, PL/SQL Server Pages display in a Web browser. By default, Oracle8i PL/SQL transmits files as HTML documents, so that the browser formats them according to the HTML tags. Files can also be retrieved and interpreted by a program that can make HTTP requests, such as a Java or Perl application.
• The PL/SQL Web Toolkit allows you to build PL/SQL code that produces formatted output. You can specify the output format as XML, a MIME type, or plain text, depending on the technology supported in the user's browser.
• You can run insert, update, and delete operations within PL/SQL Server Pages. As with any program that is expected to return results as HTML, such pages should include some output to confirm that the operation is successful or to show the updated state.
• You can share procedures, constants, and types across different PL/SQL server pages, and compile them into a separate package in the database.
• To handle database errors that occur when the script runs, you can include PL/SQL exception-handling code within a PSP file, which may launch another PSP to handle or to report the error.
Developing PSP Applications
Developers use the Oracle PL/SQL Web Toolkit to develop their PSP applications. The PL/SQL Web Toolkit contains a set of packages you can use in stored procedures to retrieve request information, construct HTML tags, and return header information to the client.
PL/SQL Server Pages compile to PL/SQL stored procedures. Compiling an HTML file as a PL/SQL Server Page produces a stored procedure that outputs the exact same HTML file. The PSP is an HTML file mixed with PL/SQL procedures combining all the content and formatting of your Web page. The HTML file contains text and tags interspersed with PSP directives, declarations, and scripts.
Deploying PSP Applications
Oracle8i PL/SQL and mod_plsql in Oracle9i Application Server provide support for deployment and performance of your PL/SQL Server Pages. By deploying PSPs on the middle-tier, the server centrally manages the application logic saving in administration and maintenance costs and optimizing performance of your PL/SQL applications. Oracle HTTP Server forwards PL/SQL request(s) to the PL/SQL engine with the plug-in mod_plsql. Applications invoke PL/SQL scripts and stored procedures to retrieve data from a database, then generate HTML pages that return the data to the client browser.
Once the PSP has been compiled into a stored procedure, you can run it by retrieving an HTTP URL through a Web browser. The virtual path in the URL depends on the way that mod_plsql is configured.
The POST and GET methods in the HTTP protocol tell browsers how to pass parameter data to the applications. The POST method passes the parameters directly from an HTML form and are not visible in the URL. The GET method passes the parameters in the query string of the URL. You can use the GET method to call a PSP from an HTML form, or you can use an HTML link to call the stored procedure with a given set of parameters.

Processing PSP Requests
The process of handling a PL/SQL Server Page is illustrated in Figure.


1. The Oracle HTTP Server receives a PL/SQL Server Page request, through Oracle Web Cache, from a client browser.
2. The Oracle HTTP Server routes the request to mod_plsql.
3. The request is forwarded by mod_plsql to Oracle8i PLSQL. By using the configuration information stored in your Database Access Descriptor (DAD), mod_plsql connects to the database, prepares the call parameters, and invokes the PL/SQL procedure in the database.
4. The PL/SQL procedure generates an HTML page using data and stored procedures accessed from the database.
5. The response is returned to mod_plsql.
6. The Oracle HTTP Server sends the response, through Oracle Web Cache, to the client browser.

Searched CASE Expression Example

SQL> select ename, job
2 , case when job = 'TRAINER' then ' 10%'
3 when job = 'MANAGER' then ' 20%'
4 when ename = 'SMITH' then ' 30%'
5 else ' 0%'
6 end as raise
7 from employees
8 order by raise desc, ename;
ENAME JOB RAISE
-------- -------- -----
BLAKE MANAGER 20%
CLARK MANAGER 20%
JONES MANAGER 20%
ADAMS TRAINER 10%
FORD TRAINER 10%
SCOTT TRAINER 10%
SMITH TRAINER 10%
ALLEN SALESREP 0%
JONES ADMIN 0%
KING DIRECTOR 0%
MARTIN SALESREP 0%
MILLER ADMIN 0%
TURNER SALESREP 0%
WARD SALESREP 0%
14 rows selected.
SQL>

Using the ESCAPE Option of the LIKE Operator

If you really want to search for actual percent sign or underscore characters with the LIKE operator, you need to suppress the special meaning of those characters. You can do this with the ESCAPE option of the LIKE operator, as demonstrated here:

SQL> select empno, begindate, comments
2 from history
3 where comments like '%0\%%' escape '\';
EMPNO BEGINDATE COMMENTS
-------- ----------- ----------------------------------------------------
7566 01-JUN-1989 From accounting to human resources; 0% salary change
7788 15-APR-1985 Transfer to human resources; 0% salary raise
SQL>

The WHERE clause here searches for 0% in the COMMENTS column of the HISTORY table. The backslash (\) suppresses the special meaning of the second percent sign in the search string. Note that you can pick a character other than the backslash to use as the ESCAPE character.

Data Dictionary

If you are interested in knowing which tables are present in your database, which columns they have, whether or not those columns are indexed, which privileges are granted to you, and similar information, you should query the data dictionary. Another common term for data dictionary is catalog.

DESCRIBE command; this command queries the data dictionary under the hood.

The data dictionary is more or less the internal housekeeping administration of Oracle.
The data dictionary stores information about the data, also referred to as metadata. The data dictionary is automatically maintained by Oracle; therefore, the data dictionary is always up-to-date.

Oracle Data Types

NUMBER Datatype Examples
Example Description
NUMBER(4) An integer with a maximum length of four digits
NUMBER(6,2) A number with a maximum precision of six digits; at most two digits behind
the decimal point
NUMBER(7,-3) A multiple of thousand with at most seven digits
NUMBER Identical to NUMBER(38,*)
NUMBER(*,5) Identical to NUMBER(38,5)

Character Datatype Examples
Example Description
VARCHAR2(25) Alphanumeric, variable length, up to 25 characters
CHAR(4) Alphanumeric, fixed length, four characters
CLOB Alphanumeric, larger than the maximum size of the VARCHAR2 datatype

Maximum Datatype Sizes
Datatype Maximum Size
NUMBER 38 digits precision
CHAR 2000
VARCHAR2 4000
CLOB 4GB

SQL Objects

SQL objects are schemas, data dictionaries, journals, catalogs, tables, aliases, views, indexes, constraints, triggers, sequences, stored procedures, user-defined functions, user-defined types, and SQL packages. SQL creates and maintains these objects as system objects.

Database Schema

A database schema is a logical collection of database objects (such as tables, indexes,
views, and so on) that is usually owned by the user of the same name.

How Relational Is My DBMS?

The term relational is used (and abused) by many DBMS vendors these days. If you want to
determine whether these vendors speak the truth, you are faced with the problem that relational
is a theoretical concept. There is no simple litmus test to check whether or not a DBMS
is relational. Actually, to be honest, there are no pure relational DBMS implementations.
That’s why it is better to investigate the relational degree of a certain DBMS implementation.
This problem was identified by Ted Codd, too; that’s why he published 12 rules (actually,
there are 13 rules, if you count rule zero, too) for relational DBMS systems in 1986. Since then,
these rules have been an important yardstick for RDBMS vendors. Without going into too
much detail, Codd’s rules are listed here, with brief explanations:


0. Rule Zero: For any DBMS that claims to be relational, that system must be able to
manage databases entirely through its relational capabilities.
1. The Information Rule: All information in a relational database is represented explicitly
at the logical level and in exactly one way: by values in tables.
2. Guaranteed Access Rule: All data stored in a relational database is guaranteed to be
logically accessible by resorting to a combination of a table name, primary key value,
and column name.
3. Systematic Treatment of Missing Information: Null values (distinct from the empty
string, blanks, and zero) are supported for representing missing information and
inapplicable information in a systematic way, independent of the datatype.
4. Dynamic Online Catalog: The database description is represented at the logical level
in the same way as ordinary data, so that authorized users can apply the same relational
language to its interrogation as they apply to the regular data.
5. Comprehensive Data Sublanguage: There must be at least support for one language
whose statements are expressible by some well-defined syntax and comprehensive in
supporting all of the following: data definition, view definition, data manipulation,
integrity constraints, authorization, and transaction boundaries handling.
6. Updatable Views: All views that are theoretically updatable are also updatable by the
system.
7. High-Level Insert,Update, and Delete: The capability of handling a table or a view as
a single operand applies not only to the retrieval of data, but also to the insertion,
updating, and deletion of data.
8. Physical Data Independence: Application programs remain logically unimpaired
whenever any changes are made in either storage representations or access methods.

9. Logical Data Independence: Application programs remain logically unimpaired when
information-preserving changes that theoretically permit unimpairment are made to
the base tables.
10. Integrity Independence: Integrity constraints must be definable in the relational data
sublanguage and storable in the catalog, not in the application programs.
11. Distribution Independence: Application programs remain logically unimpaired when
data distribution is first introduced or when data is redistributed.
12. The Nonsubversion Rule: If a relational system also supports a low-level language,
that low-level language cannot be used to subvert or bypass the integrity rules and
constraints expressed in the higher-level language.

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing
language that offers the following advantages:
Full Support for SQL
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction
control commands, as well as all the SQL functions, operators, and pseudocolumns.
So, you can manipulate Oracle Database data flexibly and safely. PL/SQL fully
supports SQL datatypes, reducing conversions as data is passed between
applications and the database.
Dynamic SQL is a programming technique that lets you build and process SQL
statements "on the fly" at run time. It gives PL/SQL flexibility comparable to
scripting languages such as Perl, Korn shell, and Tcl.
Tight Integration with Oracle Database
PL/SQL supports all the SQL datatypes. Combined with the direct access that SQL
provides, these shared datatypes integrate PL/SQL with the Oracle Database data
dictionary.
The %TYPE and %ROWTYPE attributes let your code adapt as table definitions
change. For example, the %TYPE attribute declares a variable based on the type of a
database column. If the column's type changes, your variable uses the correct type
at run time. This provides data independence and reduces maintenance costs.
Better Performance
If your application is database intensive, you can use PL/SQL blocks to group SQL
statements before sending them to Oracle Database for execution. This can
drastically reduce the communication overhead between your application and
Oracle Database.
PL/SQL stored procedures are compiled once and stored in executable form, so
procedure calls are quick and efficient. A single call can start a compute-intensive
stored procedure, reducing network traffic and improving round-trip response
times. Executable code is automatically cached and shared among users, lowering
memory requirements and invocation overhead.

Higher Productivity
PL/SQL adds procedural capabilities, such as Oracle Forms and Oracle Reports. For
example, you can use an entire PL/SQL block in an Oracle Forms trigger instead of
multiple trigger steps, macros, or user exits.
PL/SQL is the same in all environments. As soon as you master PL/SQL with one
Oracle tool, you can transfer your knowledge to others, and so multiply the
productivity gains. For example, scripts written with one tool can be used by other
tools.
Scalability
PL/SQL stored procedures increase scalability by centralizing application
processing on the server. Automatic dependency tracking helps you develop
scalable applications.
The shared memory facilities of the shared server (formerly known as
Multi-Threaded Server or MTS) enable Oracle Database to support many thousands
of concurrent users on a single node. For more scalability, you can use the Oracle
Connection Manager to multiplex network connections.
Maintainability
Once validated, a PL/SQL stored procedure can be used with confidence in any
number of applications. If its definition changes, only the procedure is affected, not
the applications that call it. This simplifies maintenance and enhancement. Also,
maintaining a procedure on the server is easier than maintaining copies on various
client machines.

PL/SQL Support for Object-Oriented Programming
Object Types An object type is a user-defined composite datatype that encapsulates a
data structure along with the functions and procedures needed to manipulate the
data. The variables that form the data structure are called attributes. The functions
and procedures that characterize the behavior of the object type are called methods,
which you can implement in PL/SQL.
Object types are an ideal object-oriented modeling tool, which you can use to
reduce the cost and time required to build complex applications. Besides allowing
you to create software components that are modular, maintainable, and reusable,
object types allow different teams of programmers to develop software components
concurrently.

Collections A collection is an ordered group of elements, all of the same type (for
example, the grades for a class of students). Each element has a unique subscript
that determines its position in the collection. PL/SQL offers two kinds of
collections: nested tables and varrays (short for variable-size arrays).
Collections work like the set, queue, stack, and hash table data structures found in
most third-generation programming languages. Collections can store instances of an
object type and can also be attributes of an object type. Collections can be passed as
parameters. So, you can use them to move columns of data into and out of database
tables or between client-side applications and stored subprograms. You can define
collection types in a PL/SQL package, then use the same types across many
applications.
Portability
Applications written in PL/SQL can run on any operating system and hardware
platform where Oracle Database runs. You can write portable program libraries and
reuse them in different environments.
Security
PL/SQL stored procedures let you divide application logic between the client and
the server, to prevent client applications from manipulating sensitive Oracle
Database data. Database triggers written in PL/SQL can prevent applications from
making certain updates, and can audit user queries.
You can restrict access to Oracle Database data by allowing users to manipulate it
only through stored procedures that have a restricted set of privileges. For example,
you can grant users access to a procedure that updates a table, but not grant them
access to the table itself.

Built-In Packages for Application Development
DBMS_PIPE is used to communicate between sessions.
DBMS_ALERT is used to broadcast alerts to users.
DBMS_LOCK and DBMS_TRANSACTION are used for lock and transaction
management.
DBMS_AQ is used for Advanced Queuing.
DBMS_LOB is used to manipulate large objects.

DBMS_ROWID is used for employing ROWID values.
UTL_RAW is the RAW facility.
UTL_REF is for work with REF values.
Built-In Packages for Server Management
DBMS_SESSION is for session management by DBAs.
DBMS_SPACE and DBMS_SHARED_POOL provide space information and
reserve shared pool resources.
DBMS_JOB is used to schedule jobs in the server.
Built-In Packages for Distributed Database Access
These provide access to snapshots, advanced replication, conflict resolution,
deferred transactions, and remote procedure calls.