SP call does not work

[Eugene A]

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:INSTANCE0 02";
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.

[Virgil Green]

"Eugene A" <lo*****@mailandnews.com> wrote in message
news:9f******************************@news.teranew s.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:INSTANCE0 02";
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

[Joe Weinstein]

Eugene A wrote:

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?
You missed one '(' in the SQL. It should be:

CallableStatement cs = con.prepareCall("{call schtru.sp_get_all_descr ( ?, ?, ?, ?}");


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:INSTANCE0 02";
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.

[Bjorn Abelli]

"Joe Weinstein" wrote...

You missed one '(' in the SQL. It should be:

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr ( ?, ?, ?, ?}");

Shouldn't that actually be "two" misses?
One in the end of the argument list as well... ;-)

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr (?, ?, ?, ?)}");
// Bjorn A

[Joe Weinstein]

Bjorn Abelli wrote:

"Joe Weinstein" wrote...

You missed one '(' in the SQL. It should be:

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr ( ?, ?, ?, ?}");

Shouldn't that actually be "two" misses?
One in the end of the argument list as well... ;-)

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr (?, ?, ?, ?)}");

Yep.

// Bjorn A

[Virgil Green]

"Joe Weinstein" <jo*******@bea.com> wrote in message
news:40**************@bea.com...

Bjorn Abelli wrote:

"Joe Weinstein" wrote...

You missed one '(' in the SQL. It should be:

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr ( ?, ?, ?, ?}");

Shouldn't that actually be "two" misses?
One in the end of the argument list as well... ;-)

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr (?, ?, ?, ?)}");

Yep.

Are these opening/closing parentheses required by Oracle? I never use them
with SQL Server. A review of the CallableStatement interface docs gives no
indication that the parentheses are needed.

- Virgil

[Bjorn Abelli]

"Virgil Green" wrote...

Bjorn Abelli wrote:

CallableStatement cs =
con.prepareCall
("{call schtru.sp_get_all_descr (?, ?, ?, ?)}");

Are these opening/closing parentheses required by Oracle?
Yes.

AFAIK, that's the standard syntax "inherited" from the similar constructions
in ODBC.
I never use them with SQL Server. A review of the CallableStatement
interface docs gives no indication that the parentheses are needed.

That's because it's up to the vendor how they have implemented it. If you
have an OracleConnection, the CallableStatement is actually an
OracleCallableStatement.

If you're familiar with PL/SQL in Oracle, you also know how local variables
are used. That syntax is also allowed for the SQL-string as an alternative
for the questionmarks.

http://database.sarang.net/database/.../jdbc_faq.html
// Bjorn A