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

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

Post by Vince Lauren » Sat, 13 Oct 2001 05:24:40



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:


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?

 
 
 

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

Post by Stephen T. Parfit » Sat, 13 Oct 2001 06:10:48


I don't think you need the quotes in UPPER('$reply')
... UPPER('$reply') is never equal to 'Y', UPPER($reply) may be equal to
'Y' or 'N'.

 (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

(etc)

 
 
 

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

Post by ST » Sat, 13 Oct 2001 10:18:40


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

>   if ( UPPER('$reply') = 'Y' )



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

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

----------------------------------------------------------------------

- Show quoted text -

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

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

----------------------------------------------------------------------

- Show quoted text -

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?

 
 
 

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

Post by Ron Reid » Sat, 13 Oct 2001 10:22:55



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


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

What is '$reply'?  That looks funky.

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.

 
 
 

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

Post by Vince Lauren » Sat, 13 Oct 2001 22:54:24


It turned out to be a typo.  The $ should have been a &.  It was one
of those cases where I was looking at the code for such a long time I
didn't see it.  

Thanks for everyones feedback!

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


>  if ( UPPER('$reply') = 'Y' )