SP call does not work
Date: Fri, 21 May 2004 20:20:09 GMT
Message-ID: <9f8b77f82e987f9e1fba10a652b3211b_at_news.teranews.com>
Hello.
In this call from a Java prog to a Oracle SP I am trying to pass along some varchars and a cursor, but apperently the syntax of the call to the stor proc is incorrect. What am I doing wrong?
Thanks,
Eugene.
/****************************** PL\SQL Stored Proc. *************************/
PROCEDURE sp_get_all_descr
( PV_dm_c IN VARCHAR2, PV_dm_x IN VARCHAR2, pv_flag IN VARCHAR2,
CURR OUT RESULTSETCURSORPKG.RC )
IS
BEGIN
IF pv_flag = 'L' THEN
V_LIKE_STR := PV_dm_x || '%';
OPEN CURR FOR SELECT dm_c, dm_x FROM dm_row_ss WHERE dm_c = PV_dm_c AND dm_x LIKE V_LIKE_STR;
ELSIF pv_flag = 'E' THEN
V_LIKE_STR := PV_dm_x;
OPEN CURR FOR SELECT dm_c, dm_x FROM dm_row_ss WHERE dm_c = PV_dm_c AND dm_x = V_LIKE_STR;
END IF;
END sp_get_all_descr;
/***************************************************************************
*/
/*************************** Java Appl***************************************/ import java.sql.*; import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleTypes;
public class callsp
{
public static void getData() {
try
{
final String driverClass = "oracle.jdbc.driver.OracleDriver";
final String connectionURL =
"jdbc:oracle:thin:_at_hostname.com:1521:INSTANCE002";
final String userID = "user"; final String userPassword = "passwd"; Connection con = null; String var1 = "PWS";
String var2 = "MF31";
String var3 = "E";
Class.forName(driverClass).newInstance();
System.out.print("---------------------------------------" + "\n"); System.out.print(" Connecting to -> " + connectionURL + "\n"); System.out.print("---------------------------------------" + "\n"); con = DriverManager.getConnection(connectionURL, userID, userPassword); CallableStatement cs = con.prepareCall("{call schtru.sp_get_all_descr ?, ?, ?, ?}"); cs.setString(1, var1); cs.setString(2, var2); cs.setString(3, var2); cs.registerOutParameter(1, Types.VARCHAR); cs.registerOutParameter(2, Types.VARCHAR); cs.registerOutParameter(3, Types.VARCHAR); cs.registerOutParameter(4, OracleTypes.CURSOR);cs.execute();
ResultSet rs = (ResultSet) cs.getResultSet();
while (rs.next()) { String s = rs.getString(1); System.out.println(s + " pounds of " + s + " sold to date."); }
}
catch(Exception e)
{
System.out.println(" Exception is "+ e); }
}
public static void main(String args[])
{
getData();
}
}
/***************************************************************************
*/
Exception is java.sql.SQLException: ORA-06550: line 1, column 41:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( _at_ % ;