how to call a package in Oracle
I'm trying to call a package in oracle
(this is the oracle way of calling it :
begin pck$xl.get_year_low_high_to_date(:compid, 20010801 , 20020108 ",:year_low,:year_high,:year_average,:low_date,:high_date); end;)
but i can't get it right.
I'm new to package calling in Oracle from Java, till now i only worked with SQL statements without parameters of that kind, can someone help me with this?
ps the ":compid" is a parameter i have to fill in and provide...
thank you very much
[528 byte] By [
maesj] at [2007-9-27 16:54:12]
I'm assuming you already have some knowledge of JDBC, so I'll jump closer to the end.
Use a PreparedStatement (via Connection.prepareStatement) and replace your parameters (:compid, etc.) with a "?" in your statement. Then use the set methods (setInt, setLong, etc) to set the values.
If you have output paramters as well, then use a CallableStatement (via Connection.prepareCall) instead of a PreparedStatement and the get methods to retrieve the values.
ok here is a test i do...
but still doesn't work something about the bindvariables (check errorlog). I know a bit jdbc and i understant what you mean, but the problem is i don't know how this should go :(
please advise
import java.sql.*;
import java.util.*;
import java.text.*;
import java.io.*;
/**
*
* @author U97488
*/
public class test {
public static void main(String[] args) {
try{
ConnectionSherpa cs = ConnectionSherpa.getInstance("ConnectionSherpa");
Connection conn = cs.getConnObj();
String sSQL = "begin?; pck$xl.get_year_low_high_to_date(?, 20010801, 20020801 ,:year_low,:year_high,:year_average,:low_date,:high_date); end;";
CallableStatement stmt = conn.prepareCall(sSQL);
stmt.setString(1, "BRU10");
System.out.println(stmt.toString());
if(stmt.execute(sSQL)){
System.out.println(stmt.getString(2));
}
}catch (Exception e){
e.printStackTrace();
}
}
}
StackTrace:
java.sql.SQLException: ORA-06550: Regel 1, kolom 6:
PLS-00110: Bindvariabele '1' is niet toegestaan in deze context..
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1839)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1764)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2354)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:909)
at com.kbcsecurities.taminoupdate.servlets.test.main(test.java:28)
maesj at 2007-7-6 1:11:45 >
Hi,
try to remove the first "?" and ";" characters.
String sSQL = "begin pck$xl.get_year_low_high_to_date(?, 20010801, 20020801 ,:year_low,:year_high,:year_average,:low_date,:high_date); end;";
I am not so good at Java, but it seems that you are using two kinds
of bindvariables, "?" that I have seen in Java and ":" that is used
in for example PRO*C.
/Uffe
