Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 11, 2001 - 3:45 am UTC

Last updated: September 08, 2008 - 4:29 pm UTC

Version: Oracle8i 8.1.6.0.0

Viewed 1000+ times

You Asked

Where is the utility named "loadpsp" found so that I can compile PSP files?

and Tom said...

It should be in $ORACLE_HOME/bin. It is only installed with a database (not client) install with 8.1.6 (fixed in 8.1.7).

If you have 8.1.6 on NT, you can get it from the installation CD via:

We just extracted the loadpsp.exe and placed it in the %ORACLE_HOME%\bin directory. For example, if c:\orant is ORACLE_HOME and d: is the CD drive, issue the
following 2 commands if you have the java developer kit:

cd c:\orant\bin
jar -xvf d:\stage\Components\oracle.rdbms\8.1.6.0.0\1\DataFiles\exec.0.0.jar loadpsp.exe

or use winzip to extract the file.


Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Would like to start using PSP

Yogeeraj, January 31, 2002 - 2:50 am UTC

Hi

could you please guide me to any good documentation which could get me started with it.

I am running Oracle 8.1.7 and IAS release 1.

thanks in advance

best regards
yogeeraj

Tom Kyte
January 31, 2002 - 7:16 am UTC

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/07_subs.htm#17196 <code>
...

Load PSP

Tarun Babu, January 29, 2004 - 12:39 am UTC

Hi tom,
Just go to this link and view source.
</code> http://www.oracle-base.com/articles/9i/dept_insert_req.psp <code>

I am not able to understand how it is running (.psp) directly in the browser. Is it possile to run a psp without loading into the database? If so how? Can we place the .psp file in the middle tier directly to run?
I know we have to use loadpsp utility to load the .psp file which create a stored procedure in db.
Can you please throw so lignt..

Tom Kyte
January 29, 2004 - 8:03 am UTC

i don't know what you mean by "running .psp directly in the browser"

I click on a link.
browser does http to the server
server was asked to retrieve /articles/9i/dept_insert_req.psp
server did that and returned html to the browser

how is that the browser running a psp?

Load PSP

Tarun, January 29, 2004 - 8:21 am UTC

Tom,
What I mean is "Accessing the the psp with .psp extension in the browser" like a .jsp file or .php or .asp etc. I know the browser can not execute the psp.

Can we execute a .psp file without loading it into db? or by placing it in the middle tier?
Your help appreciated..




Tom Kyte
January 29, 2004 - 8:27 am UTC

dept_insert_req.psp
package_name.procedure_name


would be one way to achieve this "magic"


running the stored procedure, retrieving the data and writing it using utl-file to a file named dept_insert_req.psp would be another.




Load PSP

Tarun, January 29, 2004 - 11:36 pm UTC

Thanks a lot. Tom.

Missed the point

Tim Hall, January 13, 2005 - 9:12 am UTC

Hi Tarun.

I think either you've missed the point of what I was trying to do, or I've missed the point of your original question.

The .psp files in my article are source files which you must download and load into the database. Because they contain fragments of HTML when viewed in a browser they may look like they are being run by the server, but they are not. I can guarantee this has I am not running my site on an Oracle http server ;-)

Cheers

Tim...

loadpsp limitation in 10g?

ht, September 07, 2005 - 7:02 pm UTC

Tom,
I'm trying to compile a psp that contains simple html. This works in 9.2.0.6 but not in 10.1.0.3 or 10.1.0.4 (I patched to 10.1.0.4 because of a Metalink "hint" but the problem is still occuring.

It seems to be related to a 32k file size limit because removing lines to get a successful compile seems to correspond to a filesize of 32k.

Below is the error:
$ loadpsp -replace -user scott/tiger string_error.psp
ORA-20006: "string_error.psp": compilation failed with the following errors.
"string_error.psp", line 3, pos 46: PLS-00172: string literal too long
$ ls -ltr string_error.psp
-rw-r--r-- 1 dba 32996 Sep 7 15:35 string_error.psp

Below is a successful compilation (after removing lines in the *.psp):
$ loadpsp -replace -user scott/tiger string_error.psp
"string_error.psp": procedure "string_error" created.
$ ls -ltr string_error.psp
-rw-r--r-- 1 dba 32486 Sep 7 15:35 string_error.psp
$

Below is my psp file:

<%@ page language="PL/SQL" %>

<%@ plsql procedure="string_error" %>

<html>
<body >

<head>
<title>
xyz
</title>
</head>
<center>
<p align="center">
<br>
<center>
<table>
<td > xyz
</table>

<table>
<tr> <td>This works in 9.2.0.6 but not in 10.1.0.4
...
<tr> <td>This works in 9.2.0.6 but not in 10.1.0.4
</table>

<table>
End
</table>

</body>
</html>

Do you know of how I can workaround this 10g limitation? Or maybe a workaround to my 9i code that could have been faulty but not an issue until 10g?

Thank you.


Tom Kyte
September 08, 2005 - 7:56 am UTC

that is definitely a bug - in 9i, they broke your literal text up into multiple htp.prn() calls -- i reproduced your issue (and have a workaround for you), but please - have support file this as a bug.

I took your template above and created a 50k file with:

<%@ page language="PL/SQL" %>

<%@ plsql procedure="string_error" %>

<html>
<body >

<head>
<title>
xyz
</title>
</head>
<center>
<p align="center">
<br>
<center>
<table>
<td > xyz
</table>

<table>
<tr> <td>This works in 9.2.0.6 but not in 10.1.0.4</td>
.... repeated 1000 times
<tr> <td>This works in 9.2.0.6 but not in 10.1.0.4</td>
</table>

<table>
End
</table>

</body>
</html>



Then, in 9i:

[tkyte@desktop tkyte]$ loadpsp -replace -user scott/tiger string_error.psp
"string_error.psp": procedure "string_error" created.


but in 10g:

[tkyte@desktop tkyte]$ loadpsp -replace -user scott/tiger string_error.psp
ORA-20006: "string_error.psp": compilation failed with the following errors.
"string_error.psp", line 3, pos 46: PLS-00172: string literal too long



so I edited the file and about halfway through stuck:

<%-- Comment text --%>


just to break up the string - and it worked -- psp sees the comment and "stops", htp.prn()'s whatever it had and then starts again...


[tkyte@desktop tkyte]$ !load
loadpsp -replace -user scott/tiger string_error.psp
"string_error.psp": procedure "string_error" created.





Filed a bug.

ht, September 08, 2005 - 7:06 pm UTC


Problems wrapping psps

ht, October 20, 2005 - 8:00 pm UTC

Tom,
I have been able to reverse-engineer my psps, wrap them, and compile.

However, when the psp to be wrapped passes a parameter like the one below, the wrapped file compiles but I get the "ORA-00911: invalid character" error.

<%@ page language="PL/SQL" %>

<%@ plsql procedure="test4a" %>

<%!
l_where varchar2(2000);
%>

<html>

<frameset>

<body >

<%
l_where:='where default_tablespace is not null';
%>

<frame name="f22" src="test4b?in_where=<%=l_where%>&in_orderby=username desc" resize
scrolling="yes">



</body>

</frameset>

</html>


<%@ page language="PL/SQL" %>

<%@ plsql procedure="test4b" %>

<%@ plsql parameter="in_where" type="varchar2" %>

<%@ plsql parameter="in_orderby" type="varchar2" %>

<%!

type rc is ref cursor;
l_cursor rc;
l_rec dba_users%rowtype;

l_sql varchar2(2000);
l_lifecycle varchar2(2000);
l_title varchar2(2000);

%>

<html>
<head>
<title>
</title>
</head>
<body >


    <%
    begin
    %>

    <center>
    <table>
    </table>

    <table>
    <tr>


    <%
    if in_where='null' then
    l_sql:='select * from dba_users order by '||in_orderby;
    else
    l_sql:='select * from dba_users '||in_where||' order by '||in_orderby;

    end if;

    open l_cursor for l_sql;
    loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    %>
    <tr>
    <td><%=l_rec.username%>
    <td><%=l_rec.default_tablespace%>
    <td><%=l_rec.temporary_tablespace%>
    <%

    end loop;
    close l_cursor;

    end;

    %>
    </table>



</body>
</html>

Is this a bug and can you think of a workaround? I'm on 10.2.

TIA,
ht

Tom Kyte
October 21, 2005 - 8:03 am UTC

I've a feeling whatever inputs you pass to this are causing the error, why don't you just PRINT OUT the sql statement instead of trying to execute it (eg: debug the code by seeing what dynamic code you are actually generating)

Code looks fine and it runs when not wrapped.

ht, October 21, 2005 - 8:58 am UTC

Hi Tom,
Thank you for your assistance with this.

I displayed the dynamic sql, ran it in a sqlplus session, and results were returned fine. The issue is that the PSP fails after I reverse-engineer the PSP and wrap it.

Is there a "set" command I can use to run the PSP in a SQL*Plus session so it displays results one would see in a web browser?

set serveroutput on

exec test4a

PL/SQL procedure successfully completed.


Tom Kyte
October 21, 2005 - 10:16 am UTC

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'WEB_AUTHENT_PREFIX';
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );
end;
/
set serveroutput on
exec test4a
exec owa_util.showpage


Thank you

ht, October 21, 2005 - 3:19 pm UTC

Tom,
As usual, you pointed me in the right direction. The routine to display the PSP in SQL*Plus pointed me towards the "&" being escaped. While the PSP works fine when compiling, wrapping it causes an issue with the escaped character. After removing the "\", the PSP works fine wrapped or not.
ht

OK

Raju, November 24, 2005 - 7:28 am UTC

Hi Tom,
Can I be able to use SQLLDR utility in PL/SQL procedure??

Tom Kyte
November 24, 2005 - 8:56 am UTC

what this has to do with PLSQL server pages - well, it is way beyond me.

in plsql it would be called "external tables"

use external tables and you have sqlldr from SQL.

else - no, not without running an external program and that just isn't right.

reverse-engineer

Chris, September 08, 2008 - 3:08 pm UTC

Just wondering how "ht from california" reverse-engineered the psp. I have lost the .psp pages and only have the oracle procedures left. Can I recreate the .psp pages?
Tom Kyte
September 08, 2008 - 4:29 pm UTC

not that I am aware of, the procedures just have big "htp.p" statements in them where the static HTML once was
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.