CODEWITHSUNDEEP
oracle-sqldeveloper
Mladen Oršolić
Mladen Oršolić

Reputation: 1422

Oracle SQL Developer removes schema name from package

So i have written :

create or replace package body schema_name.package_name

and as soon as i press compile, application removes schema name replacing it with white spaces like this :

create or replace package body             package_name

Does anyone know how to get rid of this annoying behavior?

Upvotes: 2

Views: 1915

Answers (2)

snydez
snydez

Reputation: 29

It does not retain the schema name if you compile/recompile. However, if you choose to export the package files through SQL Developer there is an option to include the schema and it will be added back in export.

Why it gets removed when in the editor space, Im not sure the logic behind that.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

It isn't SQL Developer doing that. If you create the package through SQL*Plus and then query dba_source (or the all/user variants) you'll see the name has been removed, along with the create or replace text:

SQL> create or replace package my_schema.package_name as
  2  end;
  3  /

Package created.

SQL> select text from sys.dba_source
  2  where type = 'PACKAGE' and owner = 'MY_SCHEMA' and name = 'PACKAGE_NAME'
  3  order by line;

TEXT
--------------------------------------------------------------------------------
package           package_name as
end;

2 rows selected.

The owner isn't part of the package source, it's stored separately in the data dictionary (dba_objects.owner etc.). The parser is removing the owner because it doesn't belong there, as part of the object that actually exists in the database. As pure, external source code it does (or may do anyway) and the version you have in source control can keep that if it's appropriate; but as part of the object defined in that schema it wouldn't mean anything.

You might also notice that if you try to create an object in another schema from the object viewer it tells you the module has been renamed and tries to reopen the object viewer.

It is interesting, though, that the owner name is substituted for spaces (including two for the double-quotes, if the schema name is quoted) rather than just being completely removed. And also interesting that SQL Developer's object viewer always puts the create or replace in - but also notice that it always includes the or replace, even if you originally didn't have that in your code. If it didn't then you wouldn't be able to recompile it, of course.

The dbms_metadata.get_ddl package re-adds the schema name by default, and quotes the identifiers; but also loses the extra whitespace in the process:

SQL> select dbms_metadata.get_ddl('PACKAGE', 'PACKAGE_NAME', 'MY_SCHEMA') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','PACKAGE_NAME','MY_SCHEMA')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "MY_SCHEMA"."PACKAGE_NAME" as
end;


1 row selected.

There doesn't seem to be any way for SQL Developer's object view to show or re-add the schema name though.

Upvotes: 5

Related Questions