Re: SP call does not work

From: Virgil Green <vjg_at_obsydian.com>
Date: Fri, 21 May 2004 22:37:42 GMT
Message-ID: <Wwvrc.10680$Kq1.9028@newssvr24.news.prodigy.com>

"Eugene A" <lorus77_at_mailandnews.com> wrote in message news: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?
>
> 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);

I wouldn't register the first three parameters as output parameters since your sp only defines them as input parameters.

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

This would seem to indicate that the error is in the stored procedure itself. However, I don't see a single " in the sp, so it's hard to say. Have you executed the sp from within oracle itself?

• Virgil

Received on Fri May 21 2004 - 17:37:42 CDT