oracle-databasesyntaxplsqlpackagepls-00103

Can't compile PL/SQL in oracle sql developer


This is my package:

CREATE OR REPLACE PACKAGE DEPARTMENT_INFO AS

  TYPE t_dep_loc IS RECORD(region_name regions.region_name%type,
                           country_name countries.country_name%type,
                           state locations.state_province%type,
                           city locations.city%type);

  TYPE t_dep_mgr IS RECORD(first_name employees.first_name%type,
                           last_name employees.last_name%type,
                           email employees.email%type,
                           phone_number employees.phone_number%type);

  FUNCTION location_department(p_department_id departments.department_id%TYPE)
    RETURN t_dep_loc;

  FUNCTION manager_department(p_department_id departments.department_id%TYPE)
    RETURN t_dep_mgr;

END DEPARTMENT_INFO;


CREATE OR REPLACE PACKAGE BODY department_info AS

  FUNCTION location_department(p_department_id departments.department_id%TYPE)
    RETURN t_dep_loc
  AS
    v_dep_loc t_dep_loc;
  begin
    select r.region_name, co.country_name, l.state_province, l.city
      into v_dep_loc.region_name, v_dep_loc.country_name, v_dep_loc.state, v_dep_loc.city
      from regions r
      join countries co on r.region_id = co.region_id
      join locations l on l.country_id = co.country_id
      join departments d on l.location_id = d.location_id
     where d.department_id = p_department_id;

    return v_dep_loc;
  end;

  FUNCTION manager_department(p_department_id departments.department_id%TYPE)
    RETURN t_dep_mgr
  AS
    v_dep_mgr t_dep_mgr;
  begin
    select e.first_name, e.last_name, e.email, e.phone_number
      into v_dep_mgr.first_name, v_dep_mgr.last_name, v_dep_mgr.email, v_dep_mgr.phone_number
      from employees e
      join departments d on e.employee_id = d.manager_id
     where d.department_id = p_department_id;

    return v_dep_mgr;
  end;
END;

I tried using / to mark an end of a PL/SQL block, but then I get the same error, but not from CREATE but from /.

This is the error:

Error(18,1): PLS-00103: Encountered the symbol "CREATE"

This is the first time i'm using the program and I'm using an example solution.


Solution

  • So you have made a package.

    The proper way to write a package body is to right click on the package name and click on 'create body'.

    Then if you want to run the package you first need to compile the package specification, and then the package body. Then you right click on the package and choose 'run'. There you can write the PL SQL block and then run it.