Michel Cadot wrote:
> "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.
Alternatively use the full qualified name like this:
update d2
set date_expired = date_expired + 365 * direct_update.period;
Kind regards
robert
Received on Tue Mar 07 2006 - 11:04:33 CST
