CODEWITHSUNDEEP
sqloracle-databaseoracle12c
user4115586
user4115586

Reputation:

'PLS-00103' and 'ORA-06550' Errors

I have several blocks that I'm unable to run in Oracle SQL Developer 18.2.0.183. I'm not sure if I'm missing something in my blocks or if I need to make some change to the developer. The below block outputs, "Error starting at line : 1 in command -." (for declare). 

declare
total_purchases number(7,2);
begin
total_purchases :=20;
case
when (total_purchases>200) then dbms_output.put_line(‘high’);
when (total_purchases>100) and total_purchases<200) then dbms_output.put_line(‘mid);
when (total_purchases<100) then dbms_output.put_line(‘low’);
end case;
end

It also outputs the following:

enter image description here

Any help is appreciated. Thanks.

Upvotes: 0

Views: 183

Answers (4)

develtype
develtype

Reputation: 76

I mark with "^"

declare
  total_purchases number(7,2);
begin
  total_purchases :=20;
  case 
       when (total_purchases>200) then dbms_output.put_line('high');
       when ((total_purchases>100) and total_purchases<200) then dbms_output.put_line('mid');
            ^                                                                             ^
       when (total_purchases<100) then dbms_output.put_line('low');
  end case;
end;
   ^

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

Here's one way to do that - put CASE into DBMS_OUTPUT.PUT_LINE call:

SQL> set serveroutput on
SQL> DECLARE
  2     total_purchases   NUMBER (7, 2);
  3  BEGIN
  4     total_purchases := 20;
  5     DBMS_OUTPUT.put_line (CASE
  6                              WHEN total_purchases > 200
  7                              THEN
  8                                 'high'
  9                              WHEN     total_purchases > 100
 10                                   AND total_purchases < 200
 11                              THEN
 12                                 'mid'
 13                              WHEN total_purchases < 100
 14                              THEN
 15                                 'low'
 16                           END);
 17  END;
 18  /
low

PL/SQL procedure successfully completed.

SQL>

Or, if you use your syntax, fix errors first (superfluous parenthesis, missing semi-colon):

SQL> DECLARE
  2     total_purchases   NUMBER (7, 2);
  3  BEGIN
  4     total_purchases := 20;
  5
  6     CASE
  7        WHEN (total_purchases > 200)
  8        THEN
  9           DBMS_OUTPUT.put_line ('high');
 10        WHEN     (total_purchases > 100)
 11             AND total_purchases < 200
 12        THEN
 13           DBMS_OUTPUT.put_line ('mid');
 14        WHEN (total_purchases < 100)
 15        THEN
 16           DBMS_OUTPUT.put_line ('low');
 17     END CASE;
 18  END;
 19  /
low

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use single quote ('') not and also case was not defined properly

declare
total_purchases number(7,2);
begin
   total_purchases :=20;
   case
      when (total_purchases>200) then dbms_output.put_line('high');
      when (total_purchases>100) and total_purchases<200) then 
      dbms_output.put_line('mid');
      when (total_purchases<100) then dbms_output.put_line('low');
   end 
end

Upvotes: 0

Vasyl Moskalov
Vasyl Moskalov

Reputation: 4630

You have a two typo in 

when (total_purchases>100) and total_purchases<200) then dbms_output.put_line(‘mid);

Replace with

when (total_purchases>100 and total_purchases<200) then dbms_output.put_line(‘mid‘);

Upvotes: 0

Related Questions