1 and 3 begin ошибка case

Is the usage of CASE wrong in the code below?
I am getting an error:

«PLS-00103: Encountered the symbol «;» when expecting one of the
following:

case The symbol «case» was substituted for «;» to continue. «

Code is :

create or replace PROCEDURE MIK_3PL_ITEM_ERRORS_PROC_1 IS 
  i_error_code varchar2(5);
  i_desc       varchar2(200);
  CURSOR c_3pl_error IS
    SELECT mie.client_item_id, mie.message_id, 
           mie.error_desc, mis.process_flag
      FROM mik_3pl_item_error_etl mie, 
           dummy_staging mis
     WHERE mie.client_item_id = mis.client_item_id
       AND mie.message_id = mis.message_id;
BEGIN
  dbms_output.put_line ('hello');
  for i in c_3pl_error
  loop
    dbms_output.put_line ('in loop');
    DECLARE
      -- L_relations_exist VARCHAR2(1);
      L_error_message   VARCHAR2(255) := NULL;
      L_return          BOOLEAN       := FALSE;
    BEGIN
      select error_code
        into i_error_code
        from mik_3pl_error_desc
       where description = i.error_desc;
      -- dbms_output.put_line(i_error_code);  
      CASE i_error_code
         WHEN  'E2'  THEN dbms_output.put_line ('in case');
      END;  -- end of CASE */
    END; /*End of begin */
  end loop;
END MIK_3PL_ITEM_ERRORS_PROC_1;

a_horse_with_no_name's user avatar

asked Jan 3, 2014 at 17:27

user3158124's user avatar

2

It needs to be:

    ...
      CASE i_error_code
         WHEN  'E2'  THEN dbms_output.put_line ('in case');
      END CASE;  -- end of CASE */
    END;

It’s trying to treat the END; as the end of the block — matching that END with the BEGIN — and it knows the case is still open.

The documentation for the CASE statement shows that. (Not to be confused with a CASE expression, which does just have END. Ahem. Thank you Nicholas!)

answered Jan 3, 2014 at 17:38

Alex Poole's user avatar

Alex PooleAlex Poole

183k11 gold badges178 silver badges313 bronze badges

1

Источник

I’m trying to use the cases environment inside an equation environment.
The sample code is quite simple:

begin{equation*}
        X(omega) = begin{cases}
                        1 text{se $omega in A$} \
                        0 text{se $omega in A^c$}
                    end{cases}

end{equation*}

Compiling it with Kile results in this error:

Missing $ inserted

and some other messages about ending delimiters not present.

I have usepackage{amsmath} at the beginning of my document.

Also I must tell you that I have an other piece of code, which is identical, and which works fine:

 begin{equation*}
  B_i = begin{cases}
        A_i^c text{se $i in I$,}
        \
        A_i text{se $i in I smallsetminus I'$}.
        end{cases}
 end{equation*}

Also this piece gave me some errors the other day, then I changed the begin{equation*} ... end{equation*} to $$ and $$ and it worked.
Replace $$ with the equation environment and the error was magically gone.

I’ve already tried doing this with that piece of code, but nothing changes.

Источник
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

WHILE @mN < @mK         -- пока не все месяцы пройдены
    BEGIN
        SELECT @mN CASE             -- текущий номер месяца
            WHEN 1 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '01.31.' + @curr_year) AND (fedp = 1) OR
                                          (pr_o = 1) AND (dat < '01.31.' + @curr_year) AND (fedp = 1) AND (dat > '01.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 2 THEN 
                BEGIN
                    If isdate(@curr_year +'0229') = 0 -- високосный год?
                        BEGIN -- не високосный
                            SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                            FROM         od_fed
                            WHERE     (pr_o = 0) AND (dat = '02.28.' + @curr_year) AND (fedp = 1) OR
                                                  (pr_o = 1) AND (dat < '02.28.' + @curr_year) AND (fedp = 1) AND (dat > '02.01' + @curr_year)
                            
                            INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                            SET @mN = @mN + 1 -- увеличиваем месяц
                        END
                    Else
                        BEGIN -- високосный
                            SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                            FROM         od_fed
                            WHERE     (pr_o = 0) AND (dat = '02.29.' + @curr_year) AND (fedp = 1) OR
                                                  (pr_o = 1) AND (dat < '02.29.' + @curr_year) AND (fedp = 1) AND (dat > '02.01' + @curr_year)
                            
                            INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                            SET @mN = @mN + 1 -- увеличиваем месяц
                        END
                    End If
                END         
            WHEN 3 THEN
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '03.31.' + @curr_year) AND (fedp = 1) OR
                                          (pr_o = 1) AND (dat < '03.31.' + @curr_year) AND (fedp = 1) AND (dat > '03.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 4 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '04.30.' + @curr_year) AND (fedp = 1) OR
                                          (pr_o = 1) AND (dat < '04.30.' + @curr_year) AND (fedp = 1) AND (dat > '04.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 5 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '05.31.' + @curr_year) AND (fedp = 1) OR
                                          (pr_o = 1) AND (dat < '05.31.' + @curr_year) AND (fedp = 1) AND (dat > '05.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 6 THEN
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '06.30.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '06.30.' + @curr_year) AND (fedp = 1) AND (dat > '06.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 7 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '07.31.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '07.31.' + @curr_year) AND (fedp = 1) AND (dat > '07.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 8 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '08.31.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '08.31.' + @curr_year) AND (fedp = 1) AND (dat > '08.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 9 THEN
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '09.30.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '09.30.' + @curr_year) AND (fedp = 1) AND (dat > '09.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 10 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '10.31.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '10.31.' + @curr_year) AND (fedp = 1) AND (dat > '10.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
            WHEN 11 THEN 
                BEGIN
                    SELECT     @kol = (SUM(kolf) + SUM(kolt)), @summa = (SUM(sumf) + SUM(sumt))
                    FROM         od_fed
                    WHERE     (pr_o = 0) AND (dat = '11.30.' + @curr_year) AND (fedp = 1) OR
                                              (pr_o = 1) AND (dat < '11.30.' + @curr_year) AND (fedp = 1) AND (dat > '11.01' + @curr_year)
                    
                    INSERT INTO #temp_tb (param_name, tm_kol, tm_sum) VALUES ('ONLS_OTP_' + @mN + @curr_year, @kol, @summa)
                    SET @mN = @mN + 1 -- увеличиваем месяц
                END
        END
    END
Источник

Вот примерный текст функции. Создаю её при помощи pgAdmin.
CREATE FUNCTION public.get(IN topic integer) RETURNS character varying AS
$BODY$CASE WHEN (SELECT count(a) FROM table WHERE b=’FALSE’ AND d=topic)>0 THEN
get_on_topic(topic)
WHEN (SELECT count(a) FROM table WHERE d=topic)>0 THEN
(SELECT get_on_topic(topic) FROM set_a_on_topic(topic))
WHEN (SELECT DISTINCT count(a) FROM table,table2 WHERE table.c=table2.c AND b=’FALSE’ AND d=topic)>0 THEN
get_b_on_topic(topic)
WHEN (SELECT DISTINCT count(a) FROM table,table2 WHERE table.c=table2.c AND d=topic)>0 THEN
(SELECT get_b_on_topic(topic) FROM set_a_b_on_topic(topic))
WHEN (SELECT count(a) FROM table,table3 WHERE table.b=’FALSE’ AND table.c=table3.c AND d=topic)>0 THEN
get_q_on_topic(topic)
ELSE
(SELECT get_q_on_topic(topic) FROM set_a_q_on_topic(topic))
END;$BODY$
LANGUAGE sql VOLATILE NOT LEAKPROOF;

А выдает следующую ошибку — Ошибка синтаксиса(примерное положение «CASE»).

Источник

I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.

  1. Missing IS
  2. Missing END
  3. Missing Slash
  4. EXECUTE IMMEDIATE

1. Missing IS

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «BEGIN».

SQL> set serveroutput on;
SQL> create or replace procedure p1
  2  begin
  3    dbms_output.put_line('Procedure 1');
  4  end;
  5  /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         ( ; is with default authid as cluster compress order using
         compiled wrapped external deterministic parallel_enable
         pipelined result_cache accessible rewrite
         The symbol "is" was substituted for "BEGIN" to continue.

Solution

The keyword IS is expected before BEGIN as explained above.

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

Procedure created.

SQL> show errors
No errors.

2. Missing END

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «end-of-file».

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  /

Warning: Procedure created with compilation errors.<

Let’s see the error.

SQL> show errors
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/38     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         ( begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-identifier>
         <a bind variable> << continue close current delete fetch lock
         insert open rollback savepoint set sql execute commit forall
         merge pipe purge json_exists json_value json_query
         json_object json_array

Solution

In this case, the keyword END is expected before the symbol / (slash).

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

Procedure created.

SQL> show errors
No errors.

3. Missing Slash

When we tried to compile two procedures in a session, we got PLS-00103: Encountered the symbol «CREATE».

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6
  7  create or replace procedure p2
  8  is
  9  begin
 10    dbms_output.put_line('Procedure 2');
 11  end;
 12  /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1      PLS-00103: Encountered the symbol "CREATE"

This is because every programming unit is an independent one, we should use symbol / (slash) in SQL*Plus to compile them separately.

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p2
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 2');
  5  end;
  6  /

Procedure created.

4. EXECUTE IMMEDIATE

In an anonymous PL/SQL block, we use EXECUTE IMMEDIATE.

SQL> begin
  2    execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
  3  end;
  4  /
  execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
                                             *
ERROR at line 2:
ORA-06550: line 2, column 46:
PLS-00103: Encountered the symbol "NO_VALUE" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "NO_VALUE" to continue.

Solution

For EXECUTE IMMEDIATE statement, you should use extra single quotes to escape original single quotes in the statement like this.

SQL> begin
  2    execute immediate 'select nvl(first_name, ''NO_VALUE'') from employees';
  3  end;
  4  /

PL/SQL procedure successfully completed.

Further reading: How to Use Bind Variable in Oracle

Источник

Возможно, вам также будет интересно:

  • 1 рубль 60 лет советской власти ошибка
  • 0xo0000142 ошибка при запуске игры
  • 1 рубль 1987 толстой ошибка цена
  • 0xfe ошибка привода мотора pf epson 1410
  • 0xfb ошибка привода мотора pf epson 1410

    • Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии