Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    47

    Question Unanswered: PLS-00103: Error in pl/sql block

    i have a procedure like

    CREATE OR REPLACE procedure proc_mgtrptdetails_mon_ca
    is
    begin
    delete from mgtrptdetails_mon_ca;


    begin
    insert into mgtrptdetails_mon_ca
    select
    case when (to_char(processdate + (7 -(to_char(processdate, 'D') - 1)),'dd-mon-yyyy') > sysdate) then to_char(sysdate,'dd-mon-yyyy')
    else to_char(processdate + (7 -(to_char(processdate, 'D') - 1)),'dd-mon-yyyy' )
    end as processdate, state, status, count(polnum) as nbr, sum(annualprem+annualsd) as annualprem
    from subcompenddetails
    where substr(channel,1,2)='CA' and processdate > '01-oct-2004'
    group by processdate,state,status;
    end;

    commit;
    end;

    it says:
    PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
    the same query works fine from sql. only if i create it as a pl/sql block it gives error.
    thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    9
    you can not use the CASE function in an anonymous block.
    use "execute immediate".
    Last edited by merzi; 10-28-04 at 03:11.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What anonymous block? It is part of the procedure ...

    However, this procedure compiles and executes without errors in my database (Ora 9.0.1.3.0) ... honestly, can't figure out what might be wrong.

    Sundarr, what DB do you use? Does it show error during procedure creation or execution?

  4. #4
    Join Date
    Jan 2004
    Posts
    9
    u are rigth, oracle 9 is ok oracle 8 didn't work.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    One work around would be to create a VIEW out of that query and use it inside your procedure.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    You should be comparing a DATE that is GREATER than another DATE.
    Not a CHAR that is greater than another CHAR.

    You are doing this in your CASE statement and in your where clause it
    looks like you are comparing a date to a character.

    as your rules are now, 19-jan-2003 will be GREATER than 10-oct-2004

    example:
    PHP Code:
      1  select case when
      2     
    '10-jan-2004' '09-oct-2004' 
      
    3     then '1' else '2' end
      4
    from dual
    sys
    @q9gds2ps> /

    C
    -
    1


      1  select 
    case when
      2     to_date
    ('10-jan-2004','dd-mon-yyyy') > 
      
    3     to_date('09-oct-2004','dd-mon-yyyy'
      
    4*   then '1' else '2' end from dual
    sys
    @q9gds2ps> /

    C
    -

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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