-5
CREATE OR REPLACE TRIGGER SYS.Restict_user
AFTER LOGON 
ON DATABASE
BEGIN 
     IF(TO_CHAR(SYSDATE, 'DAY')) = UPPER('SUNDAY') OR (TO_CHAR(SYSDATE, 'DAY')) = UPPER('SATURDAY') THEN
        RAISE_APPLICATION_ERROR(-20000, 'error');
END IF;
END; 
 /

Sadly, it's not working

  • 1
    Why -1? Please leave comment. – Dmitry Sokolov Nov 10 at 17:12
  • 2
    Are you sure your server NLS settings are what you think they are? BTW your question may have been downvoted because it doesn't actually contain a question. Also, "not working" is a poor description of a problem. – mustaccio Nov 10 at 17:19
  • 1
    @DmitrySokolov Enclose day part with trim i.e TRIM((TO_CHAR(SYSDATE, 'DAY'))) – Kumar Nov 10 at 21:37
  • 1
    Also, it is pointless to pass an upper-case literal string to the UPPER() function ... – EdStevens Nov 11 at 14:13
  • 1
    @DmitrySokolov - I'm not one of the -1 voters, but with regard to "Why -1?" chances are they are due to lack of sufficient detail. You should more precisely define "it's not working". Do you get an error when creating the trigger? Does it get created but not seem to run at all? Does it seem to run but produce the wrong result? etc. Also document what you have done to test the situation, i.e. what command you have run to check it is/not working. – David Spillett Nov 11 at 15:04
1

Your code has two flaws.

  1. TO_CHAR(..., 'DAY') pads the result with spaces.
  2. TO_CHAR(..., 'DAY') depends on current user session NLS_DATE_LANGUAGE

Try this one:

IF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=american') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR(-20000, 'error');
END IF;

Note, a user with ADMINSTER DATABASE TRIGGER privilege (e.g. a DBA) will ignore the exception and is able to logon to the database anyway.

1

Do simple test with PL/SQL and see which block works(with and without trim function).First part of code to show why your logic failing.

SQL> ed
Wrote file afiedit.sql

  1  BEGIN
  2       IF (TO_CHAR(SYSDATE, 'DAY')) = 'SUNDAY' OR (TO_CHAR(SYSDATE, 'DAY')) = 'SATURDAY'
  3         THEN
  4            -- RAISE_APPLICATION_ERROR(-20009, 'Not allowed to login on Weekends');
  5               DBMS_OUTPUT.PUT_LINE('Without Trim Function');
  6       END IF;
  7          IF TRIM((TO_CHAR(SYSDATE, 'DAY'))) = 'SUNDAY' OR TRIM((TO_CHAR(SYSDATE, 'DAY'))) = 'SATURDAY'
  8          THEN
  9      DBMS_OUTPUT.PUT_LINE('With Trim Function');
 10       END IF;
 11* END;
SQL> /
With Trim Function

PL/SQL procedure successfully completed.

Edit: As suggested by Michael use format fmday

      BEGIN
             IF (TO_CHAR(SYSDATE, 'fmday')) = 'sunday' OR (TO_CHAR(SYSDATE, 'fmday')) = 'saturday'
               THEN
                   -- RAISE_APPLICATION_ERROR(-20009,'error ');
                      DBMS_OUTPUT.PUT_LINE('This is Weekend');
          END IF;
       END;
    /
This is Weekend

PL/SQL procedure successfully completed.

Thanks Michael

  • Replace trim() with a more appropriate format. Eg: 'fmDAY'. Also, that is not how the French spell "Sunday" – Michael Kutz Nov 11 at 0:44
1

There are trailing blanks in the result of to_char(...,'DAY'). From the Oracle Database SQL Language Reference:

  • The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters.
    ...

The FM modifier suppresses the above padding in the return value of the TO_CHAR function.

so 'to_char(sysdate,'fmDAY') would be the correct format specifier as already stated in another answer here.

I prefered to avoid string formatting problems and language dependencies by using the 'D' format specifier. But this value still depends on NLS_TERRITORY as @Wernfried_Domscheit pointed out. The to_char function does not allow to set NLS_TERRITORY but only NLS_DATE_LANGUAGE: So I propose the following

CREATE OR REPLACE TRIGGER SYS.Restict_user
AFTER LOGON 
ON DATABASE
BEGIN 
     IF(TO_CHAR(SYSDATE, 'fmDY', 'NLS_DATE_LANGUAGE') in ('SAT','SUN')) THEN
        RAISE_APPLICATION_ERROR(-20000, 'error');
END IF;
END; 
 /
  • Result of TO_CHAR(SYSDATE, 'D') depends on current session NLS_TERRITORY, so it may return "wrong" result. – Wernfried Domscheit Nov 11 at 16:39
  • I did not know that. can you provide more information, when will this happen? – miracle173 Nov 11 at 16:58
  • Try alter session set nls_territory = 'germany';, then you will see the difference. Saturday/Sunday gives 6/7 because in western Europe typically Monday is the first day of the week. In Arabic countries typically the Saturday is considered as first day of week, etc. – Wernfried Domscheit Nov 11 at 18:33
  • @WernfriedDomscheit thank you, I changed my post – miracle173 Nov 11 at 19:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.