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.