Home » SQL & PL/SQL » SQL & PL/SQL » Calling External Java Program Not residing in Oracle
Calling External Java Program Not residing in Oracle [message #43689] Tue, 19 August 2003 10:04 Go to next message
MK
Messages: 31
Registered: June 2001
Member
I have posted this in both Java/XML and PL/SQL forum.

We have Oracle9i Rel 2.

I wanted to know if it is possible to call an external Java program from a database trigger?
I know you can call stored procedures in Java, but I wanted to know if it is possible to call
external Java program not residing in oracle.

I want the DB trigger to call a piece of Java code (which generates an XML document) and then store the XML document in the oracle DB.

regards
MK
Re: Calling External Java Program Not residing in Oracle [message #43698 is a reply to message #43689] Tue, 19 August 2003 20:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can call a java program from a pl/sql function and call that pl/sql function from a database trigger. You can also use java to exectue operating system files. You can also create XML from pl/sql. The following is a real basic example, just to give you the general idea, of a database trigger that calls a pl/sql function that calls a java program and stores the result in a CLOB column of a table in the database. You will need to make some modifications to be compatible with your java program that creates the XML document. If this is insufficient to get you started, then please provide more details and your code.

SQL> -- test tables:
SQL> CREATE TABLE test_table
  2    (id	NUMBER,
  3  	message VARCHAR2 (30))
  4  /

Table created.

SQL> CREATE TABLE docs
  2    (docid  NUMBER,
  3  	doc    CLOB)
  4  /

Table created.

SQL> --
SQL> --
SQL> -- java program:
SQL> create or replace and compile
  2  java source named "Hello"
  3  as
  4  public class Hello
  5  {	public static String world()
  6  	{
  7  	   return "Hello World";
  8  	}
  9  }
 10  /

Java created.

SQL> --
SQL> --
SQL> -- pl/sql function:
SQL> CREATE OR REPLACE FUNCTION jva
  2    RETURN VARCHAR2
  3  AS
  4    LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';
  5  /

Function created.

SQL> --
SQL> --
SQL> -- database trigger:
SQL> CREATE OR REPLACE TRIGGER test_table_bir
  2    BEFORE INSERT ON test_table
  3    FOR EACH ROW
  4  DECLARE
  5    v_message VARCHAR2 (30);
  6    v_clob CLOB;
  7  BEGIN
  8    v_message := jva ();
  9    :NEW.message := v_message;
 10    INSERT INTO docs (docid, doc)
 11    VALUES (:NEW.id, EMPTY_CLOB ())
 12    RETURNING doc INTO v_clob;
 13    DBMS_LOB.WRITEAPPEND (v_clob, LENGTH (v_message), v_message);
 14  END test_table_bir;
 15  /

Trigger created.

SQL> SHOW ERRORS
No errors.
SQL> --
SQL> --
SQL> -- test by doing insert:
SQL> INSERT INTO test_table (id) VALUES (1)
  2  /

1 row created.

SQL> --
SQL> --
SQL> -- check results:
SQL> SELECT * FROM test_table
  2  /

        ID MESSAGE                                                              
---------- ------------------------------                                       
         1 Hello World                                                          

SQL> SELECT * FROM docs
  2  /

     DOCID                                                                      
----------                                                                      
DOC                                                                             
--------------------------------------------------------------------------------
         1                                                                      
Hello World                                                  
Re: Calling External Java Program Not residing in Oracle [message #201816 is a reply to message #43698] Mon, 06 November 2006 21:40 Go to previous message
Muraligopal
Messages: 1
Registered: November 2006
Location: Chennai
Junior Member
Hi Friends,

I have one requirement to call java program with in a Trigger.

I have Written one simple java program that says Hello and a simple procedure that will call this java program like

CREATE OR REPLACE PROCEDURE Hello_proc
AS LANGUAGE JAVA
NAME 'Hello.sayHello()';

My Question is where to put the compiled Java Class in Oracle ?.

Now i am getting the error Like ,

ORA-29540: class Hello does not exist
ORA-06512: at "SCOTT.HELLO_PROC", line 0

can any one Help me regarding this.

Thanks & Regards
K.Murali gopal
Previous Topic: Handling Java Exceptions from PL/SQL Code
Next Topic: Trigger for capture the information
Goto Forum:
  


Current Time: Sat Dec 10 15:05:03 CST 2016

Total time taken to generate the page: 0.13907 seconds