Unit 12.3. Loading PSPs into the Database
Loading a PSP
The loadpsp utility is a standard utility provided with Oracle versions 8.1.6 and above. To load a PSP file into the database, you use the loadpsp utility from an operating system prompt. It reads in the PSP and translates it into a stored procedure using the attributes provided in the utility directive. Each PSP file corresponds to only one stored procedure in the database. PSP pages are compiled and loaded into the database in one step. An example of the syntax follows:
loadpsp -user username/password@service_name file.psp
And an example with values is:
loadpsp -user web_book/author@web_book_service hello_world.psp
In this example, the hello_world.psp file is loaded into the web_book schema of the database. The loadpsp keyword is necessary to invoke the loadpsp utility. The -user portion of this command denotes the user option to the loadpsp utility, and is always followed by the username and password (and TNSNames service name if you are connecting to a remote database). Hello_world.psp is the name of your PSP file. Additionally, you can also include a path for your PSP file appended to the front of the PSP file name, for example,
c:guestpsp_folderhello_world.psp
 | You can load more than one PSP at a time. Keep in mind that PSP files can have no other extension than .psp, and the .psp extension must be included in the filename in the loadpsp command. |
Replacing a PSP
An expanded example of loading a PSP that has already been loaded into the database, but now includes an error page, follows:
loadpsp -replace -user username/password@service_name error_page.psp file.psp
And another example with values is:
loadpsp -replace -user web_book/author@web_book_service hello_world_error.psp hello_world.psp
 | You should always use the -replace option. Running the loadpsp utility with the -replace option does not return an error if the procedure does not exist. |
Maintaining PSP Files
PSP files can be loaded into the database using the loadpsp utility, but it is a one-way trip. PSP files cannot be retrieved from the database once they have been loaded.
The PSP is loaded to the database as a PL/SQL procedure. As noted elsewhere in this chapter, querying the USER_SOURCE data dictionary view for the procedure will reveal that the PSP has been converted into a PL/SQL procedure that uses the PL/SQL Web Toolkit. So if you have lost the original PSP file that generated this procedure, you can still change and recompile the procedure using Web Toolkit syntax. However, the PSP cannot be reconstructed for you in its original state.
It is important to store and maintain PSP files in a central location. Decide on a storage mechanism before building your PSPs.
Creating and Loading Your First PSP
Copy the following code example into a text editor and save it as “hello_world.psp”:
<%@ page language="PL/SQL"%> <%@ plsql procedure="hello_world" %> <% ---------------------------------------------------- -- FILENAME: hello_world.psp -- FILEDATE: 02.11.2002 -- CREATED BY: <Reader, Place Your Name Here> -- DESCRIPTION: Simple Page with Message, "Hello World" -- MODIFIED: -------------------------------------------------------- %> <HTML> <HEAD> <TITLE>Hello World</TITLE> </HEAD> <BODY BGCOLOR="#99CCCC"> <CENTER> <H1><FONT COLOR="white"><B>HELLO WORLD!</B></FONT></H1> <H2><FONT COLOR="white"><B>This is my first PSP!</B></FONT></H2> </CENTER> </BODY> </HTML>
Now open a DOS window and navigate to the directory where your hello_world.psp file is saved. Load your PSP into the Oracle database using the loadpsp utility commands illustrated earlier in this unit. Substitute your username, password, and service name for the username, password, and service name values. After successfully loading this PSP, you should receive the following feedback message:
"hello_world.psp": procedure "hello_world" created.
Ensure that you are able to successfully view it in your browser. When you enter the URL, be sure to include the name of the server that serves up pages through the 9iAS, followed by /pls/, /<the_name_of_your_DAD>/ (which you should have specified in Chapter 2, “Oracle 9iAS”), followed by “hello_world.” The /pls/ and /<thename_of_your_DAD>/ elements will be outlined more fully in Unit 12.1, “Developing PSPs.” The syntax you use should be similar to the following:
http://my_server/pls/my_dad/hello_world
Note carefully, you do not specify the PSP extension after the PSP name. Since this procedure is now inside of the Oracle database, the .psp extension is not needed—it is not part of the procedure name inside the database. Keep in mind that the PSP filename and the procedure name invoked in the browser are two different things. For example, you could call your PSP file hello_world.psp, but call the procedure goodbye_world. Your page should look similar to that shown in Figure 12.1.
Figure 12.1. The Hello World PSP page displayed in a Web browser.
 | If you run the loadpsp utility on a file more than once, you MUST subsequently include the -replace option (you can liken this to creating or replacing a stored procedure, because, for all practical purposes, that’s exactly what you’re doing). |
Also note that if you include any error pages or include files, the names of the error pages and/or include files must precede the name of your procedure file. Otherwise, the load will fail.
 | Note the common and dreaded error: |
ORA-01031 insufficient privileges
Though the error message does not explicitly say so, the problem is that upon attempting to load this PSP, the loadpsp utility discovers that the extension, .psp (or any other extension), has been included as part of the PSP name in the procedure directive. For example:
<%@ plsql procedure="student_personal_info.psp" %> If you take out the extension and reload your PSP, you should no longer receive this error message.