fun with psp

8/14/2019 Fun With Psp

1/24


2/24

Introduction

Examples of PSPs in action

q Document your database with PSPs

q Output the DDL to recreate database objects

q Dynamically create a Microsoft Excel

spreadsheet with data from the database

q Get parameters on an HTML form and controlwhat data is retrieved and how it is sorted.

q Facilitate electronic data interchange by

dumping data from a database into XML.


3/24

Just a Quick Review

What is a PSP?

q PSP is to PL/SQL as ASP is to Visual Basis

q PSP is to PL/SQL as JSP is to Java

Simply put:

q A PSP is HTML with embedded scriptlets

written in PL/SQL


4/24

A (very) Short Tutorial

Four basic tags

q Declaratives, marked

q Declarations, marked

q Included values, marked

q Scriptlets marked,

PSPs are written in ordinary text files

Compiled with command line utility:

loadpsp


5/24

Underlying Architecture

OAS PL/SQL Cartridge or

WebDB Lightweight Web Server or

9iAS mod_plsql (also PL/SQL Gateway)

and

PL/SQL Web Toolkit loaded in the database PSPs compile to PL/SQL procedures that

call the Web Toolkit


6/24

Documenting Your Database

PSPs can be used to document our sample

database.

Always start a project with the data

q Create a cursor package

q Example: A cursor package for the system

catalog

Now create the PSP

q Example: My database documentation PSP
http://catalog_cursors.pks/http://database_definition.psp/http://database_definition.psp/http://catalog_cursors.pks/http://database_definition.htm/


7/24
http://database_definition.htm/http://database_definition.htm/


8/24

Generalizing this Report

Original version is limited

q Can only document objects visible to the owner

of the procedure

q Needs to be Invokers Rights

PSP cant create Invokers Rights modules

q So, we have to make a post-compile changePROCEDURE database_definition (

owner IN VARCHAR2 default USER,

table_name IN VARCHAR2 default '%')

AUTHID CURRENT_USER AS

BEGIN NULL;

htp.prn('');


9/24

Database Scripts

Use the same cursor package:

Code re-use is one of the benefits

Just change the PSP:

q contentType=text/plain or

q Enclose the code in

q Example: Code for Table DDL, Output

q Example: Code for Procedure DDL
http://database_ddl.psp/http://database_ddl.txt/http://procedure_ddl1.psp/http://procedure_ddl1.psp/http://database_ddl.txt/http://database_ddl.psp/


10/24

Alternative Procedure PSP

Here is another way to output DDL for a

procedure: Example

Notice the use of a OWA_UTIL procedure

q Never re-invent the wheel

q Check out OWA_UTIL and other toolkit

procedures
http://procedure_ddl2.psp/http://procedure_ddl2.psp/http://procedure_ddl2.psp/


11/24

Reporting with Query Criteria

2 Screen Application

q Query and Sorting Criteria

q Report

Start with a Cursor Package:

q Example: Cursor Package with a Cursor

Functionq The function returns a REF CURSOR
http://tms_cursor_fun.pkb/http://tms_cursor_fun.pkb/


12/24

Query Screen

Here is the PSP for the Query Screen:

Example

Notice that this is plain HTML

q Plain HTML can be a PSP

q This adds security to the application

Have to hack the database to change the page

You can even limit who can see it - grant execute
http://tms_query_fun.psp/http://tms_query_fun.psp/


13/24
http://tms_query_fun.htm/http://tms_query_fun.htm/


14/24

Producing the Report

Each field on the form is a parameter

Pass the parameters to the cursor function

Returns a REF CURSOR with selected

rows

Loop through the rows to produce the report

Example: PSP for the report
http://tms_report_fun.psp/http://tms_report_fun.psp/


15/24
http://tms_report_fun.htm/


16/24

Reporting to Excel or Word

But what if we need a spreadsheet?q

Or a Word Document?q

q While Word (and WordPerfect for that matter)

can read HTML, you might want to try RTF.

See: http://home1.stofanet.dk/peter_lorenzen/
http://tms_excel_fun.xls/


17/24
http://tms_excel_fun.xls/http://tms_excel_fun.xls/


18/24

Generating XML

XML: The standard for Electronic Data

Interchange (EDI)

XML Toolkit is easier for simple XML

output, and good for XML input too.

IMHO, PSP is easier for more control over

XML output

Example: PSP for writing XML
http://tms_xml_fun.psp/http://tms_xml_fun.psp/


19/24
http://tms_xml_fun.xml/


20/24

Design Documents from Designer

Repository

The Oracle Repositoryq The documentations in the repository., not where

management can see it.q The reports supplied with Designer dont do it.

As usual, start with a cursor package:q Example: cursor package for the repository

q Example: cursor package body for the repository

Then write a PSP to generate the docs:q Example: Functional Requirements PSP
http://designer_cursors.pks/http://designer_cursors.pkb/http://functional_requirements.psp/http://functional_requirements.psp/http://designer_cursors.pkb/http://designer_cursors.pks/http://functional_requirements.htm/


21/24
http://functional_requirements.htm/http://functional_requirements.htm/


22/24

Conclusion

PSPs are easy - just HTML with a little

PL/SQL

PSPs are versatileq Generate Database Documentation and DDL

q Generate Reports in multiple formats

Excel, Word

XML

PSPs are Fun! Now see what you can do

with them.


23/24

About the Author

John Flack

Synectics for Management Decisions, Inc.

1901 N. Moore St.

Arlington, VA 22209

[email protected]

All the code for this presentation (and much

more) can be found at:

http://www.smdi.com/employee/JohnF

See me at:


24/24

CAESARS PALACE, LAS VEGAS

JUNE 17-21, 2002

Preconference Business Rules Symposium, Sunday, June 16

ORACLE DEVELOPMENT TOOLS USER GROUP