Two Store Procedure questions

[Tony Evans]

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]

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]

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]

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.