CODEWITHSUNDEEP
oracle-database
Manimaran Balagangadharan
Manimaran Balagangadharan

Reputation: 45

Keep getting compilation error while creating the following procedure

Create a procedure named 'select_city' which accepts one input parameter user_id of type number and one output parameter city_details of type varchar. This procedure is used to display the city_details of user.If the user is from bangalore then display the city_details as 'User is from Bangalore',or if the user is from chennai then display the city_details as 'User is from Chennai', else display the city_details as 'User is from other cities'.

CREATE PROCEDURE select_city (  user_id IN  user_details.id%type,
                                city_details OUT VARCHAR2(255) ) 
AS
        BEGIN
        SELECT CASE
        WHEN city = 'Bangalore' THEN 'User is from Bangalore'
        WHEN city = 'Chennai' THEN 'User is from Chennai'
        ELSE 'User is from other cities'
        END tmp_status INTO city_details
        FROM contact cnt 
        WHERE cnt.id = user_id;
        END;

Upvotes: 1

Views: 2671

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

OUT parameter shouldn't have size. Remove it.

Also (probably not related to error you got), consider using CREATE OR REPLACE because any subsequent CREATE will fail as the procedure - although invalid - already exists so you'd have to drop it first.

Sample table:

SQL> create table contact as
  2  select 'Bangalore' city, 1 id from dual;

Table created.

Procedure:

SQL> create or replace procedure select_city
  2    (user_id       in number,
  3     city_details out varchar2                  --> no size here
  4    )
  5  as
  6  begin
  7    select case
  8      when city = 'Bangalore'  then
  9        'User is from Bangalore'
 10      when city = 'Chennai'    then
 11        'User is from Chennai'
 12      else
 13        'User is from other cities'
 14    end tmp_status
 15    into city_details
 16    from contact cnt
 17    where cnt.id = user_id;
 18  end;
 19  /

Procedure created.

Testing:

SQL> set serveroutput on;
SQL> declare
  2    l_citydet varchar2(255);
  3  begin
  4    select_city(1, l_citydet);
  5    dbms_output.put_line(l_citydet);
  6  end;
  7  /
User is from Bangalore

PL/SQL procedure successfully completed.

SQL>

Upvotes: 3

Related Questions