aspose file tools*
The moose likes JDBC and the fly likes Reg: Writing Oracle JavaStored Procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reg: Writing Oracle JavaStored Procedures" Watch "Reg: Writing Oracle JavaStored Procedures" New topic
Author

Reg: Writing Oracle JavaStored Procedures

SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi ,

I was trying to write a Java Stored Procedure and when i was loading the java class file into the database i am finding problems .

Whether i need to include any jars for this because i tried to load using the following command ,
loadjava -v -t -user username/password@host ort:sid classfile

but this command is not recognized.

can anyone help me out..

Thanks,
Sam
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Hi Sureshsam,

Welcome to JavaRanch, Please do not post same problem multiple times. It wastes others effort.

On the first place did you check if "loadjava.exe" exists or it is in path your system.


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi Shailesh,

I am new to this topic .. I dont have that in my classpath.

can u guide me where it will be. Can i know wats the importance of Java stored procedure when compared to PL/SQL procedures.

please help me out in solving this problem.

Thanks,
Sam
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

first you check that if loadjava.exe exist.

check it in folder "c:\oracle\ora92\bin"(Probably this is your installation path I picked it from another post of yours)

If this file does not exist then you would not be able to load java

Shailesh
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1124

Sam,
For your information, the loadjava utility is described in the Java Developer's Guide which is available from Oracle's Tahiti Web site.

Nonetheless, please post the entire error message you are getting, and I may be able to help you further. Also, if you tell us what Oracle database version you are using and what platform you are using, that may also be of help.

Good Luck,
Avi.
SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi abirami & Sailesh
I searched for it and it was not there and so i installed a new version of Oracle 9i and platform Windows XP.
Then I found it and i successfully loaded the class file and i also published it but when i tried to call the procedure i am getting SQL Exceptions.

This is the program through which i am calling the stored procedure .

CallableStatement cstmt = conn.prepareCall("{?= call TIC_getGreeting}");
cstmt.registerOutParameter(1, Types.CHAR);
long rslt = cstmt.executeUpdate();

this is the exception i am getting

Exception in thread "main" java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: invalid arguments in call
ORA-06512: at "SCOTT.TIC_GETGREETING", line 0
ORA-06512: at line 1

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at CallTestInternalConnection.main(CallTestInternalConnection.java:15)

Thanks,
Sam
SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi ,

I am getting depressed with this. I made some changes without knowledge and know i am getting different exception.

y is it giving "unknown source" in getConnection ??? the url which i am specifying is also correct.

I am giving the source code which i wrote and the errors i got.

please help me out.

exception :
hai --- ( this is print statement which i gave in my source code )
Exception in thread "main" java.lang.SecurityException: Prohibited package name: java.sql
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:521)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at PublishTestInternalConnection.main(PublishTestInternalConnection.java:13)

Source :


[edited to add code tags]
[ May 05, 2005: Message edited by: Jeanne Boyarsky ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

SureshSam,

few tips for you on javaranch before Jeanne or Bear tell you this

1) whenever posting source code, please put the code inside "code tag" you can simply click on code button then inside code tag you can place your code.This preserve formatting of your code and easier to understand

2) Don't use abbreviation like u, y etc

Originally posted by SureshSam Rajiv:

y is it giving "unknown source" in getConnection ???


this is because javaranch is community where people from all over world come who are not native english and they will face problem in reading this
Not only this just think if you can not make effort in typing your problem who would be interested in putting effort to solve them.


now into the your problem
could you tell what is your line 13(PublishTestInternalConnection.java:13)

As this thread started from java stored procedure so still it is not very clear to me where are you using your these programs

I guess these are being tested on command prompt only unlike java stored procedure which are called inside Oracle programs.

Shailesh
[ May 05, 2005: Message edited by: Shailesh Chandra ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Thanks Shailesh! I added the code tags you referred to.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi,
since i am very new to this forum i dont know the rules of sending messages. Any ways i will follow from here on.

Can you please tell me how to use the "code tag" and stuff like that.

Thanks,
Sam
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

SureshSam,

There is a button of code below the add reply button. when you click on code button it inserts a [C0DE] [/C0DE] tag in the post which actually preserve formatting of your code and that is only advice not a rule

Only rule we follow here is "Be Nice"

Now back to your problem ...you didn't tell us what is line 13 of your PublishTestInternalConnection.java or you have solved that problem

since now I would not be here for some time there is few advice for your problem

spaperate code of getting connection, I mean create a sepaerate method which retrun connection so you would be able to find if there is an error in getting connection or executing query.

now the sql you are executing in your program is creating a function in oracle is it requirement of you are doing it for learning .
If learning then start with simple programs because at any instance it is not good idea to execute DDL in java programs.
however there is no such rule that we can not execute DDL but such programs are meant to be executed only once.

Shailesh
[ May 06, 2005: Message edited by: Shailesh Chandra ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Originally posted by SureshSam Rajiv:
since i am very new to this forum i dont know the rules of sending messages. Any ways i will follow from here on.

Can you please tell me how to use the "code tag" and stuff like that.

Sam,
It's not actually a rule. It's just something that makes it easier to read the posts. Which in turn, helps you get a better/faster answer. When you write a post, there is a section called "Instant UBB Code" right below the edit window that you type in. Click the one called "code" and it will create the code tags. Then you copy/paste your code in between them. This preserves the formatting.

For more tips, you may be interested in

How to Ask Questions the Smart Way.

Jeanne
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1124

Sam,
Getting back to your problem, according to the stack trace you have provided, you are getting a "SecurityException" when your java code is trying to access classes in the "java.sql" package. This is very unusual. It may happen if the java code you posted is part of an applet, but since the java code you posted is a method named "main()", then it is probably not part of an applet. I, for one, am very confused.

Also, why are you creating a java stored procedure using JDBC? Is it not easier to use something like SQL*Plus or JDeveloper?

Also, you asked:

y is it giving "unknown source" in getConnection ?

Because the code for the "getConnection()" method was compiled without the "debug" (in other words "-g") flag, hence the JVM does not know which source line caused the error.

Let me put us both out of our misery. Please post the code for the "TestInternalConnection.getGreeting()" method, and I'll see if I can show you how to do it right.

Good Luck,
Avi.
SureshSam Rajiv
Greenhorn

Joined: May 04, 2005
Posts: 9
Hi all,

I think it would be better if i post the entire source code with which i tried..

First let me show the source code which i loaded using loadjava class.

second PublishTestInternalConnection is the one which i used to publish


third is the actual code where i am using callable statement to call the stored java procedure.


So pls help me out and i am doing this simply to learn about java stored procedures.

Thanks,
Suresh Babu
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1124

Suresh Babu,
[Are you any relation to Sam?]
You said:

I am doing this simply to learn about java stored procedures.

For learning about java stored procedures, allow me to recommend:
  • Java Stored Procedures Developer's Guide (available from the Tahiti Web site)
  • Java Stored Procedures Samples


  • But, as I promised, you showed me your code, so here is how you do it...

    First the java code for the "TestInternalConnection" class:

    Here is the SQL script for creating the PL/SQL wrapper for the java stored procedure:

    Here is the command to load the java class -- "TestInternalConnection" -- into the database:
    [Note that I run this command from the same directory where the class is located.]

    I use SQL*Plus to run the (SQL) script that creates the PL/SQL wrapper. (If you don't know how to do that, then read the "SQL*Plus User's Guide and Reference" -- also available from the Tahiti Web site.)

    Finally, I execute the GET_GREETING function:

    And for you, today, Babu, absolutely no charge
    SureshSam Rajiv
    Greenhorn

    Joined: May 04, 2005
    Posts: 9
    Hi Abrami,

    Thanks a lot . Sam is my nick name .

    Today i have no charge but see the fortune my server is down and i
    should wait for next day.

    Anyways the docs which you specified was very much useful.
    Thanks a lot man.

    Regards,
    Sam
    SureshSam Rajiv
    Greenhorn

    Joined: May 04, 2005
    Posts: 9
    Hi Abrami,

    I am able to execute the procedure without any errors but when i called it through a callable statement it is not retruning any values.

    Even i executed in SQL*Plus but its giving only null value.

    i am trying my best to solve it. Please help me out and i learned about Java Stored procedure to some extent.

    Thanks,
    Sam
    Avi Abrami
    Ranch Hand

    Joined: Oct 11, 2000
    Posts: 1124

    Sam,
    I gave you the code. I pointed you to the documentation and code samples. If that's not enough for you then if you want to pay me to come out and show you how to do it, that's fine by me.

    Good Luck,
    Avi.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Reg: Writing Oracle JavaStored Procedures