IMP/EXP with a named pipe plus execute PL/SQL with SELECT

Here are two TOP TIPS!

When you lack the bits and bytes at your old server and you need to dough out your data, here's a pre-historical recipe that helped lots of people through out the ages of long obsolete Oracle versions, some of them still out there:

Creating a compressed export file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mknod /tmp/exp_pipe p # Make the pipe 
nohup compress < /tmp/exp_pipe > /db02/oradata/exp/myschema.dmp.Z & # Background compress 
nohup exp lmc/cml file=/tmp/exp_pipe log=/db02/oradata/exp/myschema.log owner=myschema & # Export to the pipe

Reading a compressed export file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

% mknod /tmp/imp_pipe p # Make the pipe 
% uncompress < export.dmp.Z > /tmp/imp_pipe & # Background uncompress 
% imp file=/tmp/imp_pipe <other options> # Import from the pipe

The second tip is to help you execute a procedure from a simple SELECT statement: also a TOP TIP!



This is a long time problem that people face which can be solved with the following ingredients:



1) Permanent NESTED TABLE type

2) Function that returns that type

3) Procedure with a OUT parameter telling the result of execution

4) Making the function call that procedure collecting the OUT parameter result into the NESTED TABLE first "record"

5) Creating a view that queries the function casting out the NESTED TABLE result into a real relational table



Now just select from the view and the view will bring you a setence that is the OUT parameter of the procedure executed.