Home » SQL & PL/SQL » SQL & PL/SQL » dates again
dates again [message #2833] Fri, 16 August 2002 02:59 Go to next message
prashant
Messages: 122
Registered: September 2000
Senior Member
hi all,
please help me with this,
as per Todd's helpful advice i made this trigger which is intended to prevent any one from inserting into the emp table between 7 at night and 6 in the morning :

create or replace trigger emp_trg before insert on emp
begin
if to_char(sysdate, 'hh24') >= '19'or to_char sysdate, 'hh24') < '06' then
raise_application_error(-20000, 'Inserts not allowed in this timeframe');
end if;
end;/

but when i actually inserted one record there was the error :
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 1014
ORA-06512: at "SCOTT.TRG_EMP", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_EMP'

so i just decided to check it out by using this anonymous block

1 begin
2 if ( (to_char(sysdate,'hh24')>='19') or (to_char(sysdate,'hh24')<'06' )) then
3 dbms_output.put_line('not allowed') ;
4 else
5 dbms_output.put_line('allowed') ;
6 end if ;
7* end ;
prashant> /

begin
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 1014
ORA-06512: at line 2

please help
Re: dates again [message #2835 is a reply to message #2833] Fri, 16 August 2002 03:26 Go to previous messageGo to next message
Christine Pollhaus
Messages: 18
Registered: May 2002
Junior Member
Try the not between statement like this:

CREATE OR REPLACE TRIGGER TRI_BIUD

BEFORE INSERT OR UPDATE OR DELETE ON emp

BEGIN
IF TO_CHAR(SYSDATE, 'HH24')
NOT BETWEEN '06' AND '19' THEN

RAISE_APPLICATION_ERROR (-20001, 'NOT ALLOWED!');
END IF;
END

Mix the time and Error-message if you want it the other way. The error-mess no (-20001) can be chosen by yourself but do not name a number <20001 for these are all ora-err mess no's already used!

Good luck
Christine
not sure [message #2836 is a reply to message #2835] Fri, 16 August 2002 03:40 Go to previous messageGo to next message
prashant
Messages: 122
Registered: September 2000
Senior Member
I dont think using NOT BETWEEN '06' AND '19' would be correct since it would then check for the time slot of 6 in the morning and 7 in the evening, which is quite the opposite of what i want.
but i want to know is why that error is coming even in the case of the anonymous pl/sql block and how to resolve it
Re: not sure [message #2839 is a reply to message #2835] Fri, 16 August 2002 04:04 Go to previous messageGo to next message
Christine Pollhaus
Messages: 18
Registered: May 2002
Junior Member
Hi,

You can either use the opposite like

If ... between ... and ... 'Allowed' ELSIF ... 'Not allowed' ...

it must not be 'not between'. Use it like ever you want, "play" with it!

Anyway about the other error with anonymous pl/sql I do not know anything, but would be nice to learn too.

regards Christine
Re: dates again [message #2846 is a reply to message #2833] Fri, 16 August 2002 09:13 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
It sounds like someone has accidentally inserted another row into your DUAL table (very bad).

What do you get from this query:

select count(*)
  from dual;


You should only have 1 row. If not, you'll need to delete the extra row(s).

Christine's approach will also work, but the statement:

to_char(sysdate, 'hh24') not between '06' and '19'


will allow table modifications in the 7:00pm hour (7:00-7:59pm). If you change the '19' to '18', this will work (will allow changes up to 6:59pm).

By the way, -20000 is a valid number for raise_application_error (the range is -20000 to -20999).
Previous Topic: Re: Warning: Package created with compilation errors.
Next Topic: PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 16:47:41 CDT 2024