aspose file tools*
The moose likes JDBC and the fly likes Two Store Procedure questions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Two Store Procedure questions" Watch "Two Store Procedure questions" New topic
Author

Two Store Procedure questions

Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 579
I am running a store procedure which is failing after a database upgrade. This has raised two questions. Since I am getting a java exception I would like to run the Stpre Procedure on the SQL pluus editor.

Problem is that the Store Procedure returns a CLOB as a out parameter.

I use the run the following code

DECLARE

clobType CLOB;

BEGIN
MISupdate.insertMISP('1004','123','AA123','original','EOY','Y','TPVS','111',
'2005-12-10','Microsoft','v101','NIRS','1034','Y','Consolidated','EDI','Indi
vidual','1100','28-jun-2004 12:22:13','1','1','106','30','28-jun-2004
12:23:13','28-jun-2004 12:23:13',clobType);
END;
/

which returns a wrong literal error message. Is this the correct way to call a Store Procedure with a CLOB as an out parameter.

The java exception is as follows:

Exception Class...:java.sql.SQLException
Exception Message.:Unsupported feature
Failing Class..... racle.jdbc.dbaccess.DBError
Failing Method....:throwSqlException
Failing File...... BError.java
Failing Line No...:134
Previous Exception:null
I am updating two timestamps could that be the problem.

Thanks for any help Tony
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Tony,
Since it appears you are using Oracle (though I could be wrong since it was not explicitly stated in your post), are you familiar with the Oracle JDBC samples:

http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/index.html

and "How-To" documents:

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/index.html

Good Luck,
Avi.
Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 579
Thanks Avi.

Thanks for the reply after further experimentation, I realised that I needed to pass in date and the two timestamps using the to_date and to_timestamp functions.

The other problem was resolved further investigation revealed that weblogic service pack 3 was not set up correctly causing the original error.

Tony
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Tony,
For your information, Oracle JDBC drivers support the SQL Escape Syntax (it's on that page, believe me, just scroll down :-)

Good Luck,
Avi.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Two Store Procedure questions