Results 1 to 8 of 8

Thread: SUBSTR function

  1. #1
    Join Date
    Aug 2002
    Posts
    121

    Question Unanswered: SUBSTR function

    Hello
    The command below runs perfectly in SQL*Plus....

    SELECT NEXT_DAY(sysdate - SUBSTR(sysdate,1,2),'FRIDAY') + 14
    FROM dual;

    ... However, when I run the same query in a procedure (below) a get an error on the SUBSTR portion of the SELECT statement.. See below..

    SELECT NEXT_DAY(sysdate - SUBSTR(sysdate,1,2),'FRIDAY') + 14
    INTO v_date
    FROM dual;

    Any thoughts on what the problem is?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is a dummy PL/SQL script, but runs perfectly OK on my 8.1.7:
    Code:
    SQL> set serveroutput on;
    SQL> declare
      2    v_date date;
      3  begin
      4    select next_day(sysdate - substr(sysdate, 1, 2), 'PETAK') + 14
      5    into v_date
      6    from dual;
      7
      8    dbms_output.put_line(v_date);
      9  end;
     10  /
    16.12.05
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Can you follow the same in your database? What result do you get? Still an error?

    P.S. 'PETAK' is Croatian word for 'FRIDAY'

  3. #3
    Join Date
    Aug 2002
    Posts
    121
    Here is my procedurem which was createed in Procedure Builder...

    PROCEDURE odd_day
    is
    v_date date;

    begin
    select next_day(sysdate - substr(sysdate, 1, 2), 'FRIDAY') + 14
    into v_date
    from dual;
    text_io.put_line(v_date);
    end;

    The error I get when trying to compile is ....
    wrong number or types of arguments in call to 'SUBSTR'

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What happens if you put this:

    SUBSTR(TO_CHAR(sysdate, 'dd.mm.yyyy.'), 1, 2)

    into your procedure? Date format, actually, doesn't matter too much - regarding your SUBSTR(something, 1, 2) - first two characters, it is only important that date format has DD as the first two characters.

  5. #5
    Join Date
    Aug 2002
    Posts
    121
    Almost, however, I had to convert it back to a number again of course... thanks

    TO_NUMBER(SUBSTR(TO_CHAR(sysdate, 'dd.mm.yyyy.'), 1, 2))


    Thanks for your assistance
    TM


  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm glad you solved the problem!

    P.S. You never told us: which Oracle version is it?

  7. #7
    Join Date
    Aug 2002
    Posts
    121
    version 8.0

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see ... well, this might be explanation of your problems. The lowest version I currently have is 8.1.7. (Oracle 7 was shut down 3 months ago) so I can't test your original procedure. OK, never mind.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •