1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with comparing date in pl/sql block

Discussion in 'SQL PL/SQL' started by mukulverma2408, Sep 17, 2017.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hello Experts,

    I am trying to write a if-else sql block as below :
    Code (Text):

    DECLARE
       DOW VARCHAR;
       BEGIN
             SELECT TO_CHAR(SYSDATE,'DAY') INTO DOW FROM DUAL;
              IF
                    DOW = 'MONDAY'
                    THEN
                           DBMS_OUTPUT.PUT_LINE('TODAY IS MONDAY');
              ELSE
                           DBMS_OUTPUT.PUT_LINE('TODAY IS NOT MONDAY');
              END IF;
    END;
     
    Code (Text):

    It is resulting in below error :
    ERROR at line 2:
    ORA-06550: line 2, column 8:
    PLS-00215: String length constraints must be in range (1 .. 32767)
     
    What is the problem here.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    724
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Why use sql here?


    Code (Text):

    DECLARE
       DOW VARCHAR2(16) := TO_CHAR(SYSDATE,'DAY');
    BEGIN
      IF
        DOW = 'MONDAY'
      THEN
        DBMS_OUTPUT.PUT_LINE('TODAY IS MONDAY');
      ELSE
        DBMS_OUTPUT.PUT_LINE('TODAY IS NOT MONDAY');
      END IF;
    END;

     
    you need to carefully read the documentation
     
    mukulverma2408 likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,528
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It has already been pointed out that you have no length declaration for your varchar2 variable, which causes the error to be thrown. Another issue you will have is that even though the modified code will run no output will be visible; you also need to:

    set serveroutput on size 1000000

    so that you can see the text you're generating. As an example:

    Code (SQL):
    BING @ quanghoo > DECLARE
      2     DOW VARCHAR(16);
      3     BEGIN
      4           SELECT TO_CHAR(SYSDATE,'DAY') INTO DOW FROM DUAL;
      5            IF
      6                  DOW = 'MONDAY'
      7                  THEN
      8                         DBMS_OUTPUT.PUT_LINE('TODAY IS MONDAY');
      9            ELSE
    10                         DBMS_OUTPUT.PUT_LINE('TODAY IS NOT MONDAY');
    11            END IF;
    12* END;
    BING @ quanghoo > /

    PL/SQL PROCEDURE successfully completed.


    BING @ quanghoo >
     
    Now set serveroutput on:

    Code (SQL):
    BING @ quanghoo > SET serveroutput ON SIZE 1000000
    BING @ quanghoo > /
    TODAY IS NOT MONDAY

    PL/SQL PROCEDURE successfully completed.

    BING @ quanghoo >
    You can't overlook any detail with PL/SQL.
     
    mukulverma2408 likes this.
  4. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    I have update the block and it's compiling just fine, but the output is still not correct, appreciate any help
    Code (Text):

    DECLARE
    DT VARCHAR2(20);
     BEGIN
           SELECT  TO_CHAR(SYSDATE,'DAY') INTO DT FROM DUAL;
            IF DT = 'TUESDAY' THEN
                DBMS_OUTPUT.PUT_LINE('TODAY IS TUESDAY');
            ELSE
                DBMS_OUTPUT.PUT_LINE('TODAY IS NOT TUESDAY');  
           END IF;
    END;  
    /
     
    Code (Text):

    Output :
    TODAY IS NOT TUESDAY
     
    Code (Text):

    SQL> SELECT  TO_CHAR(SYSDATE,'DAY') FROM DUAL;
    TO_CHAR(SYSDATE,'DAY')
    ------------------------------------
    TUESDAY
     
     
  5. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    try

    DECLARE
    DT VARCHAR2(20);
    BEGIN
    SELECT TO_CHAR(SYSDATE,'DAY') INTO DT FROM DUAL;
    --DBMS_OUTPUT.PUT_LINE(length(dt));
    IF replace(DT,' ','') = 'TUESDAY' THEN
    DBMS_OUTPUT.PUT_LINE('TODAY IS TUESDAY');
    ELSE
    DBMS_OUTPUT.PUT_LINE('TODAY IS NOT TUESDAY');
    END IF;
    END;
    /
     
    mukulverma2408 likes this.
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,528
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is the problem:

    Code (SQL):
    SYS @ quanghoo > SELECT  TO_CHAR(SYSDATE-1,'DAY') FROM DUAL;

    TO_CHAR(S
    ---------
    TUESDAY

    SYS @ quanghoo > SELECT  ''''||TO_CHAR(SYSDATE-1,'DAY')||'''' FROM DUAL;

    ''''||TO_CH
    -----------
    'TUESDAY  '

    SYS @ quanghoo >
    There are two trailing spaces you don't account for; your code should be doing this:

    Code (SQL):
    SYS @ quanghoo > DECLARE
      2  DT VARCHAR2(20);
      3   BEGIN
      4         SELECT  TO_CHAR(SYSDATE-1,'DAY') INTO DT FROM DUAL;
      5          IF instr(DT,'TUESDAY') > 0 THEN
      6              DBMS_OUTPUT.PUT_LINE('TODAY IS TUESDAY');
      7          ELSE
      8              DBMS_OUTPUT.PUT_LINE('TODAY IS NOT TUESDAY');
      9         END IF;
    10  END;
    11  /
    TODAY IS TUESDAY

    PL/SQL PROCEDURE successfully completed.

    SYS @ quanghoo >
    You only need to find the string TUESDAY in the output and the instr() function will do that by returning a number value for where that substring starts in the string. This is why the > 0 condition is used; either the string is found or it isn't and, if it isn't, 0 is returned.
     
    mukulverma2408 likes this.
  7. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks David, worked just fine :)