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

From: Robert Klemme <bob.news_at_gmx.net>
Date: Wed, 8 Mar 2006 17:44:38 +0100
Message-ID: <478fvoFe5rejU1@individual.net>

Michel Cadot wrote:
> "Robert Klemme" <bob.news_at_gmx.net> a écrit dans le message de news:
> 47814dFe6i6aU1_at_individual.net...

>> 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
>>
>

> Because if this was what he wanted to do, he did not post
> that he did not have the result he wanted as he would have the result
> he wanted.

>
> Well, to be clearer. What he posted and what you posted is equivalent.

Are you sure about that? Note that "direct_update" is the name of the SP and "direct_update.period" is the SP parameter and not the column "d2.period".

There is a nice appendix about name resolution in "PL/SQL User's Guide and Reference".
see http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261.pdf

> So as this is not the result he wanted, I assumed what I said is the
> correct
> one of the two options.

I beg to differ.

Kind regards

    robert Received on Wed Mar 08 2006 - 10:44:38 CST