Created
June 23, 2011 10:11
-
-
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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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