Home » Infrastructure » Unix » Oracle table to a file in Unix
Oracle table to a file in Unix [message #133092] Wed, 17 August 2005 12:33 Go to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
hi..
i am trying to get the oracle table values into a file in unix so that i can parse it.
the scripts is below:

PID=$$
rc=`sqlplus -s "user"/"pwd"@"dblink" << EOF > ora2unx.out.$PID
set serveroutput off
set verify off
set feedback off
set timing off pagesize 0 heading off tab off
SELECT col1 || chr(9) || col2 || chr(9) || col3 || chr(9) || col4 FROM table1;
/
quit;
EOF`

then i parse it like this:

cat ora2unx.out.13771 | awk -F\t '{ printf("%s %s %s %s\n",$1,$2,$3,$4) }' | while read COL1 COL2 COL3 COL4
do
echo ${COL1}
echo ${COL2}
echo ${COL3}
echo ${COL4}
done

the output is like this

1
54569535304
METFORMIN
HCL TABLET
1
60951070370
OXYCODONE
HCL SUSTAINED RELEASE TABLET
1
60505023501
TORSEMIDE
TABLET
1
00172635660
OXYCODONE
HCL SUSTAINED RELEASE TABLET
1
00409405503
CLINDAMYCIN
PHOSPHATE VIALS INJECTABLE
1
00172435600
FLUOXETINE
HCL CAPSULE




when it should have been like this.

1
54569535304
METFORMIN HCL
TABLET
1
60951070370
OXYCODONE HCL
SUSTAINED RELEASE TABLET
1
60505023501
TORSEMIDE
TABLET
1
00172635660
OXYCODONE HCL
SUSTAINED RELEASE TABLET
1
00409405503
LINDAMYCIN PHOSPHATE
VIALS INJECTABLE
1
00172435600
FLUOXETINE HCL
CAPSULE


the difference is that if col3 has a space it is coming in col4.
pls revert incase you need more details.
how do i circumvent this.
the idea was to separate the fields in the sql plus by a tab - chr(9).

Thanks,
Pavan.
Re: Oracle table to a file in Unix [message #133098 is a reply to message #133092] Wed, 17 August 2005 13:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Why do you need a tab?( and then awk?)
To get an output you want, this would do.
correct me if i am wrong.
scott@9i > select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNT ING    NEW YORK
        20 >RESEARCH      DALLAS
        30 SALES          CHICAGO
        40 >OPERATIONS    BOSTON

scott@9i > select deptno||chr(10)||dname||chr(10)||loc from dept;

DEPTNO||CHR(10)||DNAME||CHR(10)||LOC
---------------------------------------------------------------------
10
ACCOUNT ING
NEW YORK

20
>RESEARCH
DALLAS

30
SALES
CHICAGO

40
>OPERATIONS
BOSTON
Re: Oracle table to a file in Unix [message #133111 is a reply to message #133098] Wed, 17 August 2005 14:13 Go to previous messageGo to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
The idea was to have it (the whole row) in a line separated by a character(either a tab or any other).
i echoed it in each line only for testing.
how do i separate each field by a comma or a tab and then read each field one after the other?
that is where i got this error. if the field3 has a space, then it is being added to field4 as shown before in the first message.

the idea was to have a character sepatrated file so that i can sql load it to another table.

the script i have used is fine but for the space error mentioned above.
pls suggest.
thanks,
pavan
Re: Oracle table to a file in Unix [message #133117 is a reply to message #133111] Wed, 17 August 2005 14:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
--
-- to generate csv file for sql*loader
-- Beware of nulls,
--


scott@9i >  select dname||','||deptno||','||loc from dept;

DNAME||','||DEPTNO||','||LOC
---------------------------------------------------------------------
ACCOUNTING,10,NEW YORK
RESEARCH,20,DALLAS
SALES,30,CHICAGO
OPERATIONS,40,BOSTON

oracle@mutation#cat a.lst | awk -F\, '{ print $1,$2,$3,$4 }' | while read C1 C2 C3; do echo ${C1}; echo ${C2}; echo ${C3}; done
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON

----------------------------
-- Even in your case (tab seperated), your awk script is wrong. use this.
-----------------------------
oracle@mutation#cat a.lst | awk -F\t '{ print $1,$2,$3,$4 }' | while read C1 C2 C3
> do
> echo ${C1}
> echo ${C2}
> echo ${C3}
> done
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
Re: Oracle table to a file in Unix [message #133351 is a reply to message #133117] Thu, 18 August 2005 10:35 Go to previous messageGo to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
yup...
the script is good. but what if chicago has a space in between...like
chi cago
then it wouldnt work i guess. Am still having the same problem.
if teher is a space in one of the fileds then it messes it up.
what do i do???
i used cut instead of awk to solve it but it would be great if i can get the script using awk.
thanks,
pavan
Re: Oracle table to a file in Unix [message #133353 is a reply to message #133351] Thu, 18 August 2005 10:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Still works for me.
oracle@mutation#cat dept.lst | awk -F\t '{ print $1,$2,$3'}
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHI CAGO
40      OPERATIONS      BOSTON
oracle@mutation#cat dept.lst | awk -F\t '{ print $1,$2,$3'} | while read C1 C2 C3 ; do echo ${C1}; echo ${C2}; echo ${C3}; done
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHI CAGO
40
OPERATIONS
BOSTON
Re: Oracle table to a file in Unix [message #133367 is a reply to message #133092] Thu, 18 August 2005 12:39 Go to previous messageGo to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
Thanks Mahesh,
But still..... this is the script i am using along with the output.
It still gives me the same problem.


script1.ksh

PID=$$
rc=`/oracle/app/oracle/product/9.2.0.4/bin/\
sqlplus -s "user"/"pwd"@"dblink" << EOF > ora2unx.out.$PID
set serveroutput off
set verify off
set feedback off
set timing off pagesize 0 heading off tab off
SELECT SRCE_PROD_DATA_SUPLR_ID || chr(9) || srce_ndc_cd || chr(9) || srce_brnd_desc || chr(9) || srce_fm3_desc FROM SRCE_PROD WHERE data_load_dt = '01-AUG-2005' and srce_prod_data_suplr_id = 1;
/
quit;
EOF`

The above generates the tab separated file.

the output file is ora2unx.out.15331 which is:

1 54868385303 NORVASC TABLET
1 58016070630 PROCHLORPERAZINE MALEATE TABLET
1 58016032099 GABAPENTIN TABLET
1 54868126202 OGEN TABLET
1 58016070603 PROCHLORPERAZINE MALEATE TABLET
1 58016031299 GABAPENTIN TABLET
1 58016070660 PROCHLORPERAZINE MALEATE TABLET
1 58016070690 PROCHLORPERAZINE MALEATE TABLET
1 58016070600 PROCHLORPERAZINE MALEATE TABLET
1 58016070602 PROCHLORPERAZINE MALEATE TABLET




xyz.ksh


cat ora2unx.out.15331 | awk -F\t '{ print $1,$2,$3,$4'} | while read C1 C2 C3 C4 ; do echo ${C1}; echo ${C2}; echo ${C3}; echo $(C4); done

the output i get is this:

1
54868385303
NORVASC
./xyz: C4: not found

1
58016070630
PROCHLORPERAZINE
./xyz: C4: not found

1
58016032099
GABAPENTIN
./xyz: C4: not found

1
54868126202
OGEN
./xyz: C4: not found

1
58016070603
PROCHLORPERAZINE
./xyz: C4: not found

1
58016031299
GABAPENTIN
./xyz: C4: not found

1
58016070660
PROCHLORPERAZINE
./xyz: C4: not found

1
58016070690
PROCHLORPERAZINE
./xyz: C4: not found

1
58016070600
PROCHLORPERAZINE
./xyz: C4: not found

1
58016070602
PROCHLORPERAZINE
./xyz: C4: not found


where could it go wrong? I am not able to resolve this.

Thanks,
Pavan


Re: Oracle table to a file in Unix [message #133381 is a reply to message #133367] Thu, 18 August 2005 15:54 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
We have been workin on different set of data.
That is why i can get the result.
Only If the last column has space between records, awk will not complain about it.
So realign it as shown!.
By default awk considers 'space' as delimiter
so do not use a tab delimted file or use sed to replace that 'space' to something intermitttent.
oracle@mutation#b
Originial alignment


    DEPTNO LOC           DNAME
---------- ------------- --------------
        10 NEW YORK      ACCOUNTING
        20 DALLAS        RESEARCH
        30 CHI CAGO      SALES
        40 BOSTON        OPERATIONS

re-align during the select

10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHI CAGO
40      OPERATIONS      BOSTON
display as you like
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHI CAGO
40
OPERATIONS
BOSTON
oracle@mutation#cat b
sqlplus -s scott/tiger <<EOF
prompt Originial alignment
prompt
select * from dept;
Prompt re-align during the select
prompt
set feed off
set pagesize 0
set head off
spool a.lst
select deptno||chr(9)||dname||chr(9)||loc from dept;
spool off;
exit;
EOF
echo display as you like
cat a.lst | awk -F'\t'  '{print $1,$3,$2 }' | while read C1 C2 C3 ; do echo ${C1}; echo ${C2}; echo ${C3}; done
Previous Topic: Print queue problem
Next Topic: sed .. inserts newline and spaces...
Goto Forum:
  


Current Time: Fri Mar 29 01:32:17 CDT 2024