Pl SQL Oracle PLS-00103: Encountered the symbol "CREATE"

Question

this code takes input for zipcode, city, and state and then inserts that into the table created Address. Prior to inserting data it will check if the zipcode is already in the table, if so calling procedure(error) to display an error code.

Im getting an error code pls-00103: encountered the symbol "CREATE" when trying to execute the code. Here is my code so far. Thanks for any help in advance.

drop table address;

create table address(zipcode NUMBER, city varchar2(30), state varchar2(20));

create or replace procedure error as
begin
  dbms_output.put_line('Error Zip Code already found in table');
end error;

declare
 zzip number;
 ccity varchar2(30);
 sstate varchar2(30);

create or replace procedure location(p_zipcode NUMBER,
                                     p_city varchar2,
                                     p_state varchar2) is
zip address.zipcode%type;
cit address.city%type;
st address.state%type;

begin
  select count(*) from address into zip where zipcode = zip;
  if any_rows_found then 
    error;
  else
  Insert into address values(zip, cit, st);
  end if;
end location;

begin
  select &zipcode into zzip from dual;
  select &city into ccity from dual;
  select &state into sstate from dual;
  procedure location(zzip, ccity, sstate);
end;
/

Answer 1

I'm not sure what you're trying to do, but the following may be closer to what you had in mind:

drop table address;

create table address(zipcode NUMBER, city varchar2(30), state varchar2(20));

declare
 zzip number;
 ccity varchar2(30);
 sstate varchar2(30);

 procedure error is
 begin
  dbms_output.put_line('Error Zip Code already found in table');
 end error;

 procedure location(p_zipcode NUMBER, p_city varchar2, p_state varchar2) is
  zip_count   NUMBER;
 begin
  select count(*)
    into zip_count
    from address
    where zipcode = p_zipcode;

  if zip_count > 0 then 
    error;
  else
   Insert into address
     (zipcode, city, state)
   values
     (p_zipcode, p_city, p_state);
  end if;
 end location;

begin
 select &zipcode into zzip from dual;
 select &city into ccity from dual;
 select &state into sstate from dual;

 location(zzip, ccity, sstate);
end;
/

Best of luck.

Answer 2

I dont know if I understand your problem correctly, but there are certain correction I'd like to address to answer your problem

• First, if you are going to create a procedure/function, do it in a separate worksheet then compile it. Dont compile it together with other anonymous blocks because most of the time, if you dont end your other blocks with '/', errors will surely generate.

• Second, your DECLARE statement is misplaced, if you are going to make an anonymous block, make sure DECLARE, BEGIN and END are in line, dont create a procedure/function inside an anonymous block.

• Third, you are declaring variables in your procedures and using them but doesnt have an initial value, so it will just pass a null value to the DML statement in your procedure. just use the parameter directly.

• Fourth, avoid creating a procedure that only contains dbms_output.put_line. Its silly.

• Lastly, your anonymous block that should be calling your procedure, uses '&', please avoid using '&' inside pl/sql as '&' is a feature in SQL*Plus and doesnt have any meaning in PL/SQL, instead, you can use ':' as for binding variables. But you use '&' not in binding variables so you should remove that;

Try this:

drop table address;

 /

create table address(zipcode NUMBER, city varchar2(30), state varchar2(20));

 / 

create or replace procedure location(p_zipcode NUMBER, 
                                     p_city varchar2,    
                                     p_state varchar2) is

zip address.zipcode%type;


begin
  select count(*) 
    from address 
    into zip 
    where zipcode = p_zipcode 
             and city =p_city 
                and state = p_state;

  if zip > 0 then 
   dbms_output.put_line('Error Zip Code already found in table');
  else
  Insert into address values(p_zipcode, p_city, p_state);
  end if;
end location;

/


begin

 location(:zzip, :ccity, :sstate);

end;