dates again [message #2833] |
Fri, 16 August 2002 02:59 |
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 |
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 |
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 |
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 |
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).
|
|
|