fixed length sqlplus spool file for loading data

If you are in unfortunate situation where you need to salvage data from sqlplus spool file, I think my script below will help.

You have a sqlplus spool file which looks like below. Columns list is on 4th line.

$ head table1.spool
SQL> select * from 
  2  table1;

COUNTRY          ADDRESS                 SID TYPE        ID1        ID2
---------------- ---------------- ---------- ---- ---------- ----------
japan            aoyama                    3 a             b          x

Save below as 'plusSpool.sh'

#!/bin/bash
mkdir out.$$
sed -n "${2}p" $1 > /tmp/_columns.$$
sed -n "$(($2+1))p" $1 > /tmp/_seperator.$$
sed "1,$(($2+1))d;/^$(cat /tmp/_columns.$$)$/d;/^$(cat /tmp/_seperator.$$)$/d" $1 > /tmp/_rows.$$
pos=0
for i in $(cat /tmp/_columns.$$)
do
  echo -n "$i "
  echo -n "$(($pos+1))-"
  pos=$(grep -Eo "^.{$pos} {0,1}-+" /tmp/_seperator.$$|wc -c|tr -d ' ')
  echo $pos
done |
while read colname posrange
do
  sed '/^$/d' /tmp/_rows.$$ | cut -c$posrange > out.$$/$colname
done

Run this with 2 args. 1st arg is filename and 2nd arg is the line# into the file where column list appears.

$ ./plusSpool.sh table1.spool 4

You can recycle line#1 to line#13 to create SQL*Loader(sqlldr) control file, for example.
In my script above from line#14 and below, I saved each column in seperate files.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today