CODEWITHSUNDEEP
oracle-database
BoomRamada
BoomRamada

Reputation: 141

Export or Import packages from other users

I'm having issues export or import a package (including the body) from other user (User that not connect to) through command line. What is the best way to fix this. I can view the package via Oracle SQL Developer, within Other user package. But I want to perform this in command line.

So far I'm try to export the package, I have try using following command, unfortunately this not export packages from other users.

SET HEAD OFF
SET ECHO OFF
SET FEED OFF
SET TERM OFF
SET LINE 1500
SET NEWPAGE NONE
set pagesize 0
SPOOL C:\app\export\TEST.SQL
PROMPT CREATE OR REPLACE
select trim(text) from user_source
where name='USR_RUNNER' AND type='PACKAGE';
SPOOL OFF

Above will only get packages from current user, not from other users.

Upvotes: 0

Views: 573

Answers (2)

Jon Heller
Jon Heller

Reputation: 36902

Use ALL_OBJECTS instead of USER_SOURCE to view results for all schemas that your user has access to. You also probably want to use DBMS_METADATA.GET_DDL instead of trying to create the DDL yourself with the data dictionary. Recreating objects can be extremely difficult and DBMS_METADATA is the best way to guarantee that you capture the objects correctly.

SET HEAD OFF
SET ECHO OFF
SET FEED OFF
SET TERM OFF
SET LINE 1500
SET NEWPAGE NONE
set pagesize 0
SET LONG 999999999
SPOOL C:\temp\TEST.SQL
select dbms_metadata.get_ddl('PACKAGE', object_name, owner) ddl
from all_objects
where owner = 'SOME_USERNAME'
    and object_type = 'PACKAGE'
order by owner, object_name;
SPOOL OFF

Run the below PL/SQL block in the session if you want to export the code without the schema names. This will allow you to import the script into a different user.

begin
    dbms_metadata.set_transform_param
    (
        dbms_metadata.session_transform, 'EMIT_SCHEMA', false
    );
end;
/

Upvotes: 1

eifla001
eifla001

Reputation: 1157

You are using USER_SOURCE view in your query, use ALL_SOURCE or DBA_SOURCE instead. There is an OWNER column of those views. You can use it in your query's filter.

Upvotes: 0

Related Questions