aspose file tools*
The moose likes JDBC and the fly likes Diff between Java Stored Procedure and Oracle Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Diff between Java Stored Procedure and Oracle Stored Procedure" Watch "Diff between Java Stored Procedure and Oracle Stored Procedure" New topic
Author

Diff between Java Stored Procedure and Oracle Stored Procedure

Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
What is the main difference between using Java Stored Procedure and Oracle Stored Procedure?

If I have to write 2 methods in a Java Stored Procedure java file, can one method access the other for fetching some values. If yes, how to call the same. In this case, I have to declare 2 Functions returning String values.

public class ScripReconciler
{
public static String getStringVal(String str_AccountCode)
{
String str_schemecode = "seetesh";;
System.out.println(str_schemecode);
return str_schemecode;
}

public static boolean mainmethod(String str_AccountCode)
{
String str_schemecode = getStringVal(str_AccountCode);
System.out.println(str_schemecode);
if (str_schemecode.length() > 0)
{
return true;
}
else
{
return false;
}
}
}

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
For a Java Stored Procedure written having code to connect to oracle database, I am getting the foll. error.

ERROR! Adding Employee: the Permission (java.net.SocketPermission bom019
resolve) has not been granted to SEETESH. The PL/SQL to grant this is
dbms_java.grant_permission( 'SEETESH', 'SYS:java.net.SocketPermission',
'bom019', 'resolve' )

where bom019 is the name of my database server.

Rgds,

Seetesh
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Congratulations! Read the error message carefully; it tells you how to fix the problem.

Jules
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi,

SQL>call dbms_java.grant_permission( 'SEETESH', 'SYS:java.net.SocketPermission',
'bom019', 'resolve' );

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.SecurityException: policy table update SYS:java.net.SocketPermission,
bom019

Tried that option too but no luck.

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Julian,

Have U worked on java Stored proc with Connection to Oracle database?

Rgds,

Seetesh
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Yes, and my advice would be not to touch them with the proverbial barge-pole (i.e. find another way if you have a choice). It was over 2 years ago and I'm afraid I don't remember enough details to be able to help. By the way, if you think you've added SocketPermission and it's still giving the same error, then clearly you haven't.

Jules
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Anyone else who can help me out with how to handle SocketPermission
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
What is a SocketPermission Error?
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Has anyone used JDeveloper?

Rgds,

Seetesh
nilesh Katakkar
Ranch Hand

Joined: Oct 27, 2004
Posts: 35
Main difference between Java Stored Proc and Oracle Stored Proc is that Oracle SP (PL/SQL) will give you better performance / maintainability for the SQL intesive tasks.

Java SP are good where PL/SQL isnt a natural choice.. that is doing things that are not SQL related. In oracle , when you create Java Proc you have to define a wrapper over it in PL/SQL. So there's an inherent context switch. Java datatypes need to be converted / reconverted between PL/SQL wrapper and Java SP. Plus JSP will result in more code. SQL statements in it arent really readable.ALL SQL is dynamic. PL/SQL can use static SQLs , implicit cursor, explicit cursors with minimum overhead wherever possible.


nilesh<br />neilindallas@hotmail.com
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29237
    
139

Seetesh,
I'm a little confused what JDeveloper has to do with this question. If you have a question about JDeveloper, the best place to ask it would be our IDEs forum.


[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
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
As far as JDeveloper is concerned, JDeveloper is Oracle's Visual Java Development Tool where we can upload a Java Stored Procedure as in case of Oracle 9i.

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Nilesh,

The code using java Stored procedure is portable across some of the databases supporting JSP.

Call to the JSP or Oracle SP is the same from any DAO accessing it as we are implementing MVC architecture in our project.

Dont u agree?

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Dear all,

I declare a method in the Java Stored Procedure as

public static void processInterest(int intYear, int intMonth, int intDay,
String strCreatedUserId,
short shtSSNOfficeCode,
String[] arrStrErrorCode,
String[] arrStrErrorDesc,
)
{
........
}

After loading this java file using the loadjav command, I run the CREATE OR REPLACE PROCEDURE processInterest(Param1 NUMBER, Param2 NUMBER,
Param3 NUMBER, Param4 VARCHAR2, Param5 NUMBER, Param6 OUT VARCHAR2, Param7 OUT VARCHAR2) AS LANGUAGE java
NAME 'com.epfindia.mem.dao.InterestProcessingSP.processInterest(int, int, int, java.lang.String, short,
java.lang.String[], java.lang.String[])';

on the sql prompt. The last 2 params are meant for fetching the OUT params for the same.

Currently we are setting/assigning some values to the String[] arrStrErrorCode, String[] arrStrErrorDesc in the Java Stored Procedure ie hardcode some values to the same object.

Problem : The value set is not returning to the calling class say DAO.

What cld be the problem?

Rgds,

Seetesh
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Seetesh,
You said (with spelling mistakes corrected):

What could be the problem?

The problem is that you cannot map "java.lang.String[]" to VARCHAR2.

For your information, the defined mappings are described in the "JDBC Developer's Guide and Reference" which is available from:

http://tahiti.oracle.com

By the way, it's been eight months since you started this topic. Are you saying that you still haven't resolved this issue? In case you don't know about them, perhaps these Web sites will be helpful:

http://otn.oracle.com

http://asktom.oracle.com

http://metalink.oracle.com

Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Diff between Java Stored Procedure and Oracle Stored Procedure
 
Similar Threads
PL/SQL and JDBC
Problem with stored procedure table parameter
Java Stored Procedure in SQL Server
Can I call a Java Stored Procedure from EJB
Need a disconnected resultset