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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 8 Mar 2006 16:51:26 +0100
Message-ID: <440efd7e$0$22565$626a54ce@news.free.fr>

"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. So as this is not the result he wanted, I assumed what I said is the correct one of the two options.

Regards
Michel Cadot Received on Wed Mar 08 2006 - 09:51:26 CST