Thursday, July 07, 2011

Syntax Error : Malformed SQL92

I got error

java.sql.SQLException: Malformed SQL92 string at position: 53

What is that? Not sure.Headache.Then I stare at the code..deeply

cStmt = con.prepareCall("call MYPACKAGE.MYPROCEDURE(?)}");

Nah..see the cause?
cStmt = con.prepareCall("call MYPACKAGE.MYPROCEDURE(?)}");

Now you see... the correct version should be like this.

cStmt = con.prepareCall("{call MYPACKAGE.MYPROCEDURE(?)}");

Simple '{' can make my life haywire.My clumsiness cannot be helped!

Package Error : ORA-06550 and PLS-00201

I called my package in Oracle through jdbc like this :

int num = 0;
CallableStatement cStmt = null;
cStmt = con.prepareCall("{call MYPACKAGE.MYPROCEDURE(?)}");
cStmt.registerOutParameter(1, OracleTypes.NUMERIC)
cStmt.execute();
num=cStmt.getInt(1);

Get exception like below :


java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPACKAGE.MYPROCEDURE' must be declared
After headache for hours, then I saw the light. CREATE SYNONYM,GRANT ACCESS for that package!


CREATE OR REPLACE PUBLIC SYNONYM MYPACKAGE FOR MYPACKAGE;
GRANT EXECUTE ON  MYPACKAGE TO USERNAME;
The error went pooofff  away. Always forget about basic thing!