How import/export package body and spec into .sql or .pkb file

Question

I need to import/export a package body/spec into a .sql or .pkb file using only sql plus commands.

I tried to execute the standard select but this only display it in the console, but I need the file to modify.

Also I need to do the opposite, that is to import a .sql/.pks/.pkb file to the database in order to apply changes.

Answer 1

If you want to do that using SQL*Plus, then - as you were told - spool seems to be a natural option. Here's an example, see whether it helps.

First, I'll create a simple package:

SQL> create or replace package pkg_test as
  2    function f_today return date;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2    function f_today return date is
  3    begin
  4      return sysdate;
  5    end;
  6  end;
  7  /

Package body created.

SQL> select pkg_test.f_today from dual;

F_TODAY
-------------------
02.09.2019 22:28:56

SQL>

In order to create a nice output file:

• there are some SQL*Plus settings that should be set
• as you want to export a package, we'll query user_source
• it doesn't contain create (or replace) so I'll select it separately
• the same goes for the terminating slash /

All that will be stored in a .SQL file. If you run those commands directly, the export file will contain select statements as well and that's something you'd want to avoid.

Spool.sql file:

set heading off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on
set recsep off
set linesize 120

spool pkg_test.sql

select 'create or replace' from dual;
select text from user_source
  where name = 'PKG_TEST'
    and type = 'PACKAGE'
order by line;
select '/' from dual;

select 'create or replace' from dual;
select text from user_source
  where name = 'PKG_TEST'
    and type = 'PACKAGE BODY'
order by line;
select '/' from dual;

spool off;

Let's run it; because of all those SET commands, you won't actually see anything; the SQL> prompt will be all, as if nothing happened:

SQL> @spool
SQL>

But, if you check what's written in pkg_test.sql file, you'll see the package:

SQL> $type pkg_test.sql
create or replace
package pkg_test as
  function f_today return date;
end;
/
create or replace
package body pkg_test as
  function f_today return date is
  begin
    return sysdate;
  end;
end;
/

SQL>

Looks OK, so - to answer your second question (how to import it back) - just run it. I'll exit SQL*Plus first; otherwise - again because of SET commands - you won't see anything:

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\Users\lf>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Pon Ruj 2 22:36:06 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @pkg_test

Package created.


Package body created.

SQL>

Answer 2

For exporting tables exp <username>/<password> file=tables.dmp tables=(tab1,tab2)

For importing tables imp <username>/<password> file=tables.dmp tables=(tab1,tab2)

For printing onscreen output in a file simply use

spool <filename> before all your commands to modify your sql outputs as report and spool off after sql commands as

 Spool file.txt
 SELECT * FROM TABLE
 SPOOL OFF