PL./SQL.... SQL question...

From: Vince Laurent <vlaurent_at_NOSPAMPLEASE.networkusa.net>
Date: Thu, 11 Oct 2001 15:24:40 -0500
Message-ID: <lsvbst04ofr8g6qsnr56klkl2gfbjrhpfc@4ax.com>

I am inches away from getting this to work. It seems to work but it does not change the table after the user selects 'Y' to do so. (I have just my user id coded in there now so I don't hose the entire user record). Am I using a conditonal correctly? Maybe something with PL/SQL and SQL? I am running Oracle 8.0.6.2...

set heading on;
set feedback off;

/* Get the existing client numbers from the database */
column mandt heading "Existing Client Numbers" format A25; select distinct mandt from usr02;

clear;
set heading off;

select '-----------------------------------' from dual;

set heading on;

/* Ask the user what client they would like to do the work on */
accept cnum PROMPT 'What client do you need to zero out the times for? '

column mandt heading Client;
column trdat heading Date;
column ltime heading Time;
column bname heading Username;

select mandt, trdat, ltime, bname from usr02 where mandt = &cnum;

clear;
set heading off;
select ' 'from dual;
set heading on;

/* If the reply is good verify the users intent */
clear;
set heading off;
accept reply prompt 'Are you sure you want to do this? (Y/[N])'

set feedback on;
set heading on;

begin
  if ( UPPER('$reply') = 'Y' )
  then
    /* change the last login date and time to all zeros */     /* use only MY userid for testing... */     update usr02
    set ltime = '000000', trdat = '00000000'     where mandt = &cnum
    and bname = 'VLAURE';
    commit;
  end if;
end;
/

Sample output:

SQL> @zero

Existing Client Numbers

000
001
010
066
220
230
240
330

What client do you need to zero out the times for? 000

Client                    Date     Time   Username
------------------------- -------- ------ ------------
000                       00000000 000000 BATCH_USER
000                       20011001 190929 DDIC
000                       20011002 161120 DKNICK
000                       20010130 142453 OSSUSER
000                       20010518 143351 RAGRAW
000                       20011001 184203 SAP*
000                       19960903 110701 SAPCPIC
000                       20011002 134813 TKISSE
000                       00000000 000000 TMSADM
000                       20011011 141659 VLAURE

Are you sure you want to do this? (Y/[N])y

PL/SQL procedure successfully completed.

But when I go look at the table again I see the result has not been changed:

Client Date Time Username
------ --------- ------- --------
000 20011011 141659 VLAURE <------------- 010 20011011 084635 VLAURE
220 20010201 181951 VLAURE
230 20010214 154702 VLAURE
240 20010103 140410 VLAURE
330 20010308 174658 VLAURE Ideas? Received on Thu Oct 11 2001 - 15:24:40 CDT