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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 8 Mar 2006 17:56:24 +0100
Message-ID: <440f0cbe$0$22515$626a54ce@news.free.fr>

"Robert Klemme" <bob.news_at_gmx.net> a écrit dans le message de news: 478fvoFe5rejU1_at_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".

My sincere apology, i read you too fast and thought you were talking about the column.

|

| 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

|

Regards
Michel Cadot Received on Wed Mar 08 2006 - 10:56:24 CST