创建package样例

create or replace procedure package_procedure
AS
i_count integer;
begin
select count(*) into i_count from user_objects t where t.OBJECT_TYPE ='PACKAGE' and t.OBJECT_NAME = upper('package_name');
    if i_count>0 then
        EXECUTE IMMEDIATE 'drop package package_name';
    end if;
end package_procedure;
/
call package_procedure();
drop procedure package_procedure;

CREATE OR REPLACE PACKAGE package_name IS

  TYPE timearraytyp IS TABLE OF DATE;

  PROCEDURE log_error_to_table(v_error_code IN NUMBER,
                               v_error_desc IN VARCHAR2,
                               v_sql        IN VARCHAR2);

END package_name;
/

CREATE OR REPLACE PACKAGE BODY package_name IS
  PROCEDURE log_error_to_table(v_error_code IN NUMBER,
                               v_error_desc IN VARCHAR2,
                               v_sql        IN VARCHAR2) IS
    v_error      VARCHAR2(200);
    v_error_text VARCHAR2(800);
    v_record_sql VARCHAR2(2048);
  BEGIN
    v_error      := 'Error code is:' || v_error_code;
    v_error_text := substr(v_error_desc, 1, 500);
    v_record_sql := substr(v_sql, 1, 2000);
    INSERT INTO t_error_debug
    VALUES
      (SYSDATE, v_error, v_error_text, v_record_sql);
    COMMIT;
  END log_error_to_table;
END package_name;
/