PL/SQL

Your organization is now asking you to write a simple java program to identify a special problem that can be classified by oracle�s problems only.

You have been assigned to:

1- Check for a successful installation of �initjvm.sql,�

2- Check JAVA pool memory usage,

3- Create the JAVA class, and

4- Publish the JAVA program to SQL.

Connect to SQLPLUS as the system/manager user.
SQL> CONNECT system/manager
 

Check to see that you have the JAVA tool installed.
SQL> SELECT COUNT(*) FROM dba_objects
WHERE object_type LIKE 'JAVA%'
SQL> /
This response, indicates that the JAVA tool was previously installed successfully. If you get a number less than 1200, you may have had problems with the installation process. And we recommend that you re-install the JAVA component.
 

Also Check the JAVA pool memory usage.
SQL> SELECT * FROM v$sgastat
WHERE pool LIKE 'java%'
SQL> /
You must have at least 30 megabytes of memory.
 

Memory limitation
Notice that the amount of FREE MEMORY + MEMORY IN USE should add up to more than 30 megabytes.

If you have enough memory allocated for JAVA. Connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Create an oracle java class, to return the oracle messages.
SQL> CREATE OR REPLACE JAVA SOURCE NAMED "oracle" AS
public class oracle {
static public String message (String tail) {
return "oracle-" + tail;
}
}
SQL> /
 

Publish the JAVA class to SQL by creating a PL/SQL function. Notice that JAVA programing is very case sensitive.
SQL> CREATE OR REPLACE FUNCTION error_msg (str VARCHAR2)
RETURN VARCHAR2
AS

END error_msg;
SQL> /
This is an example of how to call JAVA from PL/SQL in Oracle9i.
 

Test the error message function.
SQL> SELECT error_msg ('01320: Running JAVA was successful.')
as "Message Function"
FROM dual
SQL> /
This is an example of the oracle message function.
 

Drop the JAVA source and the error_msg function.
SQL> DROP JAVA SOURCE "oracle"
SQL> /

Drop a JAVA function

SQL> DROP FUNCTION error_msg
SQL> /
You have dropped the created objects so that you can repeat these steps over if you wish.

Q: How do you check that you have the JAVA tool installed in your server?

Q: What should it be a least size for the JAVA pool memory usage?

Q: How do you create a JAVA class?

Q: How do you publish a JAVA class?

Q: How do you test a JAVA function?

Q: How do you drop a JAVA source and Function?