Re: Problem deleting records in Oracle 9i release 2 JDBC driver with JDK 1.4

From: Judge <judge321_at_hotmail.com>
Date: 19 Sep 2003 01:25:28 -0700
Message-ID: <525c7692.0309190025.79636806@posting.google.com>

it still doesn't work. I put the statement in to the stored procedure. The strange thing again here is that when I execute that stored procedure from the SQL Plus the records that i wanted to delete gets deleted. However, if I call that stored procedure from my Java program, the records don't get deleted and I got no errors either. Strange... Is there any solution to this?

Below are my Stored procedure and the java method that execute the stored procedure.

    PROCEDURE delete(p_seconds NUMBER)
    IS
    BEGIN

      DELETE FROM deleted
      WHERE (SYSTIMESTAMP - time_stamp) > NUMTODSINTERVAL(p_seconds,
'second')
      AND change_indicator <= 0;
    EXCEPTION
      WHEN OTHERS
      THEN
          RAISE_APPLICATION_ERROR(-20102, 'delete: the record does not

exist', TRUE);

    END delete;

Java program:

public int delete(int seconds)

            throws SQLException
    {

        int count;
        CallableStatement cs = null;
        String sql = "{CALL deleted_indicator.delete(?)}";
        try {
            cs = conn_.prepareCall(sql);
            cs.setInt(1, seconds);
            cs.execute();
            cs.close();
        }
        catch (SQLException e)  {
            throw e;
        }
        finally {
            try {
                cs.close();
            }
            catch (SQLException ex) {
                // ignore
            }
        }
        return count;

    }

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<9BE9b.84539$mp.38321_at_rwcrnsc51.ops.asp.att.net>...
> How about you do a commit after the delete? My guess is that is the
> problem.
> Jim
>
> "Judge" <judge321_at_hotmail.com> wrote in message
> news:525c7692.0309152254.46b32222_at_posting.google.com...
> > Hi gurus,
> > I have a problem deleting records. I am using JDBC thin driver for
> > Oracle 9i release 2 with JDK 1.4.
> >
> > I have the following Table:
> > RESOURCE_ID CHANGE_INDICATOR TIME_STAMP
> > ------------ ---------------- ------------
> > M_CYCLE_5 0 16-SEP-03 39.52.429000 AM
> >
> > M_CYCLE_3 0 16-SEP-03 39.52.429000 AM
> >
> > M_CYCLE_0 0 16-SEP-03 11.39.52.439000 AM
> >
> >
> > I would like to delete all the records which are older than 1 minute.
> >
> > I do that by following SQL statement:
> > DELETE FROM deleted WHERE (SYSTIMESTAMP-time_stamp) > INTERVAL '60'
> > SECOND AND change_indicator <=0
> >
> > If I execute that SQL statment from the SQLPlus the above records will
> > be deleted.
> >
> > However if I do that from my java program it does not delete anything.
> > Is that a bug. If it is a bug, is there any work around for that.
> >
> > Below is my java method tries to do the same thing.
> >
> > public int delete(int seconds)
> > throws SQLException
> > {
> > int count = 0;
> > String sql = "DELETE FROM "
> > + tableName_
> > + " WHERE (SYSTIMESTAMP-time_stamp) > INTERVAL "
> > + "\'"
> > + seconds
> > + "\' SECOND "
> > + "AND change_indicator <= 0";
> > System.out.println(sql);
> > count = conn_.prepareStatement(sql).executeUpdate();
> > System.out.println("no of deleted data deleted = " + count);
> > return count;
> > }
> >
> >
> > thanks in advance,
> > Soe
Received on Fri Sep 19 2003 - 03:25:28 CDT