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_desc r
( PV_dm_c IN VARCHAR2,
PV_dm_x IN VARCHAR2,
pv_flag IN VARCHAR2,
CURR OUT RESULTSETCURSOR PKG.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_desc r;
/*************** *************** *************** *************** ***************
*/
/*************** ************ Java
Appl*********** *************** *************/
import java.sql.*;
import java.sql.Driver Manager;
import java.sql.Connec tion;
import java.sql.Types;
import java.sql.Callab leStatement;
import java.sql.Result Set;
import java.sql.SQLExc eption;
import oracle.jdbc.dri ver.OracleTypes ;
public class callsp
{
public static void getData() {
try
{
final String driverClass = "oracle.jdbc.dr iver.OracleDriv er";
final String connectionURL =
"jdbc:oracle:th in:@hostname.co m:1521:INSTANCE 002";
final String userID = "user";
final String userPassword = "passwd";
Connection con = null;
String var1 = "PWS";
String var2 = "MF31";
String var3 = "E";
Class.forName(d riverClass).new Instance();
System.out.prin t("---------------------------------------" + "\n");
System.out.prin t(" Connecting to -> " + connectionURL + "\n");
System.out.prin t("---------------------------------------" + "\n");
con = DriverManager.g etConnection(co nnectionURL, userID, userPassword);
CallableStateme nt cs = con.prepareCall ("{call
schtru.sp_get_a ll_descr ?, ?, ?, ?}");
cs.setString(1, var1);
cs.setString(2, var2);
cs.setString(3, var2);
cs.registerOutP arameter(1, Types.VARCHAR);
cs.registerOutP arameter(2, Types.VARCHAR);
cs.registerOutP arameter(3, Types.VARCHAR);
cs.registerOutP arameter(4, OracleTypes.CUR SOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getResultSet ();
while (rs.next()) {
String s = rs.getString(1) ;
System.out.prin tln(s + " pounds of " + s + " sold to date.");
}
}
catch(Exception e)
{
System.out.prin tln(" Exception is "+ e);
}
}
public static void main(String args[])
{
getData();
}
}
/*************** *************** *************** *************** ***************
*/
Exception is java.sql.SQLExc eption: ORA-06550: line 1, column 41:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "" to continue.
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_desc r
( PV_dm_c IN VARCHAR2,
PV_dm_x IN VARCHAR2,
pv_flag IN VARCHAR2,
CURR OUT RESULTSETCURSOR PKG.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_desc r;
/*************** *************** *************** *************** ***************
*/
/*************** ************ Java
Appl*********** *************** *************/
import java.sql.*;
import java.sql.Driver Manager;
import java.sql.Connec tion;
import java.sql.Types;
import java.sql.Callab leStatement;
import java.sql.Result Set;
import java.sql.SQLExc eption;
import oracle.jdbc.dri ver.OracleTypes ;
public class callsp
{
public static void getData() {
try
{
final String driverClass = "oracle.jdbc.dr iver.OracleDriv er";
final String connectionURL =
"jdbc:oracle:th in:@hostname.co m:1521:INSTANCE 002";
final String userID = "user";
final String userPassword = "passwd";
Connection con = null;
String var1 = "PWS";
String var2 = "MF31";
String var3 = "E";
Class.forName(d riverClass).new Instance();
System.out.prin t("---------------------------------------" + "\n");
System.out.prin t(" Connecting to -> " + connectionURL + "\n");
System.out.prin t("---------------------------------------" + "\n");
con = DriverManager.g etConnection(co nnectionURL, userID, userPassword);
CallableStateme nt cs = con.prepareCall ("{call
schtru.sp_get_a ll_descr ?, ?, ?, ?}");
cs.setString(1, var1);
cs.setString(2, var2);
cs.setString(3, var2);
cs.registerOutP arameter(1, Types.VARCHAR);
cs.registerOutP arameter(2, Types.VARCHAR);
cs.registerOutP arameter(3, Types.VARCHAR);
cs.registerOutP arameter(4, OracleTypes.CUR SOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getResultSet ();
while (rs.next()) {
String s = rs.getString(1) ;
System.out.prin tln(s + " pounds of " + s + " sold to date.");
}
}
catch(Exception e)
{
System.out.prin tln(" Exception is "+ e);
}
}
public static void main(String args[])
{
getData();
}
}
/*************** *************** *************** *************** ***************
*/
Exception is java.sql.SQLExc eption: ORA-06550: line 1, column 41:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "" to continue.
Comment