Michel Cadot wrote:
> "Robert Klemme" <bob.news_at_gmx.net> a écrit dans le message de news:
> 475sp5Fe42ukU1_at_individual.net...
>> 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
>>
>
> But this is exactly what he doesn't want to do!
You lost me here. How do you know the OP didn't want to do this?
robert
Received on Wed Mar 08 2006 - 06:31:09 CST
