JDeveloper, Java & XML :: Using PL/SQL Function To Execute Java Class?
May 20, 2013
i`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.
table1 is something like this:
ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100
table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)
[code]....
Then i wanna make a pl/sql function for:
1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1
I need to call DB function through Java Hibernate.
My Sample XML File looks like. --------------------------------------------------------------- <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <sql-query name="getchequeno" callable="true"> ?:=call cz_nab_mw_fn_gen_ol_chq_no() </sql-query> </hibernate-mapping> --------------------------------------------------------------- I am getting below error while reading the file
"java.lang.RuntimeException: org.hibernate.HibernateException: Errors in named queries: getchequeno"
I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.
Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:
c:javajdkinjava -cp
and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:
C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.
Some thing like following:
I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.
My problem is how can I issue the
SELECT details FROM CONNECTION_DETAILS for USER2
so that i can get the details..?
Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.
I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?
I've this java source create or replace and compile java source named "Decodificador" as public class Decodificador { public static String decodifica(String codigo) return codigo;
and this function
create or replace function F_Decodificador(codigo varchar2) RETURN VARCHAR2 IS LANGUAGE java NAME 'Decodificador.decodifica(String) return String';
when I execute the function the result is:
ORA-29531: no method decodifica in class Decodificador
I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.
a) basic definitions (work and are ok)
CREATE OR REPLACE TYPE eventtype AS OBJECT ( relevance NUMBER, bid1 NUMBER,tid1 NUMBER,prob1 NUMBER, bid2 NUMBER,tid2 NUMBER,prob2 NUMBER, bid3 NUMBER,tid3 NUMBER,prob3 NUMBER, bid4 NUMBER,tid4 NUMBER,prob4 NUMBER, bid5 NUMBER,tid5 NUMBER,prob5 NUMBER, bid6 NUMBER,tid6 NUMBER,prob6 NUMBER ); CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype; /
b) The following call works (but is not exactly that what I want):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal'; / SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;
c) The following call doesn't work (but should have the intended semantics):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal'; / SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;
d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.
The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.
my java program i am executing a pl/sql function using callable statement i checked everything in the function and the function is created without compilation errors in sql*plus environment, and i gave all DBA permissions to the user, but i got the error like below
java.sql.SQLException: ORA-06550: line 1, column 28: PLS-00201: identifier 'NAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
and i wrote the function like below for the below table
and the java program is like below try{ Class.forName("oracle.jdbc.driver.OracleDriver"); Connection connection=DriverManager.getConnection(dbURL,dbUserName,dbPassword); CallableStatement st=connection.prepareCall("{?=call
I have database 10.2.0.3 with one procedure that calls some java class loaded in database. Unfortunately I don't have source code of that java class or whole jar that was loaded into database.I would like to download it from database and try to decompile it.
how can I download some java classes from database? Everything I know is only the name of some java class that is used in one PL/SQL procedure (CREATE OR REPLACE PROCEDURE ..... AS LANGUAGE JAVA).
when I import java class from oracle forms (import java class ->> FFileChooser) appeared FFileChooser package but i don't know how use it how use OPEN_DIALOG or other function .
I am using Developer 10g R2. I have a form with two fields named input_value and output_value and a button named ok. I have a java class named Factorial which return the factorial of the input value. Now i want to use this class into pl/sql in ok button.
I need to add one validation in one form. The form is not getting run locally because of some imported java classes which was already there. From application server it works fine. When i run locally it gives ORA_JAVA.EXCEPTION_THROWN. Actually some trigger calls a procedure from this imported java class. Some path is specified in JNI.NEW_OBJECT(). That patg may not be in local system. If those files are included in our oracle home directory
I need to invoke a JavaMail code from oracle server. Conditions are :
On insertion of a row in a table a TRIGGER is to be run. This trigger would invoke a java code, that shoots an email, with desired credentials and login.
The code to send email, is based on JavaMail API, and is compiled on JVM version, 1.6.
The javaMail code can be loaded in oracle server using:
loadjava -user scott@TESTDB SendMailSSL.class
Another code doscmd.class is written and loaded in oracle, that is compiled in java 1.5. It is intended to call the SendMailSSL.class
In my PLSQL package ...How do I invoke this class file ?
So far I have done this ...
Declare abc ORA_JAVA.JOBJECT; xyz ORA_JAVA.JOBJECT; xxx VARCHAR2(25); value ORA_JAVA.JOBJECT; BEGIN
[code]...
I am getting an error saying the object type is wrong .... I would like to know how to assign a hardcoded value to the JOBJECT just to test before I continue.
Our system stores images on the unix filesystem. We scan full page images.
Our image specs are:
black and white: tiff, group4 compression, 300dpi. so files are about 40-80k compressed
photograpshs/gray scale: jpeg/jpeg compression 256 gray scale. Some images can be 2megs...but most of them are about 400-700k
We build a demo with forms10G/application server 1 year ago and performance was really bad. Took about 10-15 seconds to generate a report with 1 image of it...compared to about 1-2 secs using forms 6i. We just installed weblogic 11G (10.3.5) with forms 11gr2. I'm guessing performance will still be an issue but we will still be testing it.
Our users currently use nfs to access images on the unix server. So that would not changed.
- read_image from filesystem (c: unc path //server/images/.../1.jpeg)
- zoom in, zoom out (having vertical/hori scroll bars to move around...or best would be panning)
- save_image on filesystem
- fit to window/cancas
- always keeping aspect ratio
nice to have options, but not needed at this time
- save in blobs
- editing capabilities (mostly just select an area to delete, like black borders...some speckles here and there)
How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.
All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup.
This code makes me unlucky while run that jsp report on web and comes up with orror no out put for the job.RegEdit REPORTS_CLASSPATH set to my oraclebarcode.jar with others jar files.
At Report builder web layout works fine [With Barcode generation] but not through Report server.
In my Organization I am trying to install oracle ODTwithODAC112030 but one Error showing that is java virtual machine could not find main class program will Exit.
Server Configuration Dell power Edge r510 Windows 2003 64 bit serv PAck 2 Citrix Presentation server 4.5 .
oracle PL/SQL. I have almost finished this xml parsing task but their is one problem. Actually in our table there are more than 70-80 columns & due to that only I don't want to put the hard coded column name in my procedure, because if I will do that, the unnecessary procedure size will be increase(means line of code).Here is our procedure
Create or replace procedure loadMyXML(dir_name IN varchar2, xmlfile IN varchar2) AS l_bfile BFILE; l_clob CLOB; l_parser dbms_xmlparser.Parser; l_doc dbms_xmldom.DOMDocument; l_nl1 dbms_xmldom.DOMNodeList; l_nl2 dbms_xmldom.DOMNodeList; l_n dbms_xmldom.DOMNode; node1 dbms_xmldom.DOMNode; l_colName VARCHAR2(100); [code]...
We need to get the single node with class type ="1" from this XML as given below.At any point of time, there will be only one such tag with classType="1". Its position could be anywhere inside the "GroupDetails" Tag.ie, The query should get the tag irrespective of its position inside "GroupDetails".
<info groupId="128" classType="1"/>
Is there any way using EXTRACT function with 3 parameter? I tried but with failure.
with rec AS ( select XMLTYPE( '<connection value="0"> <GroupDetails> <info groupId="100" classType="0"/> <info groupId="128" classType="1"/>