Re: GATHER_SCHEMA_STATS from Java program

"Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message news:1112261004.620064.98030_at_f14g2000cwb.googlegroups.com...
>
> 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
>

Thank you Rauf

I managed to get it working. Works fine now. Well at least on 10g...Still have to try it on 9i

Christophe Received on Thu Mar 31 2005 - 08:56:10 CST