DPSP
Contacts About Us


Powered by Dynamic PSP
  1. What is Dynamic PSP?
  2. What are the differences between Oracle PSP and Dynamic PSP?
  3. Why not use JSP?
  4. Why Oracle still supports PL/SQL when they integrated Java into Oracle RDBMS?
  5. How To Secure Dynamic PSP Development Interface?
  6. How to enable native PL/SQL compilation in 9i Release 2 on Windows?
  7. I am unable to login into Unit Commander using default account.
  8. What is WebDAV?
  9. How to avoid redirection from index.html to DPSP unit?
  10. 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
  11. Java class to convert from DBF (DB2) to CSV file within Oracle PL/SQL

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.
For more information on Dynamic PSP, please see Introduction to Dynamic PSP (PDF document, Adobe® Acrobat® Reader required).


Back to the top of page
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).


Back to the top of page
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.


Back to the top of page
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:

PL/SQL is still native and preferred programming language for Oracle application development. PL/SQL codebase is already massive and expands each day. Oracle will not dump PL/SQL in favor of Java because PL/SQL is best suited for database programming, while Java is a general purpose language. Adding Java support into Oracle8i expanded the horizon for Oracle developers giving them new powerful tool for developing applications, but Java is not a replacement for PL/SQL, it's a valuable addition to it.


Back to the top of page
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:

  1. Obtaining server certificate.
    You can create self-signed server certificate or submit server certificate for signing by a public certificate authority, like VeriSign, Thawte or others. Creating self-signed certificate is the easiest way to enable SSL. For details on creating/signing/installing server certificate, please refer to mod_ssl documentation at http://www.modssl.org/docs/2.8 and OpenSSL documentation at http://www.openssl.org/docs/
  2. Installing the certificate on the server.
    This is fairly easy - you just need to copy signed certificate to the location you specified with SSLCertificateFile directive.
  3. Restarting Apache with -DSSL define or using startssl parameter to enable SSL.

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 'DPSP' and mod_plsql handler is assigned to '/pls' location. All edits should be made to httpd.conf file or a file that is included into it. For changes to take effect, Apache server must be restarted.

There are several ways to restrict access to the DPSP Development Interface.

  1. Using LocationMatch directive and setting host-based security with Order, Allow and Deny directives:

    
     # default Order Deny,Allow is in effect here
     Deny from all
     Allow from 192.168.0.10
     Allow from 192.168.0.11
    
    

    The above example will deny access to development interface DAD for all IP addresses except 192.168.0.10 and 192.168.0.11.

    For more information on Order, Allow and Deny directives consult with mod_access documentation at http://httpd.apache.org/docs/mod/mod_access.html

  2. Using LocationMatch directive and setting user-based security with Auth* and Require directives:

    
     AuthType Basic
     AuthName "DPSP Development Interface"
     AuthUserFile /your/path/to/user/file
     AuthGroupFile /your/path/to/group/file
     Require group developers
    
    

    You will have to create a user file using htpasswd utility and add users to it. Then you will create a group file and include all needed users into developers group:

       developers: devuser1 devuser2 devuser3
    
    The above example will ask for password whenever the development interface DAD is accessed and will only allow members of developers group to access it.

    You may also combine methods 1) and 2) (for example, to allow access for remove developers when their IP addresses are not known or rapidly change):

    
     # allow access if either auth check passes (host-based will be checked
     # first, so internal developers will not be asked any password)
     Satisfy any
    
     # host-based auth - Deny, Allow is in effect
     Deny from all
     Allow from 192.168.0.10
     Allow from 192.168.0.11
    
     # password-pased auth
     AuthType Basic
     AuthName "DPSP Development Interface"
     AuthUserFile /your/path/to/user/file
     AuthGroupFile /your/path/to/group/file
     Require group developers
    
    

    The above example will try to satisfy any of the two requirements. IP address will be evaluated first and if it is one of allowed, evaluation will complete here allowing access to the DAD, else user name and password will be requested from the visitor.

  3. using mod_ssl for SSL certificate-based authentication:
    1. create your own CA (certificate authority) certificate and provide it to SSL engine via SSLCACertificateFile directive. You may use any commercially available Certificate Servers, like Microsoft or Netscape, or you may use OpenSSL engine (provided with mod_ssl) to create your own CA certificate.
    2. create certificates for developers and sign them with your CA private key, then distribute them to developers. Developers will need to install their certificates into browser and configure it to present this certificate to the site where development is done.
    3. use directive in conjunction with mod_ssl authentication directives to restrict access to the development interface to only those clients with valid certificates:
      # allow access to other zones with no verification of client cert
      SSLVerifyClient none
      # enforce mod_ssl reconfiguration based on accessed location
      
       # should verify client certificate validity against locally known CAs
       SSLVerifyClient require
       # allow only self-signed or your_ca-signed certificates
       SSLVerifyDepth       1
       # make your_ca.crt the only known CA certificate
       SSLCACertificateFile conf/ssl.crt/your_ca.crt
       # use fake basic auth and deny access if SSL is not used
       SSLOptions           +FakeBasicAuth +StrictRequire
       # enforce SSL connection
       SSLRequireSSL
       # check for certain fields in client certificate
       SSLRequire    %{SSL_CLIENT_S_DN_O}  eq "Your Company" and \
                     %{SSL_CLIENT_S_DN_OU} eq "DPSP Developers"
      
      

    The above example will allow access only for clients with client certificate which is signed by your CA and have O (Organization) set to "Your Company" and OU (Organizational Unit) set to "DPSP Developers".

    For more information, please refer to mod_ssl documentation at http://www.modssl.org/docs/2.8


Back to the top of page
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 (9.2.0.1):

  1. Create ORAPLS9.LIB from ORAPLS9.DLL.

    As of 9i Release 2 patch set 1 (9.2.0.2) and later, this step is no longer necessary, as ORAPLS9.LIB is supplied with this patchset. Those readers who have 9.2.0.2 or later patch set applied may skip this step.

    Amazingly, Oracle didn't supply this library effectively preventing the code from being linked successfully. However, you can create this library yourself. Two tools are needed for this: Borland IMPDEF (available with their free C++ Command Line Compiler) and Microsoft LIB (available as part of Visual C++). First, you create a .DEF file using IMPDEF orapls9.def orapls9.dll and then you create a library from it using LIB /def:orapls9.def /out:orapls9.lib. When done, copy the library file into your %ORACLE_HOME%\lib directory.
    You can download ready-made library here: orapls9lib.zip (note that this file is valid only for initial release of 9i R2 - version 9.2.0.1. 9.2.0.2 and later patch sets provide their own library file.)

  2. Replace the makefile provided by Oracle with the one that works.

    Yes, spnc_makefile.mk in %ORACLE_HOME%\plsql is tailored for UNIX and GNU make and does not work on Windows with Microsoft NMake as it is. There are a lot of modifications required to this file. I went through the burden of modifying and debugging it and you can download the resulting (verified working) makefile here: Makefile (right-click on this link and choose Save Target As...) Be sure to read through comments in this file for additional modifications necessary to tailor this makefile for your particular system and file locations.

  3. Install Cygwin rm and mv utilities.

    Makefile I provided uses rm and mv commands instead of conventional DEL and MOVE Windows commands, and for a reason: Windows embedded commands do not like UNIX-style filenames (like c:/oracle/ora92/plsql/...) - they expect backslash as separator character and treat slash character as an option specifier, and subsequently fail to perform the command. Cygwin team ported most UNIX utilities to Windows and we can take advantage of that. You can download the latest Cygwin library and utilities from their site at http://www.cygwin.com or you can download only rm.exe, mv.exe and required Cygwin library DLL here: fileutils.zip (included is cygwin1.dll version 1.3.22-1 and rm.exe and mv.exe from GNU fileutils 4.1.1), and copy the files from this ZIP to your %SystemRoot%\system32 folder.

  4. Ensure your PATH environment contains paths to necessary VC++ dirs.

    This is necessary so that cl and link can find needed DLLs. Basically you need to locate vcvars32.bat file in your Visual C++ BIN directory and cut PATH set command from it (actually, you don't need the whole command, you only need paths that this command file appends to the PATH variable). Then right-click on My Computer, Properties, Advanced tab, Environment Variables..., select System Variables and locate PATH variable there. If it's not there, create it clicking on the New button, otherwise double-click and edit it to include the paths you extracted from vcvars32.bat (and be sure to expand all variables manually). Unedited, extracted string should look like

    %MSDevDir%\BIN;%MSVCDir%\BIN;%VSCommonDir%\TOOLS\%VcOsDir%;%VSCommonDir%\TOOLS;

    and you should edit it and expand variables in it like this (paths and names are for VC++ 6.0, line is wrapped for convenience of the reader but it should actually be contiguous):

    C:\PROGRA~1\MICROS~1\Common\MSDev98\BIN;C:\PROGRA~1\MICROS~1\VC98\BIN;
    C:\PROGRA~1\MICROS~1\Common\TOOLS\WINNT;C:\PROGRA~1\MICROS~1\Common\TOOLS;

    using definitions in vcvars32.bat for these variables. Now just prepend or append the expanded string to the current PATH value and save it. You may need to reboot so that OS will re-read the environment and then provide it to Oracle and make utility that Oracle will spawn (remember that Oracle usually starts under LocalSystem account and this account only reads environment on OS startup).

  5. Ok, we're almost there - just need to configure Oracle to know where our files are.

    Several initialization parameters need to be set for Oracle to be able to compile PL/SQL natively. Assuming that C:\VCHOME is Visual C++ home directory, C:\ORACLE\ORA92 is Oracle home and you created a directory C:\ORACLE\ORA92\plsql\nlib to store your DLLs, here's which and how (you need to issue these commands AS SYSDBA):

    ALTER SYSTEM SET plsql_native_make_utility='C:\VCHOME\bin\nmake.exe' SCOPE=BOTH;
    ALTER SYSTEM SET plsql_native_make_file_name='C:\ORACLE\ORA92\plsql\Makefile' SCOPE=BOTH;
    ALTER SYSTEM SET plsql_native_library_dir='C:\ORACLE\ORA92\plsql\nlib' SCOPE=BOTH;

    Be sure not to use %ORACLE_HOME% in any of these - use full real path this environment variable translates into. Same's with %VCHomeDir%. Although these variables could save you a few keystrokes, Oracle does not recognize them as variables and does not expand them into real paths.

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:

ALTER SYSTEM SET plsql_native_make_utility='C:\VCHOME\bin\nmake.exe /NOLOGO';

Final note: make sure you protected the Makefile, nmake.exe and other executables it will call during the course of compiling your DLLs from being overwritten by a malicious user. Since Oracle runs as LocalSystem, all processes it spawns inherit the same privileges and thus are not limited in almost any way on what they can do to OS and files (LocalSystem is a very powerful account), which makes them an attractive target for hacker attacks. Ensure that nobody but Administrators can overwrite these files by explicitly revoking Write, Modify and Full Control permissions from all non-admins (especially from Everyone group) and granting these directly to Administrators group only. This way, only Administrators will be able to update the executables if need be and nobody else, including LocalSystem, will be able to trojan them while still be able to execute them.

Happy ncomping!

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.

Back to the top of page

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.
In a few words, the bug is in mod_plsql, which does not always pass GCI environment variable names in uppercase, as expected by the OWA package. The fix is to explicitly apply UPPER() function to all CGI environment variable names as they are initialized by the OWA package.
Additionally, the latest mod_plsql patch (Version 3.0.9.8.5) seems to have another bug, which cannot be easily fixed: this, and only this version does not pass the authentication information correctly to the PL/SQL programs it calls. All previous 3.0.9.8.x versions did not expose this behavior, as well as mod_plsql 9.0.x series shipped with Oracle9iAS Release 2. The only known way around this bug is to use mod_plsql v3.0.9.8.4 or 9.0.x until (if ever) Oracle fixes this obvious bug in 3.0.9.8.5.


Back to the top of page
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.
Look at our WebDAV demo.
Click here to view more...


Back to the top of page
How to avoid redirection from index.html to DPSP unit?

Use Apache mod_rewrite directive:

<IfModule mod_rewrite.c>
RewriteEngine on
RewriteCond %{HTTP_HOST} www.dynamicpsp.com [NC]
RewriteRule ^/$ /dpsp/prod/!go?id_=nhome [PT]
RewriteCond %{HTTP_HOST} www.dynamicpsp.com [NC]
RewriteRule ^/([\!]go)(.*)$ /dpsp/prod/!go$2 [PT]
RewriteLog "rewrite.log"
RewriteLogLevel 0

</IfModule>


Back to the top of page
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 MS Excel to CSV file into PL/SQL
* and upload data from .csv file to Oracle Database
* Copyright(c) 2000-2006 by HitMedia. All rights reserved.
* Written by Andrew A. Toropov
*

1. How to Install
>loadjava -v -u user/pass@[TNSNAME] -r jxl.jar
>sqlplus user/pass@[TNSNAME] @gocsv.sql

2. How to Run
>sqlplus user/pass@[TNSNAME]
sql>
set serverout on
declare
Path varchar2(1000) := '/temp/'; -- directory with perms
Fname varchar2(1000) := 'ExcelFile.xls';
Fine varchar2(1000);
begin
Fine := nn$gocsv.ConvertCSV(Path,Fname,';');
dbms_output.put_line(substr(Fine,1,255));
end;

/
File -/temp/ ExcelFile.xls.csv will be created
Warning! Grant Oracle Java permissions to USER.
> sqlplus "sys/[password]@[TNSNAME] as sysdba"
sql>
begin
dbms_java.grant_permission('USER',
'SYS:java.io.FilePermission',
'/temp/-',
'read,write,delete');
end;
CREATE OR REPLACE DIRECTORY temp AS '/temp/';
GRANT READ ON DIRECTORY temp TO user;
GRANT WRITE ON DIRECTORY temp TO user;
GRANT DELETE ON DIRECTORY temp TO user;
/

3. Now you can upload data from .csv file to an Oracle Database

>sqlplus user/pass@[TNSNAME]
sql>
CREATE TABLE ExcelFile
(p1 VARCHAR2(4000)
,p2 VARCHAR2(4000)
,p3 VARCHAR2(4000)
,p4 VARCHAR2(4000)
,p5 VARCHAR2(4000))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY
temp /**/
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(p1,p2,p3,p4,p5))
LOCATION ('
ExcelFile.xls.csv')
) PARALLEL 5 REJECT LIMIT UNLIMITED;


4. Now you can fetch data from Oracle external table created based on Excel file


>sqlplus user/pass@[TNSNAME]
sql>
select * from ExcelFile;


Back to the top of page
Java class to convert from DBF (DB2) to CSV file within Oracle PL/SQL

* nn$dbf2csv.dbf2csv
*
* Java Utility class for convert DBF file to CSV file
* and upload data from .csv file to Oracle Database
* Copyright(c) 2000-2007 by Andrew Toropov All rights reserved.
* Written by Andrew A. Toropov
*

1. How to Install
>loadjava -v -u user/pass@[TNSNAME] -r dbf2csv.jar
>sqlplus user/pass@[TNSNAME] @dbf2csv.sql

2. How to Run
>sqlplus user/pass@[TNSNAME]
sql>
set serverout on
declare
Path varchar2(1000) := '/temp/'; -- directory with perms
DFBFile varchar2(1000) := 'DFBFile.dbf';
CSVFile varchar2(1000) := 'CSVFile.csv';
Fine varchar2(1000);
begin
Fine :=
nn$dbf2csv.dbf2csv(Path||DFBFile, Path||CSVFile, ';');
dbms_output.put_line(substr(Fine,1,255));
end;
/

File -/temp/'CSVFile.csv will be created
Warning! Grant Oracle Java permissions to USER.
> sqlplus "sys/[password]@[TNSNAME] as sysdba"
sql>
begin
dbms_java.grant_permission('USER',
'SYS:java.io.FilePermission',
'/temp/-',
'read,write,delete');
end;
CREATE OR REPLACE DIRECTORY temp AS '/temp/';
GRANT READ ON DIRECTORY temp TO user;
GRANT WRITE ON DIRECTORY temp TO user;
GRANT DELETE ON DIRECTORY temp TO user;
/

3. Now you can upload data from .csv file to an Oracle Database

>sqlplus user/pass@[TNSNAME]
sql>
declare
Path varchar2(1000) := '/temp/'; -- directory with perms
DFBFile varchar2(1000) := 'DFBFile.dbf';
CSVFile varchar2(1000) := 'CSVFile.csv';
Fine varchar2(1000);
begin
Fine :=
nn$dbf2csv.dbf2csv(Path||DFBFile, Path||CSVFile, ';');
execute immediate Fine;
end;
4. Now you can fetch data from Oracle external table created based on Excel file

>sqlplus user/pass@[TNSNAME]
sql> select * from NNDBF$DFBFile;


Back to the top of page
email us
Copyright © 2000-2017 by HitMedia LLC. All Rights Reserved.