SP call does not work

From: Eugene A <lorus77_at_mailandnews.com>
Date: Fri, 21 May 2004 20:20:09 GMT
Message-ID: <9f8b77f82e987f9e1fba10a652b3211b@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?

Please help.

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:@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:

   := . ( @ % ;
The symbol ":=" was substituted for "" to continue. Received on Fri May 21 2004 - 15:20:09 CDT