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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 7 Mar 2006 18:43:53 +0100
Message-ID: <440dc659$0$8420$626a54ce@news.free.fr>

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

Regards
Michel Cadot Received on Tue Mar 07 2006 - 11:43:53 CST