PLSQL Types Creation

PLSQL Types Creation

create or replace type department_rec as object
  ( id number(4,0)
  , name varchar2(30)
  );

  create or replace type department_tab as table of department_rec;


create or replace
procedure check_deps
  ( p_deps in out department_tab )
is
begin
  if p_deps is null or p_deps.count = 0
  then
    return;
  end if;
 
  for i in p_deps.first..p_deps.last
  loop
    begin
      select department_name into p_deps(i).name
      from departments
      where department_id = p_deps(i).id;
    exception
      when no_data_found then
        null; -- Just skip it.
    end;
  end loop;
end;

==================

CREATE OR REPLACE TYPE emp_rec_type AS OBJECT (
    employee_id     NUMBER(18),
    first_name      VARCHAR2(30),
    last_name       VARCHAR2(30),
    middle_name     VARCHAR2(30),
    employee_type   VARCHAR2(30),
    location        VARCHAR2(30),
    addr_line1      VARCHAR2(30),
    addr_line2      VARCHAR2(30),
    addr_line3      VARCHAR2(30),
    city            VARCHAR2(30)
);


CREATE OR REPLACE TYPE emp_tbl_type AS TABLE OF emp_rec_type;


CREATE OR REPLACE PACKAGE emp_ddl_package AS
   PROCEDURE validate_emp_records (
      emp_rec_list        IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2   
    );
END emp_ddl_package;

CREATE OR REPLACE PACKAGE BODY emp_ddl_package AS

  PROCEDURE validate_emp_records(
      emp_rec_list IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2
  )IS 
  BEGIN
         dbms_output.put_line('Welcome to Validate_emp_records');
  END validate_emp_records;
END emp_ddl_package;