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

"Steve" <luodi_at_yahoo.com> a écrit dans le message de news: 1141747100.364769.123800_at_j52g2000cwj.googlegroups.com...
|
| 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;
|

This is what happens when you don't have some correct naming rules. Hint: change your parameter names to something like "p_pid" and "p_period" and this will work.

Regards
Michel Cadot Received on Tue Mar 07 2006 - 10:36:46 CST