Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    9

    Unanswered: Err calling proc: ORA-00900: Invalid SQL Stmt

    Hi
    I have the following code where I am calling a procedure:

    CallableStatement cstmt = null;
    cstmt = con.prepareCall( " { call DROP_COMMON_TBL ( ? , ? ) } " );
    cstmt.setString( 1, "A" );
    cstmt.setString( 2, "B" );
    boolean a = cstmt.execute();

    When I am excuting this call I am getting ORA-00900 error with following Stack trace:

    java.sql.SQLException: ORA-00900: invalid SQL statement

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1983)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:1141)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:2149)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:2032)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2894)
    at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:608)
    at oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStatement.java:684)
    at weblogic.jdbc.wrapper.PreparedStatement.execute(Pr eparedStatement.java:68)



    I am using Weblogic Server 8.1.
    The procesdure runs fine indeendently when tested.

    Any ideas why I am getting this error?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't speak Java, but - what happens if you substitute "call DROP_COMMON_TBL" with "execute DROP_COMMON_TBL"?

  3. #3
    Join Date
    Feb 2005
    Posts
    9
    no that doesnt work either

    java.sql.SQLException: Non supported SQL92 token at position: 10: execute
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava:1130)
    at oracle.jdbc.driver.OracleSql.handleToken(OracleSql .java:202)
    at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql. java:122)
    at oracle.jdbc.driver.OracleSql.parse(OracleSql.java: 70)
    at oracle.jdbc.driver.OracleConnection.nativeSQL(Orac leConnection.java:1232)
    at oracle.jdbc.driver.OracleStatement.expandSqlEscape s(OracleStatement.java:6689)
    at oracle.jdbc.driver.OracleStatement.parseSqlKind(Or acleStatement.java:6678)
    at oracle.jdbc.driver.OraclePreparedStatement.<init>( OraclePreparedStatement.java:152)
    at oracle.jdbc.driver.OracleCallableStatement.<init>( OracleCallableStatement.java:77)
    at oracle.jdbc.driver.OracleCallableStatement.<init>( OracleCallableStatement.java:48)
    at oracle.jdbc.driver.OracleConnection.privatePrepare Call(OracleConnection.java:1183)
    at oracle.jdbc.driver.OracleConnection.prepareCall(Or acleConnection.java:1038)
    at weblogic.jdbc.common.internal.ConnectionEnv.makeSt atement(ConnectionEnv.java:1017)
    at weblogic.jdbc.common.internal.ConnectionEnv.getCac hedStatement(ConnectionEnv.java:855)
    at weblogic.jdbc.common.internal.ConnectionEnv.getCac hedStatement(ConnectionEnv.java:794)
    at weblogic.jdbc.wrapper.Connection.prepareCall(Conne ction.java:357)
    at weblogic.jdbc.wrapper.JTSConnection.prepareCall(JT SConnection.java:459)

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Huh, that's even worse! Sorry, I can't help you about that ...

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What happens if you call that procedure from SQLplus? Does it complete all right?

  6. #6
    Join Date
    Feb 2005
    Posts
    9
    Ya we tried that too. It works purrrfectly fine.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    And what about that ?
    Code:
    cstmt = con.prepareCall( "BEGIN DROP_COMMON_TBL(? ,? ); END;" );
    HTH & Regards,

    RBARAER

  8. #8
    Join Date
    Feb 2005
    Posts
    9
    No. tried that too

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    And what error do you get that time ?

    BTW, could you please provide your JDBC Driver version, and how you create your Connection object ?

    Regards,

    RBARAER
    Last edited by RBARAER; 02-23-05 at 06:14.

  10. #10
    Join Date
    Feb 2005
    Posts
    9
    See the issue is somewhere else...
    Because instead of proc call if i just fire a SQL stmt like:
    "DROP TABLE TBL1"
    then it works very fine.

    Also if I call a procedure for Creating a Table TBL2, then too it wrks absolutely fine.

    But what I dont understand is why there is a problem with this particular DropTBL() procedure.

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK, so what is the error and stack trace when using BEGIN END ?

    Does the user that connects through the java program have the right to execute this procedure ?

    Does this user "see" this procedure when called without schema ? (ie is he the owner of this procedure, or is there a synonym on it ?)

    HTH & Regards,

    RBARAER

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Privs acquired via ROLES work as desired in SQL*Plus.
    Privs "acquired" via roles do NOT apply to/within PL/SQL procedures.
    GRANTs must be explicitly issued to the USER who invokes the procedure(s).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Feb 2005
    Posts
    9
    The stack trace for BEGIN ... END;

    java.sql.SQLException: Non supported SQL92 token at position: 8: BEGIN
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava:1130)
    at oracle.jdbc.driver.OracleSql.handleToken(OracleSql .java:202)
    at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql. java:122)
    at oracle.jdbc.driver.OracleSql.parse(OracleSql.java: 70)
    at oracle.jdbc.driver.OracleConnection.nativeSQL(Orac leConnection.java:1232)
    at oracle.jdbc.driver.OracleStatement.expandSqlEscape s(OracleStatement.java:6689)
    at oracle.jdbc.driver.OracleStatement.parseSqlKind(Or acleStatement.java:6678)
    at oracle.jdbc.driver.OraclePreparedStatement.<init>( OraclePreparedStatement.java:152)
    at oracle.jdbc.driver.OracleCallableStatement.<init>( OracleCallableStatement.java:77)
    at oracle.jdbc.driver.OracleCallableStatement.<init>( OracleCallableStatement.java:48)
    at oracle.jdbc.driver.OracleConnection.privatePrepare Call(OracleConnection.java:1183)
    at oracle.jdbc.driver.OracleConnection.prepareCall(Or acleConnection.java:1038)
    at weblogic.jdbc.common.internal.ConnectionEnv.makeSt atement(ConnectionEnv.java:1017)
    at weblogic.jdbc.common.internal.ConnectionEnv.getCac hedStatement(ConnectionEnv.java:855)
    at weblogic.jdbc.common.internal.ConnectionEnv.getCac hedStatement(ConnectionEnv.java:794)
    at weblogic.jdbc.wrapper.Connection.prepareCall(Conne ction.java:357)
    at weblogic.jdbc.wrapper.JTSConnection.prepareCall(JT SConnection.java:459)

  14. #14
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK, for BEGIN END and EXECUTE, I think it comes from your JDBC driver. If you used "$ORACLE_HOME/jdbc/lib/ojdbc14.jar", these syntaxes would work.

    Apart from that, if you say that your first syntax works for other procedures but not this one, then your problem may come from user rights. As anacedent said, check if the user you use to connect to Oracle has been explicitly granted EXECUTE privilege on this procedure, not via a role.

    However, I think that you would have had another error if this was the case...

    This "Invalid SQL Statement" error is strange since your syntax seems correct.

    Strange...

    RBARAER

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE DROP TABLE TBL1;
    must be done from inside a PL/SQL procedure
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •