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. 6 4683
"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
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.
"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
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
"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
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ¤ Alias |
last post by:
I have a function named
getID3info (lvwDiscInfo.SelectedItem).
What is the difference between
getID3info (lvwDiscInfo.SelectedItem)
and
Call getID3info(lvwDiscInfo.SelectedItem) ?
|
by: Hubert Baumeister |
last post by:
Fifth International Conference on
eXtreme Programming and Agile Processes
in Software Engineering
XP2004
June 6-10, 2004, Garmisch-Partenkirchen, Germany
http://www.xp2004.org/
|
by: Sue |
last post by:
After finishing up my first quarter JavaScript on 12/12/03, I decided
to improve character checking on my project. In my project I only had
to do very basic validation. Therefore, I only had one...
|
by: ip4ram |
last post by:
I used to work with C and have a set of libraries which allocate
multi-dimensional arrays(2 and 3) with single malloc call.
data_type **myarray
=...
|
by: Randell D. |
last post by:
Folks,
I'm sure this can be done legally, and not thru tricks of the trade - I
hope someone can help.
I'm writing a 'tool' (a function) which can be used generically in any
of my projects. ...
|
by: Bern McCarty |
last post by:
I have run an experiment to try to learn some things about floating point
performance in managed C++. I am using Visual Studio
2003. I was hoping to get a feel for whether or not it would make...
|
by: Kurt Van Campenhout |
last post by:
Hi,
I am trying to get/set Terminal server information in the active directory
on a windows 2000 domain. Since the ADSI calls for TS don't work until W2K3,
I need to do it myself.
I'm fairly...
|
by: |
last post by:
Is it possible to have just a multithreaded sub procedure? What I need is a
timer time_elapsed event (2 sec interval) send params to a sub that is
multithreaded. I have a COM component used to...
|
by: darthghandi |
last post by:
I've created a class to listen to all interfaces and do a
BeginAccept(). Once it gets a connection, it passes the connected
socket off and stores it in a List. Next, it continues to listen for...
|
by: HolyShea |
last post by:
All,
Not sure if this is possible or not - I've created a class which
performs an asynchronous operation and provides notification when the
operation is complete. I'd like the notification to be...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |