Amardeep Sidhu's Oracle blog

On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don’t have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
do
echo "spooling $a"
sqlplus username/password@string <<EOF
set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
EOF
done

I didn’t see any work around for Windoze as SQLPLUS << EOF thing doesn’t seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.

Sidhu

An interesing post by Laurent. Check out http://laurentschneider.com/wordpress/2007/06/to-divide-or-to-multiply.html

My findings on 10gR2 on Windoze XP

SQL> var z numberSQL> var y numberSQL> exec :z := power(2,102)*2e-31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10SQL> set timi offSQL> print y

      Y----------9.988E-126

SQL> exec :z := power(2,-104)*2e31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04SQL> set timi offSQL> print y

      Y----------9.988E-126

SQL>

Sidhu

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on
spool onspool c:\salgrade.xls
select * from salgrade;
spool offset markup html off
spool off

And the xls it makes shows up like:

Sidhu

A very nice series of articles by Howard Rogers about Oracle Concepts. It includes all the basics like what a database, instance is ? Various types of files and all basic stuff. Read it here

Sidhu

Well, a simple method to import(export) directly to(from) compressed files using pipes. Its for Unix based systems only, as I am not aware of any pipe type functionality in Windows. The biggest advantage is that you can save lots of space as uncompressing a file makes it almost 5 times or more. (Suppose you are uncompressing a file of 20 GB, it will make 100 GB) As a newbie I faced this problem, so thought about writing a post.

Lets talk about export first. The method used is that create a pipe, write to a pipe(ie the file in exp command is the pipe we created), side by side read the contents of pipe, compress (in the background) and redirect to a file. Here is the script that achieves this:

export ORACLE_SID=MYDB
rm -f ?/myexport.pipe
mkfifo ?/myexport.pipe
cat ?/myexport.pipe |compress > ?/myexport.dmp.Z &
sleep 5
exp file=?/myexport.pipe full=Y log=myexport.log

Same way for import, we create a pipe, zcat from the dmp.Z file, redirect it to the pipe and then read from pipe:

export ORACLE_SID=MYDB
rm -f ?/myimport.pipe
mkfifo ?/myimport.pipe
zcat ?/myexport.dmp.Z > ?/myimport.pipe &
sleep 5
imp file=myimport.pipe full=Y show=Y log=?/myimport.log

In case there is any issue with the script, do let me know

Update: If you are on Wintel, you can directly use a compressed folder as an export target. No need to create a pipe as the file system will automatically do it for you. ( Thanks Noons for the tip)

If you are using gunzip:

For export:

export ORACLE_SID=MYDB
rm -f exp.pipe
mknod exp.pipe
gzip < exp.pipe > T1.dmp.gz &amp;amp;
exp file=exp.pipe full=Y log=myexport.log

For import:

export ORACLE_SID=MYDB
rm -f imp.pipe
mknod imp.pipe
gzip < T1.dmp.gz > imp.pipe &amp;amp;
imp file=imp.pipe full=Y log=myimport.log

Update: I came across an article that discusses few ways to achieve the same on Windows. Check it here.