Monthly Archives: June 2007

Shell script to spool a no of tables into .xls files…

2 Replies

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

Is multiplication faster than division ?

Leave a reply

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 number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> set timi off
SQL> 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 on
SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

PL/SQL procedure successfully completed.

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

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

SQL>

Sidhu

IMPORT & EXPORT from/to compressed files directly…

5 Replies

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 &amp;amp;
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 &amp;amp;
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.