Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Diff between Java Stored Procedure and Oracle Stored Procedure

 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Congratulations! Read the error message carefully; it tells you how to fix the problem.

Jules
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Julian,

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

Rgds,

Seetesh
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyone else who can help me out with how to handle SocketPermission
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is a SocketPermission Error?
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Has anyone used JDeveloper?

Rgds,

Seetesh
 
nilesh Katakkar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic