Re: oracle 10g bug or sth wrong of my code?

Steve wrote:
> I just try to update DATE field by add n * 365 by a stores_procedure,
> but
> after run the procedure, the date field becomes NULL. I don;t know why?
>
>
> I have tried on Oracle 10.1 and 10.2(XE)
> Thanks for help!
>
> Steve
> -----------------------------------------------------------
> drop table d2;
>
> CREATE TABLE D2
> (
> ID VARCHAR2(20 BYTE) NOT NULL,
> DATE_CREATED DATE,
> DATE_UPDATED DATE,
> DATE_EXPIRED DATE,
> DATE_PAID DATE,
> PERIOD NUMBER,
> ACCOUNT VARCHAR2(40 BYTE),
> CONSTRAINT "CID" PRIMARY KEY ("ID", "ACCOUNT")
> );
>
> INSERT INTO D2 ( ID, DATE_CREATED, DATE_UPDATED, DATE_EXPIRED,
> DATE_PAID, PERIOD,
> ACCOUNT ) VALUES (
> '001', NULL, TO_Date( '03/02/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS
> AM'), TO_Date( '03/07/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , NULL, NULL, 'inhouse');
> COMMIT;
>
>
> CREATE OR REPLACE PROCEDURE direct_update(pid varchar2, period number)
> IS
> BEGIN
>
> update d2
> set date_expired = date_expired + 365 * period;
>
>
> END direct_update;
> /
>
> select date_expired from d2;
>
>
> exec direct_update('001', 2);
>
> select date_expired from d2;

Another thought ... though not the proximate cause.

Try a COMMIT after doing inserts and running PL/SQL.

Things work better that way.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)