you seem to have a problem with a ; in the wrong place, would think it
could be the stored procedure, can you run the SQL in another format
and / or check the syntax ?
> -----Original Message-----
> From: chad_zeve@h... [mailto:chad_zeve@h...]
> Sent: 19 March 2001 03:32
> To: pro_jsp@p...
> Subject: [pro_jsp] Error wit Oracle
>
>
> Hi everyone,
>
> Can't seem to beat this error. Any help would be greatly appreciated!
>
> java.sql.SQLException: ORA-06550: line 1, column 48:
> PLS-oo103: Encountered the symbol ";" when expecting one of
> the following:
> .(),* @ % & | = - etc. etc.
> between is null is not || indicator is dangling
> The symbol ")" was substituted for ";" to continue.
>
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
> at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
> at oracle.jdbc.ttc7.Oa117.receive(Oa117.java:542)
> at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1311)
> at
> oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:738)
> at
> oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleState
> ment.java:1313)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatem
> ent.java:1232)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleSt
> atement.java:1353)
> at
> oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.j
> ava:1760)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimout(OracleS
> tatement.java:1805)
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(Oracl
> ePreparedStatement.java:322)
> at
> oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePrepa
> redStatement.java:366)
> at DbBean.getOutputValues(DbBean.java:68)
> at Vendor.getAVendor(Vendor.java:182)
> at testDriver.main(testDriver.java:49)
> Exception in thread "main"
>
> testpage.java
> --------------------------------------------------------------
> Vendor testVendor = new Vendor();
> testVendor.setDbType("O");
> testVendor.getAVendor(vendorNum);
>
> vendor.java
> --------------------------------------------------------------
> public void getAVendor(String vVendorNum) throws SQLException{
>
> DbBean oDb = new DbBean();
> String sp = "{call p77_GetAVendor(?,?,?,?,?,?,?,?,?}";
> String vendorNum = vVendorNum;
> int [] prmDirection = {1,2,2,2,2,2,2,2,2};
> int [] prmType = {1,3,3,3,3,3,3,3,3};
> String [] prmValue = new String[]
> {vendorNum,"","","","","","","",""};
> int prmCount = 9;
>
> Vector result =
> oDb.getOutputValues(sp,prmCount,prmDirection,prmType,prmValue,dbType);
> }
>
> DbBean.java
> --------------------------------------------------------------
> public Vector getOutputValues(String sp,
> int prmCount,
> int prmDirection[],
> int prmType[],
> String prmValue[],
> String dbSwitch)
> throws SQLException
> {
>
> Connection dbCon;
> int numOut = 0;
> if (dbSwitch == "O") // Oracle Connection
> dbCon =
> DriverManager.getConnection(dbURLOra,login,password);
> else // SQL Server Connection
> dbCon =
> DriverManager.getConnection(dbURLMS,login,password);
>
> CallableStatement cs = dbCon.prepareCall(sp);
> cs = buildCS(cs, prmCount, prmDirection, prmType,
> prmValue);
> cs.execute();
> //
> // Load output parameters in Vector for return
> //
> for (int i = 0;i<prmCount;i++) {
> if (prmDirection[i] == 2)
> numOut++;
> } // determine # of output parameters
>
> Vector retVals = new Vector(numOut);
>
> if (numOut == 1) {
> if (prmType[prmCount-1] == 5) {// Oracle Integer not
> getObject
> Integer I = new Integer(cs.getInt(prmCount));
> retVals.add(I);
> }
> else
> retVals.add(cs.getObject(prmCount));
> }
> else {
> for (int i = 1;i<numOut+1;i++) {
> if (prmType[prmCount-1] == 5) {// Oracle
> Integer not
> getObject
> Integer I = new
> Integer(cs.getInt(prmCount-numOut)+i);
> retVals.add(I);
> }
> else
> retVals.add(cs.getObject((prmCount-numOut)+i));
> }
> }
> dbCon.close();
> return retVals; //send back Vector to caller
> }
> --------------------------------further down in
> bean--------------------
>
> private CallableStatement buildCS( CallableStatement cs,
> int prmCount,
> int prmDirection[],
> int prmType[],
> String prmValue[]) throws
> SQLException {
>
>
> for (int i = 0;i<prmCount;i++) {
> if (prmDirection[i] == 1){ // input parameters
>
> switch (prmType[i]) {
> case 1:
> cs.setInt (i+1, Integer.parseInt(prmValue[i]));
> break;
> case 2:
> Float f = new Float(prmValue[i]);
> cs.setFloat (i+1, f.floatValue());
> break;
> case 3:
> cs.setString(i+1, prmValue[i]);
> break;
> }
>
> }
>
> if (prmDirection[i] == 2){ //output parameters
>
> switch (prmType[i]) {
>
> case 1:
> cs.registerOutParameter (i + 1,
> Types.INTEGER);
> break;
> case 2:
> cs.registerOutParameter (i + 1,
> Types.FLOAT);
> break;
> case 3:
> cs.registerOutParameter (i + 1,
> Types.VARCHAR);
> break;
> case 4:
> cs.registerOutParameter (1,
> OracleTypes.CURSOR);
> break;
> case 5:
> cs.registerOutParameter (i + 1,
> OracleTypes.INTEGER);
> break;
> }
> }
> }
> return cs;
> }
> }
>
> Stored Procedure
> --------------------------------------------------------------
> ----------
> CREATE OR REPLACE PROCEDURE P77_GetAVendor
> (
> id in integer,
> name out varchar2,
> address1 out varchar2,
> city out varchar2,
> province out varchar2,
> postalcode out varchar2,
> phone out varchar2,
> type out varchar2,
> email out varchar2
> )
> as
> begin
> select name,address1,city,province,
> postalcode,phone,type,email into
> name,address1,city,province,
> postalcode,phone,type,email
> from t77_vendor
> where vendorno = id;
> end;
> /
> --------------------------------------------------------------
> ---------
>
> Hope this isn't too lengthy. Any advice on how to run a
> stored procedure
> from within Oracle SQL*Plus would be a great help as well.
>
> Thanks for you time.
>
> Chad
View Cart
