Re: GATHER_SCHEMA_STATS from Java program

Christophe Bonte wrote:
> Hello
>
> I don't have much experience in stored procedures. I'm having some
trouble.
> I'm creating a java program that would compute statistics from the
current
> schema in the Oracle DB.
>
> Here's the main code below
> ***********************************
> try {
> CallableStatement stmt = null;
> Connection con = null;
> String oracleUser = "CYBERLAB";
> con = ConnectionManager.getConnection();
> if (con == null){
> throw new MipsNoConnectionAvailableException("No Connection",
> "Statistics.java");
> }
>
> stmt = con.prepareCall("begin ? :=

dbms_stats.gather_schema_stats(?,?);
> end;");
> stmt.registerOutParameter(1, OracleTypes.CURSOR);
> stmt.setString(2, "ownname=> '" + oracleUser + "'");
> stmt.setString(3, "cascade=> TRUE");
> //stmt.setString("ownname", oracleUser);
> //stmt.setBoolean("cascade", true);
> stmt.execute();
>
> } catch (SQLException e) {
> ........
> }
> ***************************
>
> When I execute this I get the error
>
> 2005-03-31 09:26:59 ** - ** - ** New JDBC connection needed: driver
name and
> version: Oracle JDBC driver 10.1.0.2.0, URL:
> jdbc:oracle:oci:@cyber10g_CYBERBASE
> java.sql.SQLException: SQLException ORA-06550: line 1, column 13:
> PLS-00306: wrong number or types of arguments in call to
> 'GATHER_SCHEMA_STATS'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> Does anyone have any idea what I'm doing wrong? Thanks in advance.

PLS-00306 is self explanatory. Check the IN/OUT parameters and types that you are passing to dbms_stats.gather_schema_stats procedure. You are incorrectly registering 1st param as OUT plus you are incorrectly setting the other parameters. Remember... this procedure is overloaded and has slightly changed from 9i to 10g i.e. there is no OUT parameter in 10g version.

My suggestion... goto http://tahiti.oracle.com and lookup definition for dbms_stats package. Look at both overloaded procedures. Create your own PLSQL wrapper around this call which may only have 1 or 2 parameters then call that from your java procedure.

Regards
/Rauf Received on Thu Mar 31 2005 - 03:23:24 CST