sqloracle-databasesyntaxcompiler-errorspls-00103

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


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;
/

Solution

  • 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.