What is Dynamic PSP?
Dynamic PSP, or DPSP, is a server-side scripting technology for Oracle8i/9i RDBMS.
It is installed into Oracle8i/9i and becomes instantly available. DPSP provides web-based
development environment for rapid internet application development. It gives Oracle developers
unprecedented flexibility and greatly facilitates application development while offering
compatibility with existing Oracle tools and technologies.
What are the differences between Oracle PSP and Dynamic PSP?
Although Oracle has a term 'PSP' with the same meaning of 'PL/SQL Server Pages', it differs seriously from our DPSP implementation. After a bit of playing with Oracle's PSP we decided that it is lacking the functionality we needed, so we decided to create our own server-side extension to Oracle RDBMS/AS and devised it Dynamic PSP as this abbreviation best describes what we intend to achieve but sets us aside from Oracle's PSP approach. We believe DPSP is a viable alternative to Oracle's PSP as well as JSP, because it doesn't require experienced Oracle PL/SQL programmers to learn Java and doesn't limit them in the way they can get things done. DPSP shares common syntax with Oracle's PSP, but extends it with some helpful features, like a number of predefined functions, dynamic execution (unlike Oracle's one-time compilation approach), unlimited number of parameters to any DPSP object (in PSP, number of parameters is fixed), and more.
In Oracle PSP you write a dynamic page as a text file and then use a special utility that parses your page, creates a package and loads/compiles that package into Oracle RDBMS. Each external parameter you define in PSP page becomes a parameter of the procedure in the package. This limits number and names of external parameters for this particular PSP page. Each time a change in functionality of the page is required, you need to edit the source file and reload the page into Oracle. On the contrary, each DPSP block is stored in the database, it has unique numeric identifier (ID) and unique string name, and it is parsed and compiled dynamically when accessed. These blocks are called DPSP units. DPSP unit can represent a complete page or a logical block (subroutine) that can be called from another DPSP unit. DPSP units can be called from each other either by ID or by name, and can accept unlimited number of external named parameters. DPSP units are dynamically parsed and compiled and all changes you made to them become instantly available. DPSP units can also be compiled into packages (in fact, this is a mandatory step for publishing a unit when recommended secure setup procedure is followed).
Why not use JSP?
JSP, or Java Server Pages, are gaining respect from web developers for a number of useful features they provide, like simple interfacing with EJB (Enterprise Java Beans), general portability, etc. Still, JSP is not a scripting technology of choice for Oracle developers. Reason is simple: JSP pages are compiled into Java classes and are run outside the database, all database operations are performed via JDBC and thus they are less optimal than those performed with PL/SQL right inside the database. Besides, Java code is slower than PL/SQL when it comes to database operations. And development process requires reupload to the web server and recompilation of the page each time it's changed, no matter how extensive the change was.
PSP and DPSP have numerous advantages over JSP - they reside inside the Oracle database, PL/SQL is native Oracle language and it is usually executed faster than corresponding Java code when it comes to data manipulation and is suited best for database operations. All in all, Java is a general purpose programming language while PL/SQL was designed specifically for effective database programming. Yet PL/SQL is well suited for general application development as well. If there is anything that can't be done easily with PL/SQL, it can be done with Java, because Java is also integrated into Oracle8i/9i. PSP and DPSP don't restrict developer in a number of available features - everything that can be done with plain PL/SQL, can be done with PSP. You can create special packages or procedures and call them from DPSP as if they were native DPSP features. You can create Java classes within Oracle database, then define PL/SQL interface wrappers for them and use them in DPSP pages as well.
Why Oracle still supports PL/SQL when they integrated Java into Oracle RDBMS?
You can find this out at Oracle Technology Network,
but here's a small primer:
How To Secure Dynamic PSP Development Interface?
There are several ways to secure the Dynamic PSP Development Interface on Apache HTTP server:
I. Securing the communications between DPSP server and developers.
To secure communications between DPSP server and developers' seats you need to setup SSL to encrypt the traffic between them and optionally authenticate developers. By enabling SSL you will ensure that even if your traffic is intercepted by third party, it will be encrypted using strong cryptography and will not be compromised.
Enabling SSL on Apache involves several steps:
When these steps are complete, your server will be accessible via HTTPS and all traffic between server and clients will be encrypted when using HTTPS.
II. Restricting access to the Development Interface
All examples below assume that the Dynamic PSP Development Interface DAD is
There are several ways to restrict access to the DPSP Development Interface.
How to enable native PL/SQL compilation in 9i Release 2 on Windows?By Vladimir M. Zakharychev <bob at dpsp-yes dot com>
Native PL/SQL compilation (this link leads to OTN site, which needs registration to access - registration is free) is a new feature of Oracle9i Database. As such, it was not implemented in 9i Release 1 on Windows (this fact was accounted as a bug in 9iR1). Release 2 was said to support ncomping the PL/SQL on Windows, but in reality there's a lot of things you need to do before you can compile your PL/SQL natively. Here are the steps necessary to make it work on Release 2 (22.214.171.124):
That's it. With the last step you defined make utility, makefile and path to receive compiled files and Oracle is now ready to ncomp your PL/SQL. To test if everything is in order, run the test from Oracle documentation
that demonstrates PL/SQL ncomping feature (slightly modified to remove bugs in the source code from documentation):
alter session set plsql_compiler_flags='NATIVE'; create or replace procedure hello_native_compilation as dt DATE; begin dbms_output.put_line('Hello world'); select sysdate into dt from sys.dual; dbms_output.put_line('Today is '||to_char(dt,'DD Mon, YYYY HH24:MI:SS')); end; /
If you get any errors here, use SHOW ERROR command in SQL*Plus and fix the problem if you can figure it out - regretably, ncomping cuts the make output and it is not always visible what went wrong (but of course,
you are guaranteed to see the Microsoft NMake banner in its entirety. ;) To avoid seeing the NMake banner, you can add /NOLOGO switch to the NMake call you defined in plsql_native_make_utility:
Final note: make sure you protected the Makefile,
P.S. Recently I was asked if it is possible to setup Oracle on Windows to use GCC compiler for PL/SQL compilation. The answer is: definite yes. I've put together a Makefile that uses GCC 3.3 with MinGW for compiling PL/SQL natively and successfully tested it with GCC 3.3.3 (cygwin special) with MinGW libraries. You can download the makefile here: Makefile.mingw. Read through the comments in this file, make necessary modifications and you should be able to compile your PL/SQL with GCC on Windows.
I am unable to login into Unit Commander using default account.
The README.txt file accompanying the Dynamic PSP distribution archive describes one important patch to Oracle OWA
package for the bug, which prevents authorization to take place. Please see Section 4 in the README.txt
file for detailed instructions on how to fix this bug.
What is WebDAV?
Briefly: WebDAV stands for "Web-based Distributed Authoring and Versioning". It is a set of extensions to the HTTP protocol which allows users to collaboratively edit and manage files on remote web servers.
How to avoid redirection from index.html to DPSP unit?
Use Apache mod_rewrite directive:
Java class to convert xls to csv or from MS Excel to CSV file into PL/SQL and upload data from .xls file to Oracle
* gocsv.ConvertCSV Version 2.00
Java class to convert from DBF (DB2) to CSV file within Oracle PL/SQL