mjackson110
asked on
Oracle 8i on the web
I am just getting to know Oracle and so far this is what i have done:
1)I have installed Oracle 8i enterprise version on windows 2000 server.
2)I created a database during installation.
3)I tried to create a web page and connect to this database using the Web Publishing Assistant - But the web page was not generated.I think it is due to the fact that the created database does not have any contents to run a query on.
4)I used the Database configuration assistant and created a new database but the same results again.....so i dint get anywhere.
5)I created a new table using SQL and populated it but then i have no idea how to connect it to the web page.
So my question is how do i populate a database and then how do i connect this database on the web, So as to be able to run a query or modify it from a client browser.
All suggestion's or comments would be really appreciated.
I am reffering to The Complete Reference - ORACLE 8i.
Thanks.
1)I have installed Oracle 8i enterprise version on windows 2000 server.
2)I created a database during installation.
3)I tried to create a web page and connect to this database using the Web Publishing Assistant - But the web page was not generated.I think it is due to the fact that the created database does not have any contents to run a query on.
4)I used the Database configuration assistant and created a new database but the same results again.....so i dint get anywhere.
5)I created a new table using SQL and populated it but then i have no idea how to connect it to the web page.
So my question is how do i populate a database and then how do i connect this database on the web, So as to be able to run a query or modify it from a client browser.
All suggestion's or comments would be really appreciated.
I am reffering to The Complete Reference - ORACLE 8i.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Qa. Is the OracleHTTPService on Win2000 running -
Could u ellaborate on this , I followed the instructions that u had mentioned but the $ORACLE_HOME folder itself is missing.Other than 8.1.5 i dont have anything else installed.
I need a web server can i use IIS or do i need to get something else like 9ioas ,apache etc..
Qb. Which "Web Publishing Assistant" are you referring to-
The web publishing assistant is
START>PROGRAMS>ORACLE-ORAH
This wizard states that it creates dynamic web pages by stating the database<name> that it has to link to.
Qc. what type of Web Page are you creating w/ Oracle, you have a myriad of choices: PL/SQL Pages(PSP),
JSP, JAVA stored procedures...
I am working on PSP right now but what would your suggestions on developer 2000 be,because forms running queries would be the main issue that i am working on.
Could u ellaborate on this , I followed the instructions that u had mentioned but the $ORACLE_HOME folder itself is missing.Other than 8.1.5 i dont have anything else installed.
I need a web server can i use IIS or do i need to get something else like 9ioas ,apache etc..
Qb. Which "Web Publishing Assistant" are you referring to-
The web publishing assistant is
START>PROGRAMS>ORACLE-ORAH
This wizard states that it creates dynamic web pages by stating the database<name> that it has to link to.
Qc. what type of Web Page are you creating w/ Oracle, you have a myriad of choices: PL/SQL Pages(PSP),
JSP, JAVA stored procedures...
I am working on PSP right now but what would your suggestions on developer 2000 be,because forms running queries would be the main issue that i am working on.
Qa. Is the OracleHTTPService on Win2000 running -
Could u ellaborate on this , I followed the instructions that u had mentioned but the $ORACLE_HOME folder
itself is missing.Other than 8.1.5 i dont have anything else installed.
I need a web server can i use IIS or do i need to get something else like 9ioas ,apache etc..
*** looks like your Oracle installation does not include the Oracle Apache Service (i.e. OracleHTTPService), which include modules for PL/SQL, Servlet Engine, and JServer module.
you can either install the 9ias OracleHTTPService (i.e. intended to be a middle-tier application server on an N-tier configuraion, WEB cache, Data cache, scalable, etc.) OR upgrade your existing Oracle8.1.5 installation. w/o know your overall strategy, i recommend keeping it simple at this stage and upgrading your 8.1.5 with the OracleHTTPService:
Also, load your Oracle Server CD, and validate whether you need to install JVM software from the CD as well.
*** Consider upgrading your Oracle Server to 8.1.7, it may be easier for you at this point, and provide JServer accelerator and other bug fixes.
Doc ID: Note:132936.1
Subject: What scripts are needed to manually install Oracle8i JVM (8.1.5 - 8.1.7)
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 19-JAN-2001
Last Revision Date: 19-APR-2001
PURPOSE
-------
This note describes what sql scripts are needed to fully install
all components of Oracle8i JVM, in version 8.1.5, 8.1.6 and 8.1.7.
Scripts must be executed as SYS or INTERNAL.
The 'initjvm.sql' script is responsible for the installation of the JVM, the subsequent
scripts mentioned will install the added functionality described by
the corresponding comments (prefixed by the "--" characters).
SCOPE & APPLICATION
-------------------
Manual istallation of Oracle8i JVM
--------------------------
Folow the checklist guide for a manual install of the Oracle8i JVM as described in
[NOTE:105472.1]Setup, Configuration, and Use of the Java Virtual Machine (JVM)
[NOTE:103855.1] Jserver Installation for Oracle8i
Note: ? is sqlplus substitution fo $ORACLE_HOME or %ORACLE_HOME%
and it can be used on both Unix and Windows platforms.
Scripts needed for JVM version 8.1.5
--------------------------
Only script needed for 8.1.5 version is initjvm.sql
-- Setup a database for running Java and the ORB
SQL>spool initjvm.log
SQL>@?/javavm/install/init
SQL>spool off
Scripts needed for JVM version 8.1.6
--------------------------
In 8.1.6 additional scripts are needed:
-- Setup a database for running Java and the ORB
SQL>spool initjvm.log
SQL>@?/javavm/install/init
SQL>spool off
-- Initializes Java library needed by PL/SQL
SQL>spool initplsj.log
SQL>@?/rdbms/admin/initpls
SQL>spool off
-- Load AQ/JMS jar files into the database
SQL>spool initjms.log
SQL>@?/rdbms/admin/initaqj
SQL>spool off
-- Load RepAPI server classes and publish 'repapi' obj
SQL>spool initrepapi.log
SQL>@?/rdbms/admin/initrep
SQL>spool off
Scripts needed for JVM version 8.1.7
--------------------------
In 8.1.7 many new Java API's and components are added.
So, we need to run even more scripts :
-- Setup a database for running Java and the ORB
SQL>spool jvminst.log
SQL>@?/javavm/install/init
SQL>spool off
-- Loads xml components into the JServer
SQL>spool initxml.log
SQL>@?/oracore/admin/initx
SQL>spool off
-- loads the XMLSQL Utility (XSU) into the database
SQL>spool catxsu.log
SQL>@?/rdbms/admin/catxsu.
SQL>spool off
-- JIS (OSE) installation
SQL>spool init_jis.log
SQL>@?/javavm/install/init
SQL>spool off
-- Turn on JAccelerator (ncomp) for JIS
SQL>spool jisja.log
SQL>@?/javavm/install/jisj
SQL>spool off
-- Adds the set of default end points to the server
-- with hardcoded values for the admin service
SQL>spool jisaephc.log
SQL>@?/javavm/install/jisa
SQL>spool off
-- Load PLSQL Gateway Servlet jar files into the database
SQL>spool initplgs.log
SQL>@?/rdbms/admin/initplg
SQL>spool off
-- Install Oracle JSP
SQL>spool initjsp.log
SQL>@?/jsp/install/initjsp
SQL>spool off
-- Turn on JAccelerator for JSP libs
SQL>spool jspja.log
SQL>@?/jsp/install/jspja.s
SQL>spool off
-- Initializes Java library needed by PL/SQL
SQL>spool initplsj.log
SQL>@?/rdbms/admin/initpls
SQL>spool off
-- Load AQ/JMS jar files into the database
SQL>spool initjms.log
SQL>@?/rdbms/admin/initjms
SQL>spool off
-- Load RepAPI server classes and publish 'repapi' obj
SQL>spool initrepapi.log
SQL>@?/rdbms/admin/initrep
SQL>spool off
-- loads sql, objects, extensibility and xml related java
SQL>spool initsoxx.log
SQL>@?/rdbms/admin/initsox
SQL>spool off
-- Configure OSE defauls admin Web Service
SQL>spool jisaep.log
SQL>@?/javavm/install/jisa
SQL>spool off
SQL>
RELATED DOCUMENTS
-----------------
Note.105472.1 Setup, Configuration, and Use of the Java Virtual Machine (JVM)
Note.103855.1 Jserver Installation for Oracle8i
*** make sure all objects are valid:
select object_name from all_objects
where status='INVALID'
Qb. Which "Web Publishing Assistant" are you referring to-
The web publishing assistant is
START>PROGRAMS>ORACLE-ORAH
This wizard states that it creates dynamic web pages by stating the database<name> that it has to link
to.
*** i see, i'll look into this, no comment yet.
Qc. what type of Web Page are you creating w/ Oracle, you have a myriad of choices: PL/SQL Pages(PSP),
JSP, JAVA stored procedures...
*** great.
I am working on PSP right now but what would your suggestions on developer 2000 be,because forms running
queries would be the main issue that i am working on.
*** if you plan on using Oracle Forms, than consider the Forms 6i, internet deployable forms. the old client-server forms are not suitable for the new 8i Internet Architecture.
**** if you are creating your own forms with JSPs or PSPs than even better, i understand the Oracle 6i forms can be slow.
sounds like you have "interesting problems" in this regard you will learn much and do well.
see ya,
Eric
below is another doc that may interest you:
Doc ID: Note:105472.1
Subject: Setup, Configuration, and Use of the Java Virtual Machine (JVM)
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 14-APR-2000
Last Revision Date: 17-APR-2001
Overview
--------
This article contains an introduction to the Java Virtual Machine (JVM).
It is written for the beginning to moderate level DBA.
After reading this article, you should be able to do the following:
- install the JVM
- understand the JAVA POOL (init.ora parameter JAVA_POOL_SIZE).
- create a Java stored procedure
- explain what the initjvm, jvmu815, and the rmjvm.sql scripts do
- use the LOADJAVA utility
- describe the security options available for Java
- know/understand what objects are created by the LOADJAVA utility
Introduction
------------
The Java Virtual Machine is a scalable, general-purpose Java execution
environment that is compliant with the SUN Java JDK specification and has been
optimized to leverage the Oracle architecture.
The following is Oracle JDK version compliance:
Oracle 8.1.5 is compliant with JDK 1.1
Oracle 8.1.6 is compliant with JDK 1.2
Java is complimentary to PL/SQL and fulfills other application needs. SQL
and PL/SQL can be called from Java programs through an embedded JDBC driver
in the database. In turn, Java procedures can be called from SQL and PL/SQL
once they have been published as a PL/SQL program.
Java Scripts
------------
*** All scripts are located in the $OH/javavm/install directory ***
initjvm.sql
===========
This script is the heart of the JVM installation process. It loads the
initial set of Java classes necessary to support Java, initializes the SQL
tables for the CORBA namespace, and publishes some top-level entry points
through call specifications.
rmjvm.sql
=========
The rmjvm.sql removes any trace of Java usage. This includes core classes,
user created classes, and all the Java objects from the data dictionary. It
should only be run if you wish to remove all Java objects from a database.
Once this script is run, all of your Java objects will be gone, unless you
have a backup.
jvmu815.sql
===========
This script should be run when upgrading a database from 8.1.5 to 8.1.6 and
Java was used in the 8.1.5 database. It preserves user classes while replacing
system classes, whereas if you ran rmjvm.sql followed by initjvm.sql, it
removes user classes and replaces system classes.
JAVA Installation
-----------------
The Java Virtual Machine can be installed using one of the following two
methods:
- An Oracle8i typical or minimal install installs JVM automatically.
- An Oracle8i custom install installs JVM, when the JVM option is selected.
The Java Virtual Machine can be activated in a new or existing database by doing one
of the following:
- Create a database with the database assistant and choose to activate java (when
selecting to activate java for the new database, it will take longer for the database
to be created because it will run the initjvm.sql script automatically)
- You can manually run the initjvm.sql script as internal or sys (the
script can take over an hour to run).
The initjvm.sql script does the following to install the JVM:
? Creates and loads the Java classes into the SYS schema using the CREATE
or REPLACE JAVA SYSTEM command.
? Creates public synonyms for the loaded classes to be accessible to all
users.
? Creates roles that can be used with Java.
? Defines database startup and shutdown triggers.
? Configures the JVM for CORBA and EJBs.
? Creates the DBMS_JAVA package by calling the initdbj.sql script.
? Installs SQLJ by calling the initsqlj.sql script.
SYSTEM Requirements (These numbers are recommendations):
? SHARED_POOL_SIZE >= 65 MB
? JAVA_POOL_SIZE >= 50 MB
? 50 MB free in the SYSTEM tablespace
? 250 MB of rollback segment space
Verifying Installation
----------------------
The banner in svrmgrl/sqlplus shows that Java Options have been installed.
The following query checks that the number of Java objects created is greater
than zero:
SELECT count(*)
FROM dba_objects
WHERE object_type LIKE '%JAVA%';
If the above query returns a number < 4000, the installation was not
successful.
The query below checks for any invalid Java classes using a Java stored
procedure:
SELECT dbms_java.longname(name)
FROM sys.obj$
WHERE type# = 29 and status != 1;
If the above query fails or returns any rows, then something is wrong with
the JVM installation.
Sizing and Configuring the JAVA_POOL_SIZE
--------------------------
The shared parts of each Java class are stored in the Java pool. This includes
read-only memory, such as methods. The init.ora parameter JAVA_POOL_SIZE
controls the size of the Java pool which defaults to 20 MB.
Java pool is consumed primarily during class loading, although compilation and
resolution also consume Java pool memory. The more Java classes and code that
is actually in use in your database instance, the more Java pool is required.
There is no incremental per-session cost for the Java pool. The per class
memory requirement can average about 4-8 KB for each class. On a dedicated
server only using Java stored procedures, it is possible that the
JAVA_POOL_SIZE could be as low as 10 MB.
None of the per session Java states are stored in the Java pool. For dedicated
servers, it is stored in the UGA within the PGA. Under Multi-Threaded Server
(MTS), which is required for CORBA and EJBs, the Java pool could be very large.
CORBA and EJBs require more memory, large Java-intensive applications could
require up to one GB of Java pool memory.
In MTS servers, some of the UGA used for per session Java states are stored in
the Java pool. Since the Java pool is fixed in size, you must estimate the
total requirement for your applications and multiply by the number of
concurrent sessions they want to create. All UGAs must be able to fit in the
Java pool.
As a general guideline, the JAVA_POOL_SIZE should be set to 50 MB or higher for
large applications. The default of 20 MB should be adequate for typical Java
stored procedure usage.
To determine how much Java pool memory is being used, query V$SGASTAT:
SELECT *
FROM V$SGASTAT
WHERE pool = 'java pool';
Add free memory to memory in use to determine the size of the Java pool.
Common Errors Installing/Using JVM
--------------------------
ORA-3113 : end-of-file on communication channel running initjvm.sql
-- An ORA-3113 can occur when there is a lack of resources. Check the
following settings:
SYSTEM Requirements (These numbers are recommendations)
? SHARED_POOL_SIZE >= 65 MB
? JAVA_POOL_SIZE >= 50 MB
? 50 MB free space in the SYSTEM tablespace
? 250 MB of rollback segment space
Note: This error is documented in [BUG:1160778], entitled "ORA-3113 ON
CREATE OR REPLACE JAVA SYSTEM; IN INITJVM.SQL".
ORA-4030 : out of process memory when trying to allocate %s bytes (%s,%s)
running initjvm.sql
-- This problem can be solved by setting all ulimit parameters to UNLIMITED
by typing: unlimit.
Note: This problem is documented in [NOTE:101019.1], entitled
"ORA-04030: running initjvm.sql script".
ORA-4031 : unable to allocate bytes of shared memory during loadjava or
deployejb
-- To resolve this problem, shutdown your database and reset the
SHARED_POOL_SIZE or JAVA_POOL_SIZE parameter to a larger value.
The mention of 'shared pool' in the error message can be misleading
because the error indicates you are running out of memory in the
'Shared Global Area'. It does not always mean you should increase your
SHARED_POOL_SIZE, you may need to increase your JAVA_POOL_SIZE instead.
In this case, you may need to increase both parameters to resolve
the problem.
Creating Java Stored Procedures
--------------------------
To create a Java Stored Procedure, perform the following steps:
1. Write the Java program.
2. Load the program into the database.
3. Publish the Java program to SQL (PL/SQL Wrapper).
4. Call the Java program from SQL or PL/SQL.
Step 1: Write the Java program.
======
EchoInput.java.
public class EchoInput {
public static void main (String[] args){
for (int i=0; i<args.length;i++)
System.out.println(args[i]
Step 2: Load the program into the database.
======
Issue the following command from the UNIX prompt:
% loadjava -u system/manager -v -r EchoInput.java
Or manually from SQL*Plus:
CREATE OR REPLACE JAVA SOURCE NAMED "EchoInput" AS
public class EchoInput { public static void main (String[] args) {
for (int i=0; i<args.length;i++) System.out.println(args[i]
Step 3: Publish the Java stored procedure
======
To call a Java procedure from SQL or PL/SQL, you must publish the
procedure to SQL. This is also referred to as creating a PL/SQL Wrapper.
Once the procedure is published/wrapped, it can be called from any PL/SQL
routine or SQL statement.
For example:
create or replace procedure echo_input (
s1 varchar2, s2 varchar2, s3 varchar2)
as language Java
name 'EchoInput.main(java.lang.
Step 4: Call the Java program from SQL or PL/SQL.
======
call dbms_java.set_output(5000)
call echo_input('It', 'works', 'now!');
Note: Please note that to run this in SQL*Plus, you need to
specify 'set serveroutput on'
LOADJAVA/DROPJAVA Utility
-------------------------
The LOADJAVA Utility assists in the loading of Java classes/source into the
database. The utility is invoked from the Operating System prompt, and behaves
much like the Export/Import Utility.
When the utility is invoked, it connects to the database and attempts to load
the Java files into the schema of the user it connects to as, or into the
schema of the user who is specified in the -schema parameter.
Based on this, the schema that the objects are loaded into must meet the
following three criteria:
- The user must exist.
- The user must have sufficient quota in the default tablespace.
- There must be enough free space in the tablespace.
Common options for the LOADJAVA utility include:
- user : The user that you are logging into the database as
- schema : The schema you want the objects loaded into if it is different
from the -user parameter. If this is not specified, the objects
are loaded into the logon schema.
- verbose : Enables the verbose mode in which progress messages are
displayed
- resolve : After all the objects are loaded and compiled, resolves all
external references in those classes. If this option is not
specified, files are loaded but not compiled or resolved until
runtime.
- oci8 : Directs loadjava to communicate with the database using the OCI
JDBC Driver (default driver).
- thin : Directs loadjava to communicate with the database using the thin
JDBC Driver.
When the LOADJAVA Utility is used, six objects (below) are created in the
users' schema if they do not already exist. These objects assist in the
loading of the Java objects into the database.
1. JAVA$CLASS$MD5$TABLE
- This is a hash table that tracks the loading of Java objects into a schema.
** If you use the loadjava utility to load the objects, you must use the dropjava
utility to drop the objects otherwise this table is not update.
2. CREATE$JAVA$LOB$TABLE
- This table contains the name of the Java object, the date it was loaded,
and has a BLOB column to store the Java object.
3. LOADLOBS (package and package body)
- This package is used to load the Java object into the BLOB column in the
CREATE$JAVA$LOB$TABLE.
4. Three indexes:
- one on the name column of the CREATE$JAVA$LOB$TABLE
- one on the LOB column of the CREATE$JAVA$LOB$TABLE
- one on the name column of the JAVA$CLASS$MD5$TABLE
The DROPJAVA Utility converts the filenames into the names of schema objects,
drops the objects, and then deletes their corresponding row in the
JAVA$CLASS$MD5$TABLE.
The options available are:
- oci8
- schema
- thin
- verbose
The behavior for the above options is the same as in LOADJAVA.
Java Security
-------------
In 8.1.5, security is provided only through the use of database roles. In
8.1.6, JServer security is based on Java 2 security which allows developers to
assign privileges on a class by class basis.
The Java related roles in 8.1.5 still exist in 8.1.6 for backward
compatibility, however, Oracle recommends that you specify each permission
explicitly, rather than use the roles.
In 8.1.5 the initjvm.sql script creates two roles named JAVAUSERPRIV and
JAVASYSPRIV.
JAVAUSERPRIV privileges:
- open a TCP/IP socket
- read or write from a file using UTL_FILE_DIR
JAVASYSPRIV privileges:
- all the privileges associated with JAVAUSERPRIV
- create a subprocess
- listen on a specific network port
- set the socket factory
- set the stream handler
- read or write from any file
In Java 2 security, you create security policies or permissions for each
specific class. Within Oracle8i, you specify the policy through the DBMS_JAVA
package, and the security policy is stored within the PolicyTable.
The PolicyTable is a new table within the database that exclusively manages
Java 2 security permissions. You can grant or revoke permissions dynamically
by using PL/SQL through DBMS_JAVA procedures or Java through the class:
oracle.aurora.rdbms.securi
In summary, the following two methods may be used to manage privileges:
Fine Grain Definition
- You grant each permission individually for specific users or roles. If
you do not grant a permission for access, the schema is denied.
General Permission Definition
- An alternative to the Fine Grain method is that you can make use of the
two roles supplied by Oracle: JAVAUSERPRIV and JAVASYSPRIV.
The following two views have been added in 8.1.6 to view the policy table:
DBA_JAVA_POLICY - can see all rows within the policy table
USER_JAVA_POLICY - can only see permissions relevant to the current user
Both views contain information about granted and restricted permissions.
Note: For more information on the security in Java, refer to the "Oracle
8.1.6 Java Developers Guide".
JVM Tuning Considerations
-------------------------
Which is faster - Java or PL/SQL? Performance numbers are not yet available.
In general, PL/SQL is best for data intensive programs and Java is best for
compute intensive coding.
PL/SQL is better and more efficient than Java for managing SQL data types in
SQL map closer to that of PL/SQL. In a scenario with many database read/writes
and very little computation, code written in PL/SQL always runs significantly
faster then similar code written in Java (approximately ten times faster).
Java is more suited for the writing of programs that implement intensive logic
and use object oriented style of coding. In a scenario with much computation
and few database read/writes, code written in Java always runs significantly
faster than similar code written in PL/SQL (up to approximately five times
faster).
Note: Java has been introduced to compliment PL/SQL, not replace it.
It is not necessary to rewrite PL/SQL programs to Java. Because
PL/SQL and Java inter-operate cleanly in Oracle 8i, the best answer
may in fact be to have a combination of both languages.
Oracle introduced the following two new init.ora parameters to limit Java
session memory usage:
JAVA_SOFT_SESSIONSPACE_LIM
- This parameter allows you to specify a soft limit on the amount of Java
memory usage in a session. If this parameter is exceeded, a message
is written to the alert.log. The default is 1 MB.
JAVA_MAX_SESSIONSPACE_SIZE
- This parameter sets a hard limit on the amount of Java memory usage a
session can have. If a session exceeds this parameter, it terminates
with the following error:
ORA-29554 : unhandled Java out of memory condition
The default is four GB. This limit was purposely set very high so that
it would not normally be visible.
JServer Accelerator (8.1.7 only)
--------------------------
The JServer Accelerator(NCOMP) Utility is new with 8.1.7. It is designed to
increase the speed of Java code execution by creating platform specific shared
libraries that can be deployed to the server.
In 8.1.5 and 8.1.6, the core classes that are shipped with JVM are already in
NCOMP format, but there is not a utility that would do it for users classes.
All user classes are translated into byte code, and a byte code interpreter,
stored internally in the database, interprets each class at runtime. This is
a slow method and the JServer Accelerator allows for the Java classes to
already be in a natively compiled C language format.
This NCOMP format has shown between a two to five times faster runtime than
non-NCOMP formats. The individual results still depend on the application.
SQL intensive applications may not see as a high of a performance gain, other
applications may see a greater increase.
Related Documents
-----------------
"Oracle 8.1.6 Oracle JAVA Stored Procedures Developer's Guide"
"Oracle 8.1.6 Oracle JAVA Developer's Guide"
.
Could u ellaborate on this , I followed the instructions that u had mentioned but the $ORACLE_HOME folder
itself is missing.Other than 8.1.5 i dont have anything else installed.
I need a web server can i use IIS or do i need to get something else like 9ioas ,apache etc..
*** looks like your Oracle installation does not include the Oracle Apache Service (i.e. OracleHTTPService), which include modules for PL/SQL, Servlet Engine, and JServer module.
you can either install the 9ias OracleHTTPService (i.e. intended to be a middle-tier application server on an N-tier configuraion, WEB cache, Data cache, scalable, etc.) OR upgrade your existing Oracle8.1.5 installation. w/o know your overall strategy, i recommend keeping it simple at this stage and upgrading your 8.1.5 with the OracleHTTPService:
Also, load your Oracle Server CD, and validate whether you need to install JVM software from the CD as well.
*** Consider upgrading your Oracle Server to 8.1.7, it may be easier for you at this point, and provide JServer accelerator and other bug fixes.
Doc ID: Note:132936.1
Subject: What scripts are needed to manually install Oracle8i JVM (8.1.5 - 8.1.7)
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 19-JAN-2001
Last Revision Date: 19-APR-2001
PURPOSE
-------
This note describes what sql scripts are needed to fully install
all components of Oracle8i JVM, in version 8.1.5, 8.1.6 and 8.1.7.
Scripts must be executed as SYS or INTERNAL.
The 'initjvm.sql' script is responsible for the installation of the JVM, the subsequent
scripts mentioned will install the added functionality described by
the corresponding comments (prefixed by the "--" characters).
SCOPE & APPLICATION
-------------------
Manual istallation of Oracle8i JVM
--------------------------
Folow the checklist guide for a manual install of the Oracle8i JVM as described in
[NOTE:105472.1]Setup, Configuration, and Use of the Java Virtual Machine (JVM)
[NOTE:103855.1] Jserver Installation for Oracle8i
Note: ? is sqlplus substitution fo $ORACLE_HOME or %ORACLE_HOME%
and it can be used on both Unix and Windows platforms.
Scripts needed for JVM version 8.1.5
--------------------------
Only script needed for 8.1.5 version is initjvm.sql
-- Setup a database for running Java and the ORB
SQL>spool initjvm.log
SQL>@?/javavm/install/init
SQL>spool off
Scripts needed for JVM version 8.1.6
--------------------------
In 8.1.6 additional scripts are needed:
-- Setup a database for running Java and the ORB
SQL>spool initjvm.log
SQL>@?/javavm/install/init
SQL>spool off
-- Initializes Java library needed by PL/SQL
SQL>spool initplsj.log
SQL>@?/rdbms/admin/initpls
SQL>spool off
-- Load AQ/JMS jar files into the database
SQL>spool initjms.log
SQL>@?/rdbms/admin/initaqj
SQL>spool off
-- Load RepAPI server classes and publish 'repapi' obj
SQL>spool initrepapi.log
SQL>@?/rdbms/admin/initrep
SQL>spool off
Scripts needed for JVM version 8.1.7
--------------------------
In 8.1.7 many new Java API's and components are added.
So, we need to run even more scripts :
-- Setup a database for running Java and the ORB
SQL>spool jvminst.log
SQL>@?/javavm/install/init
SQL>spool off
-- Loads xml components into the JServer
SQL>spool initxml.log
SQL>@?/oracore/admin/initx
SQL>spool off
-- loads the XMLSQL Utility (XSU) into the database
SQL>spool catxsu.log
SQL>@?/rdbms/admin/catxsu.
SQL>spool off
-- JIS (OSE) installation
SQL>spool init_jis.log
SQL>@?/javavm/install/init
SQL>spool off
-- Turn on JAccelerator (ncomp) for JIS
SQL>spool jisja.log
SQL>@?/javavm/install/jisj
SQL>spool off
-- Adds the set of default end points to the server
-- with hardcoded values for the admin service
SQL>spool jisaephc.log
SQL>@?/javavm/install/jisa
SQL>spool off
-- Load PLSQL Gateway Servlet jar files into the database
SQL>spool initplgs.log
SQL>@?/rdbms/admin/initplg
SQL>spool off
-- Install Oracle JSP
SQL>spool initjsp.log
SQL>@?/jsp/install/initjsp
SQL>spool off
-- Turn on JAccelerator for JSP libs
SQL>spool jspja.log
SQL>@?/jsp/install/jspja.s
SQL>spool off
-- Initializes Java library needed by PL/SQL
SQL>spool initplsj.log
SQL>@?/rdbms/admin/initpls
SQL>spool off
-- Load AQ/JMS jar files into the database
SQL>spool initjms.log
SQL>@?/rdbms/admin/initjms
SQL>spool off
-- Load RepAPI server classes and publish 'repapi' obj
SQL>spool initrepapi.log
SQL>@?/rdbms/admin/initrep
SQL>spool off
-- loads sql, objects, extensibility and xml related java
SQL>spool initsoxx.log
SQL>@?/rdbms/admin/initsox
SQL>spool off
-- Configure OSE defauls admin Web Service
SQL>spool jisaep.log
SQL>@?/javavm/install/jisa
SQL>spool off
SQL>
RELATED DOCUMENTS
-----------------
Note.105472.1 Setup, Configuration, and Use of the Java Virtual Machine (JVM)
Note.103855.1 Jserver Installation for Oracle8i
*** make sure all objects are valid:
select object_name from all_objects
where status='INVALID'
Qb. Which "Web Publishing Assistant" are you referring to-
The web publishing assistant is
START>PROGRAMS>ORACLE-ORAH
This wizard states that it creates dynamic web pages by stating the database<name> that it has to link
to.
*** i see, i'll look into this, no comment yet.
Qc. what type of Web Page are you creating w/ Oracle, you have a myriad of choices: PL/SQL Pages(PSP),
JSP, JAVA stored procedures...
*** great.
I am working on PSP right now but what would your suggestions on developer 2000 be,because forms running
queries would be the main issue that i am working on.
*** if you plan on using Oracle Forms, than consider the Forms 6i, internet deployable forms. the old client-server forms are not suitable for the new 8i Internet Architecture.
**** if you are creating your own forms with JSPs or PSPs than even better, i understand the Oracle 6i forms can be slow.
sounds like you have "interesting problems" in this regard you will learn much and do well.
see ya,
Eric
below is another doc that may interest you:
Doc ID: Note:105472.1
Subject: Setup, Configuration, and Use of the Java Virtual Machine (JVM)
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 14-APR-2000
Last Revision Date: 17-APR-2001
Overview
--------
This article contains an introduction to the Java Virtual Machine (JVM).
It is written for the beginning to moderate level DBA.
After reading this article, you should be able to do the following:
- install the JVM
- understand the JAVA POOL (init.ora parameter JAVA_POOL_SIZE).
- create a Java stored procedure
- explain what the initjvm, jvmu815, and the rmjvm.sql scripts do
- use the LOADJAVA utility
- describe the security options available for Java
- know/understand what objects are created by the LOADJAVA utility
Introduction
------------
The Java Virtual Machine is a scalable, general-purpose Java execution
environment that is compliant with the SUN Java JDK specification and has been
optimized to leverage the Oracle architecture.
The following is Oracle JDK version compliance:
Oracle 8.1.5 is compliant with JDK 1.1
Oracle 8.1.6 is compliant with JDK 1.2
Java is complimentary to PL/SQL and fulfills other application needs. SQL
and PL/SQL can be called from Java programs through an embedded JDBC driver
in the database. In turn, Java procedures can be called from SQL and PL/SQL
once they have been published as a PL/SQL program.
Java Scripts
------------
*** All scripts are located in the $OH/javavm/install directory ***
initjvm.sql
===========
This script is the heart of the JVM installation process. It loads the
initial set of Java classes necessary to support Java, initializes the SQL
tables for the CORBA namespace, and publishes some top-level entry points
through call specifications.
rmjvm.sql
=========
The rmjvm.sql removes any trace of Java usage. This includes core classes,
user created classes, and all the Java objects from the data dictionary. It
should only be run if you wish to remove all Java objects from a database.
Once this script is run, all of your Java objects will be gone, unless you
have a backup.
jvmu815.sql
===========
This script should be run when upgrading a database from 8.1.5 to 8.1.6 and
Java was used in the 8.1.5 database. It preserves user classes while replacing
system classes, whereas if you ran rmjvm.sql followed by initjvm.sql, it
removes user classes and replaces system classes.
JAVA Installation
-----------------
The Java Virtual Machine can be installed using one of the following two
methods:
- An Oracle8i typical or minimal install installs JVM automatically.
- An Oracle8i custom install installs JVM, when the JVM option is selected.
The Java Virtual Machine can be activated in a new or existing database by doing one
of the following:
- Create a database with the database assistant and choose to activate java (when
selecting to activate java for the new database, it will take longer for the database
to be created because it will run the initjvm.sql script automatically)
- You can manually run the initjvm.sql script as internal or sys (the
script can take over an hour to run).
The initjvm.sql script does the following to install the JVM:
? Creates and loads the Java classes into the SYS schema using the CREATE
or REPLACE JAVA SYSTEM command.
? Creates public synonyms for the loaded classes to be accessible to all
users.
? Creates roles that can be used with Java.
? Defines database startup and shutdown triggers.
? Configures the JVM for CORBA and EJBs.
? Creates the DBMS_JAVA package by calling the initdbj.sql script.
? Installs SQLJ by calling the initsqlj.sql script.
SYSTEM Requirements (These numbers are recommendations):
? SHARED_POOL_SIZE >= 65 MB
? JAVA_POOL_SIZE >= 50 MB
? 50 MB free in the SYSTEM tablespace
? 250 MB of rollback segment space
Verifying Installation
----------------------
The banner in svrmgrl/sqlplus shows that Java Options have been installed.
The following query checks that the number of Java objects created is greater
than zero:
SELECT count(*)
FROM dba_objects
WHERE object_type LIKE '%JAVA%';
If the above query returns a number < 4000, the installation was not
successful.
The query below checks for any invalid Java classes using a Java stored
procedure:
SELECT dbms_java.longname(name)
FROM sys.obj$
WHERE type# = 29 and status != 1;
If the above query fails or returns any rows, then something is wrong with
the JVM installation.
Sizing and Configuring the JAVA_POOL_SIZE
--------------------------
The shared parts of each Java class are stored in the Java pool. This includes
read-only memory, such as methods. The init.ora parameter JAVA_POOL_SIZE
controls the size of the Java pool which defaults to 20 MB.
Java pool is consumed primarily during class loading, although compilation and
resolution also consume Java pool memory. The more Java classes and code that
is actually in use in your database instance, the more Java pool is required.
There is no incremental per-session cost for the Java pool. The per class
memory requirement can average about 4-8 KB for each class. On a dedicated
server only using Java stored procedures, it is possible that the
JAVA_POOL_SIZE could be as low as 10 MB.
None of the per session Java states are stored in the Java pool. For dedicated
servers, it is stored in the UGA within the PGA. Under Multi-Threaded Server
(MTS), which is required for CORBA and EJBs, the Java pool could be very large.
CORBA and EJBs require more memory, large Java-intensive applications could
require up to one GB of Java pool memory.
In MTS servers, some of the UGA used for per session Java states are stored in
the Java pool. Since the Java pool is fixed in size, you must estimate the
total requirement for your applications and multiply by the number of
concurrent sessions they want to create. All UGAs must be able to fit in the
Java pool.
As a general guideline, the JAVA_POOL_SIZE should be set to 50 MB or higher for
large applications. The default of 20 MB should be adequate for typical Java
stored procedure usage.
To determine how much Java pool memory is being used, query V$SGASTAT:
SELECT *
FROM V$SGASTAT
WHERE pool = 'java pool';
Add free memory to memory in use to determine the size of the Java pool.
Common Errors Installing/Using JVM
--------------------------
ORA-3113 : end-of-file on communication channel running initjvm.sql
-- An ORA-3113 can occur when there is a lack of resources. Check the
following settings:
SYSTEM Requirements (These numbers are recommendations)
? SHARED_POOL_SIZE >= 65 MB
? JAVA_POOL_SIZE >= 50 MB
? 50 MB free space in the SYSTEM tablespace
? 250 MB of rollback segment space
Note: This error is documented in [BUG:1160778], entitled "ORA-3113 ON
CREATE OR REPLACE JAVA SYSTEM; IN INITJVM.SQL".
ORA-4030 : out of process memory when trying to allocate %s bytes (%s,%s)
running initjvm.sql
-- This problem can be solved by setting all ulimit parameters to UNLIMITED
by typing: unlimit.
Note: This problem is documented in [NOTE:101019.1], entitled
"ORA-04030: running initjvm.sql script".
ORA-4031 : unable to allocate bytes of shared memory during loadjava or
deployejb
-- To resolve this problem, shutdown your database and reset the
SHARED_POOL_SIZE or JAVA_POOL_SIZE parameter to a larger value.
The mention of 'shared pool' in the error message can be misleading
because the error indicates you are running out of memory in the
'Shared Global Area'. It does not always mean you should increase your
SHARED_POOL_SIZE, you may need to increase your JAVA_POOL_SIZE instead.
In this case, you may need to increase both parameters to resolve
the problem.
Creating Java Stored Procedures
--------------------------
To create a Java Stored Procedure, perform the following steps:
1. Write the Java program.
2. Load the program into the database.
3. Publish the Java program to SQL (PL/SQL Wrapper).
4. Call the Java program from SQL or PL/SQL.
Step 1: Write the Java program.
======
EchoInput.java.
public class EchoInput {
public static void main (String[] args){
for (int i=0; i<args.length;i++)
System.out.println(args[i]
Step 2: Load the program into the database.
======
Issue the following command from the UNIX prompt:
% loadjava -u system/manager -v -r EchoInput.java
Or manually from SQL*Plus:
CREATE OR REPLACE JAVA SOURCE NAMED "EchoInput" AS
public class EchoInput { public static void main (String[] args) {
for (int i=0; i<args.length;i++) System.out.println(args[i]
Step 3: Publish the Java stored procedure
======
To call a Java procedure from SQL or PL/SQL, you must publish the
procedure to SQL. This is also referred to as creating a PL/SQL Wrapper.
Once the procedure is published/wrapped, it can be called from any PL/SQL
routine or SQL statement.
For example:
create or replace procedure echo_input (
s1 varchar2, s2 varchar2, s3 varchar2)
as language Java
name 'EchoInput.main(java.lang.
Step 4: Call the Java program from SQL or PL/SQL.
======
call dbms_java.set_output(5000)
call echo_input('It', 'works', 'now!');
Note: Please note that to run this in SQL*Plus, you need to
specify 'set serveroutput on'
LOADJAVA/DROPJAVA Utility
-------------------------
The LOADJAVA Utility assists in the loading of Java classes/source into the
database. The utility is invoked from the Operating System prompt, and behaves
much like the Export/Import Utility.
When the utility is invoked, it connects to the database and attempts to load
the Java files into the schema of the user it connects to as, or into the
schema of the user who is specified in the -schema parameter.
Based on this, the schema that the objects are loaded into must meet the
following three criteria:
- The user must exist.
- The user must have sufficient quota in the default tablespace.
- There must be enough free space in the tablespace.
Common options for the LOADJAVA utility include:
- user : The user that you are logging into the database as
- schema : The schema you want the objects loaded into if it is different
from the -user parameter. If this is not specified, the objects
are loaded into the logon schema.
- verbose : Enables the verbose mode in which progress messages are
displayed
- resolve : After all the objects are loaded and compiled, resolves all
external references in those classes. If this option is not
specified, files are loaded but not compiled or resolved until
runtime.
- oci8 : Directs loadjava to communicate with the database using the OCI
JDBC Driver (default driver).
- thin : Directs loadjava to communicate with the database using the thin
JDBC Driver.
When the LOADJAVA Utility is used, six objects (below) are created in the
users' schema if they do not already exist. These objects assist in the
loading of the Java objects into the database.
1. JAVA$CLASS$MD5$TABLE
- This is a hash table that tracks the loading of Java objects into a schema.
** If you use the loadjava utility to load the objects, you must use the dropjava
utility to drop the objects otherwise this table is not update.
2. CREATE$JAVA$LOB$TABLE
- This table contains the name of the Java object, the date it was loaded,
and has a BLOB column to store the Java object.
3. LOADLOBS (package and package body)
- This package is used to load the Java object into the BLOB column in the
CREATE$JAVA$LOB$TABLE.
4. Three indexes:
- one on the name column of the CREATE$JAVA$LOB$TABLE
- one on the LOB column of the CREATE$JAVA$LOB$TABLE
- one on the name column of the JAVA$CLASS$MD5$TABLE
The DROPJAVA Utility converts the filenames into the names of schema objects,
drops the objects, and then deletes their corresponding row in the
JAVA$CLASS$MD5$TABLE.
The options available are:
- oci8
- schema
- thin
- verbose
The behavior for the above options is the same as in LOADJAVA.
Java Security
-------------
In 8.1.5, security is provided only through the use of database roles. In
8.1.6, JServer security is based on Java 2 security which allows developers to
assign privileges on a class by class basis.
The Java related roles in 8.1.5 still exist in 8.1.6 for backward
compatibility, however, Oracle recommends that you specify each permission
explicitly, rather than use the roles.
In 8.1.5 the initjvm.sql script creates two roles named JAVAUSERPRIV and
JAVASYSPRIV.
JAVAUSERPRIV privileges:
- open a TCP/IP socket
- read or write from a file using UTL_FILE_DIR
JAVASYSPRIV privileges:
- all the privileges associated with JAVAUSERPRIV
- create a subprocess
- listen on a specific network port
- set the socket factory
- set the stream handler
- read or write from any file
In Java 2 security, you create security policies or permissions for each
specific class. Within Oracle8i, you specify the policy through the DBMS_JAVA
package, and the security policy is stored within the PolicyTable.
The PolicyTable is a new table within the database that exclusively manages
Java 2 security permissions. You can grant or revoke permissions dynamically
by using PL/SQL through DBMS_JAVA procedures or Java through the class:
oracle.aurora.rdbms.securi
In summary, the following two methods may be used to manage privileges:
Fine Grain Definition
- You grant each permission individually for specific users or roles. If
you do not grant a permission for access, the schema is denied.
General Permission Definition
- An alternative to the Fine Grain method is that you can make use of the
two roles supplied by Oracle: JAVAUSERPRIV and JAVASYSPRIV.
The following two views have been added in 8.1.6 to view the policy table:
DBA_JAVA_POLICY - can see all rows within the policy table
USER_JAVA_POLICY - can only see permissions relevant to the current user
Both views contain information about granted and restricted permissions.
Note: For more information on the security in Java, refer to the "Oracle
8.1.6 Java Developers Guide".
JVM Tuning Considerations
-------------------------
Which is faster - Java or PL/SQL? Performance numbers are not yet available.
In general, PL/SQL is best for data intensive programs and Java is best for
compute intensive coding.
PL/SQL is better and more efficient than Java for managing SQL data types in
SQL map closer to that of PL/SQL. In a scenario with many database read/writes
and very little computation, code written in PL/SQL always runs significantly
faster then similar code written in Java (approximately ten times faster).
Java is more suited for the writing of programs that implement intensive logic
and use object oriented style of coding. In a scenario with much computation
and few database read/writes, code written in Java always runs significantly
faster than similar code written in PL/SQL (up to approximately five times
faster).
Note: Java has been introduced to compliment PL/SQL, not replace it.
It is not necessary to rewrite PL/SQL programs to Java. Because
PL/SQL and Java inter-operate cleanly in Oracle 8i, the best answer
may in fact be to have a combination of both languages.
Oracle introduced the following two new init.ora parameters to limit Java
session memory usage:
JAVA_SOFT_SESSIONSPACE_LIM
- This parameter allows you to specify a soft limit on the amount of Java
memory usage in a session. If this parameter is exceeded, a message
is written to the alert.log. The default is 1 MB.
JAVA_MAX_SESSIONSPACE_SIZE
- This parameter sets a hard limit on the amount of Java memory usage a
session can have. If a session exceeds this parameter, it terminates
with the following error:
ORA-29554 : unhandled Java out of memory condition
The default is four GB. This limit was purposely set very high so that
it would not normally be visible.
JServer Accelerator (8.1.7 only)
--------------------------
The JServer Accelerator(NCOMP) Utility is new with 8.1.7. It is designed to
increase the speed of Java code execution by creating platform specific shared
libraries that can be deployed to the server.
In 8.1.5 and 8.1.6, the core classes that are shipped with JVM are already in
NCOMP format, but there is not a utility that would do it for users classes.
All user classes are translated into byte code, and a byte code interpreter,
stored internally in the database, interprets each class at runtime. This is
a slow method and the JServer Accelerator allows for the Java classes to
already be in a natively compiled C language format.
This NCOMP format has shown between a two to five times faster runtime than
non-NCOMP formats. The individual results still depend on the application.
SQL intensive applications may not see as a high of a performance gain, other
applications may see a greater increase.
Related Documents
-----------------
"Oracle 8.1.6 Oracle JAVA Stored Procedures Developer's Guide"
"Oracle 8.1.6 Oracle JAVA Developer's Guide"
.
ASKER
All the suggestions have been great so far,and i think i should be able to simulate what i plan to achieve in a couple of days...hopefully.
Using developer 6.0 i have created forms and they work fine in the intranet....but to get it on the web i am working on getting it to work as a java applet.For which i am trying to upgrade to oracle 8i release 2 and also get the http server and JVM figured out .Which as per your previous response should ship along with 8.1.7.
Will get back soon hopefully not with any more problems.
Thanks.
Using developer 6.0 i have created forms and they work fine in the intranet....but to get it on the web i am working on getting it to work as a java applet.For which i am trying to upgrade to oracle 8i release 2 and also get the http server and JVM figured out .Which as per your previous response should ship along with 8.1.7.
Will get back soon hopefully not with any more problems.
Thanks.
Please update and finalize this old, open question. Please:
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks to all,
Moondancer - EE Moderator
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks to all,
Moondancer - EE Moderator
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
The given answer is correct and oriented the asker what to do.
Thanks; finalized today by Moondancer - EE Moderator
Oracle8.1.7 on Windows2000 is an Oracle Certified Web enabled platform.
OVERVIEW
Oracle8i has integrated a Java Virtual Machine (JVM), a Java dB Connectivity (JDBC) driver, and an Object Request Broker (ORB) directly into the database engine. This new infrastructure allows you to easily develop dynamic db-driven intranet and Web apps using 2 different programming models: stored procedures and component-based modeling.
a. Stored procedures can be written in Java or PL/SQL. Java programs can access data via JDBC or SQLJ (e.g. embedded SQL in Java, very simle). Java stored programs reap performance benefits from an embedded native compiler and the tight integration of the JVM w/ db.
b. Component-based prgms can be written using 2 models. Common Object Request Broker Architecture (CORBA) is a standard way of building distributed apps. Enterprise Java Beans (EJB) is the server-side component model for Java.
IMPLEMENTAION QUESTIONS:
1)I have installed Oracle 8i enterprise version on windows 2000 server.
- this config is Oracle Certified
2)I created a database during installation.
- good
3)I tried to create a web page and connect to this database using the Web Publishing Assistant -
But the web page was not generated.
Qa. Is the OracleHTTPService on Win2000 running -
Qb. Which "Web Publishing Assistant" are you referring to-
Qc. what type of Web Page are you creating w/ Oracle, you have a myriad of choices: PL/SQL Pages(PSP), JSP, JAVA stored procedures...
this doc may be of use, it's an intro to writing PSP:
***This document aims to introduce Oracle developers to the concepts
of PL/SQL Server Pages, and write the first Internet based PL/SQL
procedure.
SCOPE & APPLICATION
-------------------
For users who are developing dynamic Internet sites based on Oracle
technology.
What are PL/SQL Server Pages? (PSPs)
--------------------------
A PL/SQL Server Page is a dynamic, database driven page of Internet
content built mainly in the easy to write HTML language, with dynamic
pieces on Oracle content specified in scriptlet tags similar to those
used in JavaServer Pages.
PSPs are based on technology first introduced in Oracle Application
Server which is now the platform for Oracle's Enterprise Portal offering
Oracle Portal. This technology known as the PL/SQL Web Toolkit is
shipped pre-installed in the database, so no additional packages need
to be loaded.
By writing an HTML page with embeded PL/SQL code and compiling it as a
PL/SQL Server Page, you may call procedures from the PL/SQL Web Toolkit,
but are not required to generate every line of HTML output.
Building a PSP.
The first requirement is a PL/SQL Gateway through a Web Listener.
Typically this is Oracle Portal or Apache with the mod_plsql module,
pre-configured with Oracle 8.1.7 or Internet Application Server (iAS).
Once this is established, you will have a virtual path, typically
http://servername/DAD_name/procedurename
that you can access the database with. The name of your PSP will
form the procedure name.
Using basic HTML and with a page directive and procedure directive
we construct the page.
<%@ page language="PL/SQL" %>
<%@ plsql procedure="first" %>
<HTML>
<HEAD><TITLE>This is a PSP Page!</TITLE></HEAD>
<BODY>
This is a list of employees: <BR>
<% for emp in (select * from emp order by ename) loop %>
Employee ID = <%= emp.empno %>.
Employee Name = <%= emp.ename %><BR>
<% end loop; %>
</BODY>
</HTML>
This will scroll through the list of employees in the emp table,
outputting to a browser the employee ID and employee name. Notice
that any PL/SQL call can be made in a PSP page given the executing
user's correct credentials.
We now use the loadpsp command to place the PSP inside the database.
The syntax for loadpsp is as follows:
usage: loadpsp [-replace] -user <logon> [<page1> <page2> ...]
where <logon> ::= <username>/<password>[@<co
-replace will replace this procedure with any existing procedures of
the same name, regardless of compilation outcome. That is if this
procedure fails to compile correctly there will be an invalid procedure
in the schema, therefore errors will be returned to the user when
accessing that URL. This argument must be used when an object of the
same name already exists in the target schema.
-user is the username and password with any connect string.
<page1..n> are the files that contain procedures.
There is currently no way to combine these procedures in a single
package inside the database.
D:\Oracle>loadpsp -replace -user scott/tiger@orcl.world first.psp
Should return
"first.psp": procedure "first" created.
The final step is to load the PSP in the browser. My DAD is called
SCOTT_ORCL
so my URL is
http://multra2.au.oracle.com:8181/SCOTT_ORCL/first
And the output should be
This is a list of employees:
Employee ID = 7876. Employee Name = ADAMS
Employee ID = 7499. Employee Name = ALLEN
Employee ID = 7698. Employee Name = BLAKE
Employee ID = 7782. Employee Name = CLARK
Employee ID = 7902. Employee Name = FORD
Employee ID = 7900. Employee Name = JAMES
Employee ID = 7566. Employee Name = JONES
Employee ID = 7839. Employee Name = KING
Employee ID = 7654. Employee Name = MARTIN
Employee ID = 7934. Employee Name = MILLER
Employee ID = 7788. Employee Name = SCOTT
Employee ID = 7369. Employee Name = SMITH
Employee ID = 7844. Employee Name = TURNER
Employee ID = 7521. Employee Name = WARD
If you do not get the expected result the first debugging step is
to check that the procedure is in fact VALID inside the SCOTT
schema. The following SQL will assist you with this
select object_name, object_type, status from user_objects where
object_name like 'F%';
The output you get should look something like
OBJECT_NAME OBJECT_TYPE STATUS
--------------------------
FIRST PROCEDURE VALID
If the procedure is INVALID, then go back and check the syntax for
your PSP. A common mistake is to leave of the '=' in a directive.
This will disrupt the conversion of your PSP to a procedure. If the
object does not exist at all in the schema, ensure that the correct
permissions are available (connect, resource), and that your loadpsp
command was in fact directed at the correct database.
RELATED DOCUMENTS
-----------------
Oracle8i Application Developer's Guide - Fundamentals
Chapter 15: Developing Web Applications with PL/SQL
*********************
I think it is due to the fact that the created database does not have any contents to run a query on.
* You may not have configured the OracleHTTPService on windows2000, below are the key concepts/files
1. $ORACLE_HOME/apache/modpls
2. $ORACLE_HOME/apache/modpls
wdbsvr.app - defines DAD, Database Access Description, it describes the oracle user/password, connect-string (tnsnames.ora), and HTML file location.
3. $ORACLE_HOME/apache/apache
4. $ORACLE_HOME/apache/apache
5. $ORACLE_HOME/apache/apache
6. $ORACLE_HOME/apache/apache
7. enable browser cookies
8. windows2000 environment variable: WV_GATEWAY_CFG=
$ORACLE_HOME/apache/apache
9. test by invoking Web browser, enter: http:[server_name]
it should bring up the ORACLEHTTPService demo page that introduces you to the various Web Modules: OSE,JSP,PSP, etc..
see ya,
Eric
4)I used the Database configuration assistant and created a new database but the same results again.....so
i dint get anywhere.
- see above
5)I created a new table using SQL and populated it but then i have no idea how to connect it to the
web page.
- see above