• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Reg: Writing Oracle JavaStored Procedures

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
SureshSam Rajiv
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Shailesh! I added the code tags you referred to.
 
SureshSam Rajiv
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 9
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 9
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 1143
    1
    Eclipse IDE Oracle Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic