Skip to content

Instantly share code, notes, and snippets.

@manadart
Created June 23, 2011 10:11
Show Gist options
  • Save manadart/1042301 to your computer and use it in GitHub Desktop.
Save manadart/1042301 to your computer and use it in GitHub Desktop.
Oracle throws "ORA-21700: object does not exist or is marked for delete" when referencing a type declared inside a package, externally.
-- This is the top few lines of my package header, showing the type declarations.
create or replace package aims_migration as
type r_aims_wo_item is record(ref_identifier varchar(50), geometry sdo_geometry);
type t_aims_wo_item is table of r_aims_wo_item;
-- Rest of the package spec...
end aims_migration
-- This is a procedure from my testing package.
-- This throws the ORA-21700 error.
procedure ut_populate_wo_item as
ids_to_migrate aims_migration.t_aims_wo_item;
data_table varchar2(40);
imported_identifier wo_item.ref_identifier%type;
begin
data_table := 'MAPINFO.FS_POLE';
ids_to_migrate := aims_migration.get_records_for_import(data_table);
aims_migration.populate_wo_item(99, data_table, ids_to_migrate); -- Passing ids_to_migrate back in here wrecks the show.
select ref_identifier into imported_identifier from wo_item;
utassert.eq('Standard Success Case', imported_identifier, 'P_GTLT02080_20110430_151722');
end ut_populate_wo_item;
-- We need to declare the types externally to the package.
-- This is just done from the SQL prompt, or a script.
-- Note: If I was re-creating these, I would not be able to mod r_aims_wo_item because the t_aims_wo_item is based on it.
-- I would have to drop t_aims_wo_item first.
create or replace type r_aims_wo_item is object(ref_identifier varchar(50), geometry sdo_geometry);
create or replace type t_aims_wo_item is table of r_aims_wo_item;
-- The type used is now the globally declared one.
-- The types are removed altogether from the package header in the first file.
procedure ut_populate_wo_item as
ids_to_migrate fmc3.t_aims_wo_item;
-- Rest of the declarations...
begin
-- Same body as above...
end ut_populate_wo_item;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment