Very Computer

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

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?

 (etc)

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

I suggest you try removing the quotes in '$reply'

Quote:> 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...

> --------------------------------------------------------------------------

Quote:> 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;
> /

> --------------------------------------------------------------------------

Quote:> 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?

Where is your exception handler?  How do you account for and give
feedback when no updates are found?
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.

Thanks for everyones feedback!

On Thu, 11 Oct 2001 15:24:40 -0500, Vince Laurent