iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 19

“I am still determined to be cheerful and happy, in whatever situation I may be; for I have also learned from experience that the greater part of our happiness or misery depends upon our dispositions, and not upon our circumstances.” Martha Washington (1732 - 1802)

 

Read first then play the video:

   SQL-VIDEO -How to use JAVA Stream

  

Calling JAVA Stream in Oracle

 

Hands-On introduction

Your organization is now asking you to write a simple java program to identify a special problem that can be classified by iselfschooling’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


JAVA installation

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.


JAVA pool memory usage

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 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 iself user.
SQL> CONNECT iself/schooling


Create a JAVA class

Create an iself java class, to return the iselfschooling messages.
SQL>

CREATE OR REPLACE JAVA SOURCE NAMED "iself" AS
    public class iself

    {
    static public String message (String tail)

        {
            return "iSelfSchooling-" + tail;
        }
    }
SQL> /


Publish a JAVA class

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

BEGIN


    LANGUAGE JAVA NAME 'iself.message (java.lang.String)
    return java.lang.String';

 

END error_msg;


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


Test a JAVA function

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 iselfschooling message function.


DROP a JAVA source

Drop the JAVA source and the error_msg function.
SQL> DROP JAVA SOURCE "iself"
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.

 

“The farther behind I leave the past, the closer I am to forging my own character.” Isabelle Eberhardt

 

Questions:

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?