stored procedure « oracle « Java Database Q&A





1. Confusion over class resolution in Oracle java stored procedures    stackoverflow.com

I'm trying to use a third-party java library within oracle. The library seems compatible with the same 1.4 version of the jvm that our Oracle 10g server hosts, since it ...

2. How can I open a file using java stored procedure    stackoverflow.com

I am using oracle database 10g. I have to write a java stroed procedure through which i can call a file at the server and store it locally at the users ...

3. How to load Java Stored Procedure through JDBC into Oracle 10g?    stackoverflow.com

I'm trying to load some java stored procedures into an Oracle 10g database through JDBC. The statement I'm executing is -

CREATE OR REPLACE JAVA SOURCE NAMED "test.Test" AS
package test;
public class Test ...

4. Oracle Java Stored Procedure Command-line Interaction    stackoverflow.com

I have a funny requirement. We need to have a command-line interaction in a Java Stored Procedure. In spite of granting appropriate permissions using the dbms_java.grant_permission commands, I am encountering java.io.IOException, where ...

5. java - passing array in oracle stored procedure    stackoverflow.com

I have a Java app accessing an oracle stored procedure. The arguments to the stored procedure include an array type. I do it like the following...

con = this._getConnection();  
Connection narrowdConn ...

6. Can I update the result (sys_refcursor) of an Oracle stored procedure (in Java)?    stackoverflow.com

Given the following function:

create or replace FUNCTION "GETADDRESSES"
    RETURN sys_refcursor
IS
    address_cursor sys_refcursor;
BEGIN
    OPEN address_cursor FOR 
       ...

7. How do I get output into Java from a SELECT stored procedure in Oracle?    stackoverflow.com

I'm using Java to connect to an Oracle 10 Database. I want to create a stored procedure (don't ask why) that takes no arguments and returns a lot of rows. Specifically, in Java ...

8. Passing ArrayList to Oracle Stored Procedure in Java    stackoverflow.com

Is there a way to pass a Java ArrayList object as a parameter to an Oracle Stored Procedure? I have seen examples of passing an Array object to an Oracle ...

9. How to call oracle stored procedure which include user-defined type in java?    stackoverflow.com

In Oracle DB:

Store Procedure like:
procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor )
Type IDS_TABLE is:
create or replace type IDS_TABLE as table of IDS    

create or replace type ...





10. Calling Oracle stored procedure from Java    stackoverflow.com

I'm calling an Oracle stored function using JDBC thin driver. Following is the code.

class testSP 
{ 
  public static void main (String args []) 
      ...

11. Can an Oracle stored procedure return an object useable in Java?    stackoverflow.com

I am working with a PL/SQL developer on a project. The other developer is writing the stored procedures I need to call. One of the stored procedures needs to return everything ...

12. Printing from Oracle Java Stored Procedure    stackoverflow.com

I'm trying to solve a problem where I need to initiate printing from an Oracle database server through a Java stored procedure, and seem to be one step away from a ...

13. EclipseLink - Oracle Stored Procedure call newbie problem    stackoverflow.com

I'm in charge of a task which I believe should be simple, but as I never did it before I have some trouble with it. I have succesfully created an EJB ...

14. Java stored procedure in Oracle database: java session lifecycle    stackoverflow.com

In oracle we can define java procedure like:

class Proc {
    private static int counter = 0;
    public static int incrementAndGetCounter() {
     ...

15. How to debug Java Stored Procedures in Oracle    stackoverflow.com

I have an Oracle Db with stored java procedures, which I load new procedures here and then. I would like to be able to debug these java procedures, with a same debug ...

16. Passing arabic parameters to oracle stored procedure    stackoverflow.com

I'm trying to pass arabic input as parameters to Oracle stored procedure from Java to make search on it. but there is no results, but if i called the procedure in SQL Developer ...





17. Having trouble calling Oracle stored procedure in java    stackoverflow.com

I'm having trouble calling an Oracle stored procedure in Java. I added a stored procedure to the database like this:

         String SQL = ...

18. Access external lib from oracle stored procedures    stackoverflow.com

How do we refer to external libraries in oracle stored procedure? I did this.

loadjava -jarasresource -user apps/xxx@instance sqljdbc.jar
(I am loading the lib required for jdbc connection to sql server 2000,) now, in my ...

19. passing data from Oracle stored procedures to Java    stackoverflow.com

We're going to write a new web interface for a big system based on Oracle database. All business rules are already coded in PL/SQL stored procedures and we'd like to reuse ...

20. how to write a java stored procedure?    stackoverflow.com

How to write a java stored procedure in oracle? What are the advantages of java stored procedures over pl/sql stored procedures? Thanks

21. First call to a stored procedure, after successful compilation, fails. Oracle 10g    stackoverflow.com

I have an Oracle 10G installation (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod), and a java web app that calls stored procedures and functions in oracle via a JDBC ...

22. Oracle stored procedures    coderanch.com

23. Passing Arrays to Oracle stored procedures    coderanch.com

Hi, Am trying to do the same operation - i.e. Passing an array of strings (single dimension array) to an oracle stored proc. I also get a ClassCastException when I create the ArrayDescriptor object.. The application is running on JRUN and the connection is obtained from a connection pool using DBCP connection pooling resources(org.apache.commons.dbcp.PoolableConnection). I know that a bug exists in ...

25. How to call an Oracle Stored Procedure from CMD file?    coderanch.com

Hi, I have an Oracle Stored Procedure(SP) , I want to schedule win NT to call the SP at certain time interval. I heard I have ti write a .cmd file to do that. How can I call oracle SP from .cmd file. The SP is like a batch process, neither takes input parameter nor returns. Asha

26. Moving From Oracle Stored Procedure to Java Stored Procedure    coderanch.com

Hi, We have been using Oracle Stored Procedures for a while and would like to switch to Java Stored Procedures. Is there a tool that I can use to port Oracle Stored Procedures to Java Stored Procdures? If I end up in doing the porting manually how much time it takes for the porting ( I would like to know approximately ...

28. Passing an integer array to a Oracle Stored Procedure    coderanch.com

Hi, I have a pecuilar problem. I need to pass an array of integers to an oracle stored procedure so that I can loop through every integer in the array and update fields in a table (whose primary keys are present in the array). Is it possible? If yes, can someone give a sample code(JDBC as well as stored procedure end) ...

29. URGENT Java Stored procedures on oracle    coderanch.com

30. Oracle 8I Java Stored Procedures returning a result Set    coderanch.com

Hi, Does anyone know if it is possible to write a Java Stored Procedure in Oracle 8i to return a Result Set. I have read that this is not possible in 8i as does not have any Data Type in the Oracle.sql package to map to a REF CUROSR. My understanding is that the oracle.sql package provides wrappers for the SQL ...

33. Oracle Stored Procedure Problem...    coderanch.com

I am using an oracle driver to connect to Oracle8.1.7 database. I am able to connect to a table, but when I try to connect to stored procedure, it always throws error. The code that I am using to connect to table is DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); con = DriverManager.getConnection("jdbc racle:thin:@sfddb:1527 EV", "ID", "PWD"); This code works to connect to table : Statement ...

34. Oracle Java Stored Procedure ... Urgent help needed.    coderanch.com

Hi, How do we get the result set from an oracle stored procedure in Java? We are using Oracle 8.1.7. I am able to get the out parameters by passing some in parameters. But the result set is always returning null. Is there any specific way to create stored procedure in Oracle for same purpose? I am using oracle thin driver. ...

36. Oracle Stored Procedure Problem    coderanch.com

Hi I am, trying to run a oracle 9.i stored procedure from jboss It is giving me the following error, Can any one suggest in what direction should I look for solving this problem Thank you. My Stored procedure : ( 33 parameters ) "{call InsCustTxnLookupDtls(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"; Argument List: /* CREATE OR REPLACE PROCEDURE InsCustTxnLookupDtls( 1 i_session_id IN VARCHAR2, 2 i_cust_session_id ...

37. Oracle Stored Procedure    coderanch.com

App Server : Weblogic 6.1 on Unix DataBase : Oracle 9i on Unix when i call a oracle stored procedure from session bean i get a n array of results from the procedure. But the array has calues in heya decimal. can anyone say does this occur. ? I have set nlscharset.zip and classes12.zip in classpath

38. Diff between Java Stored Procedure and Oracle Stored Procedure    coderanch.com

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 ...

39. Oracle stored procedure trouble    coderanch.com

40. Calling Oracle stored procedures in JRun container    coderanch.com

I'm trying to call stored procedures that exist in an Oracle database. My app is a web-app running in the JRun 4 container. When I try to call the stored procedure an exception is thrown saying the stored procedure does not exist. CallableStatement cs = dbConnection.prepareCall("{call MY_PACKAGE.MY_PROC(?,?)}"); cs.setString(1, "foo"); cs.registerOutParameter(2,java.sql.Types.INTEGER); dbResultSet = cs.executeQuery(); My Stored procedure's signature looks like this PROCEDURE ...

41. Java & Oracle Stored Procedures    coderanch.com

Hi, What is the difference between Java Stored Procedure and Oracle Stored procedures? Stored procedures are group of sql statement that will be called withnin a program. Am I right? How can we use either Oracle Stored procedures or Java Stored Procedures. Any sample program or site that having sample coding will be useful to begin with for beginners like me!! ...

44. passing 2D arrays to Oracle Stored Procedure with/without using JPublisher    coderanch.com

Our application is J2EE based with Oracle 8i as back end. The database logic resides in the Oracle Stored Procedures & our Java database classes invoke these stored procedures for the datbase operations. We have a requirement where we are supposed to pass a 2D array from Java to the Oracle procedure. We have no problems passing a 1D array through ...

45. Stored procedure in oracle problem    coderanch.com

Hi everyone, i have a problem with my java code wich calls a store proc. from oracle. I check it and everything seems fine although i get a SQLException Invalid column index. I dont know what else to do, please help me out. The following is my code: ************************************************** ************* public class OracleConn { public static void main(String args[]){ try{ OracleConn ...

47. Problem while accesing collection from stored procedure using Oracle10g    coderanch.com

java.sql.SQLException: Internal Error at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :208) at oracle.jdbc.oracore.OracleTypeCOLLECTION.initCollElemTypeName(OracleT ypeCOLLECTION.java:1074) at oracle.jdbc.oracore.OracleTypeCOLLECTION.getAttributeType(OracleTypeC OLLECTION.java:1107) at oracle.jdbc.oracore.OracleNamedType.getFullName(OracleNamedType.java: 81) at oracle.jdbc.oracore.OracleNamedType.getFullName(OracleNamedType.java: 68) at oracle.sql.TypeDescriptor.initSQLName(TypeDescriptor.java:238) at oracle.sql.TypeDescriptor.getName(TypeDescriptor.java:199) at oracle.sql.StructDescriptor.getClass(StructDescriptor.java:1105) at oracle.sql.STRUCT.toJdbc(STRUCT.java:575) at oracle.jdbc.oracore.OracleTypeUPT.unpickle81UPT(OracleTypeUPT.java:50 2) at oracle.jdbc.oracore.OracleTypeUPT.unpickle81rec(OracleTypeUPT.java:45 6) at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBodyElements(Or acleTypeCOLLECTION.java:1011) at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBody(OracleType COLLECTION.java:952) at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81(OracleTypeCOLLECT ION.java:764) at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearizeInternal(OracleTy peCOLLECTION.java:243) at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearize(OracleTypeCOLLEC TION.java:217) at oracle.sql.ArrayDescriptor.toJavaArray(ArrayDescriptor.java:891) at oracle.sql.ARRAY.getArray(ARRAY.java:315) at weblogic.jdbc.wrapper.Array_oracle_sql_ARRAY.getArray(Unknown Source) at ...

48. Can we call java program from a Oracle stored procedure?    coderanch.com

While I'm sure there might be a trick to do it, off hand, it wouldn't be available. Its bad enough to see the content of SQL stored procedures in java, but visa versa would lead to some really difficult to maintain code. You'd essentially be writing code no one could update. [ August 05, 2008: Message edited by: Scott Selikoff ] ...

50. Passing in array parameter to an Oracle stored procedure    coderanch.com

I've searched the forum first for what I need before creating this post. I've found some similar posts but without satisfactory solutions. So please bear with me for bringing this up again. I need to pass an array of Java objects to a callable statement to execute an Oracle stored procedure. I use Oracle 10g and was able to write the ...

51. Oracle Java Stored Procedures    coderanch.com

I was recently looking to migrate/rewrite one of our legacy app developed in VB6 and Oracle 9i. I noticed this below mentioned stored procedures and few other like that, which used "AS LANGUAGE JAVA". I am a mid level java developer but never used this before. Can you help me understand why this is used and is there any benefits using ...

52. Calling Oracle Stored Procedure    coderanch.com

java.sql.Connection conn = null; java.sql.CallableStatement cs = null; String storedProc = "call SCHEMA.MIKE_REPORT.LAB_ORG_LISTING( ?, ? , ?, ?)"; ResultSet rs = null; try { conn = OracleDAOFactory.getConnection(); cs = conn.prepareCall(storedProc); cs.registerOutParameter(1, java.sql.Types.VARCHAR); cs.setString(2, "2070=2071"); // the proc separates these cs.setString(3, "USER"); cs.setString(4, "ParameterText"); rs = cs.executeQuery(); while (rs.next()) { // processing result set, never get here } } catch (SQLException sqle) ...

53. Passing an ARRAY object to Oracle Stored Procedure    dbforums.com

Hi All, I have a packaged procedure which has the parameters as, PROCEDURE process_adjustment (p_adj IN NUMBER,p_adjustmentvalue IN adjustment_value, p_doubleentry_array IN DOUBLE_ENTRY_ARRAY) DOUBLE_ENTRY_ARRAY is declared as, CREATE OR REPLACE type DOUBLE_ENTRY_ARRAY as table of DOUBLEENTRY_VALUE DOUBLEENTRY_VALUE is defined as Point is that i have to call the packaged procedure process_adjustment from java, I am using the oracle.sql.ARRAY implementation. Java code: connection=ejbGlossary.getOracleConnection(); ...

54. Java stored procedure execution freeze on Oracle 8.1.7.4 HP-UX only with OPS.    dbforums.com

Hello. I'm having a very strange problem using java stored procedure (JSP) with RMI on Oracle 8.1.7.4 with OPS running on a HP-UX 11. My JSP communicates through RMI with a remote object (in the same machine) to perform some action. However, a strange situation takes place when I try to execute this procedure for the first time on an instance, ...

55. Problem calling Oracle stored procedure (bug?)    java-forums.org

Hello everybody, I am trying to call a stored procedure Oracle on Oracle 10g XE from Java. I can succesfully execute query from Java, so I can say that driver, connection, ... are all right. I have the following error when I try to call a stored procedure or a function. I think that it is a bug of the file ...

57. is it recommended to called oracle stored function and procedures from page    forums.oracle.com

I have a question if it is recommended to call oracle function and procedures from JSP pages , for example if i want to check the user name and password for the use who want to login to my system , can i do so by calling the oracle procedure who do the validation or it is recommended to write such ...

58. Problem passign IN parameters to Oracle Stored Procedure from Java method    forums.oracle.com

I get a valid result. The IN parameters passed to the method seem to be fine because I modified the stored procedure a few times and assigned the values of the IN parameters directy to the OUT parameters and what I retrieved was what I had passed. Also when I substitute the values of the IN parameters directly in the query, ...

60. ORACLE - Java stored procedures and O9208    forums.oracle.com