OraFAQ Forum: Unix » Problem with space

Home » Infrastructure » Unix » Problem with space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with space [message #139353]

Tue, 27 September 2005 10:52

rajus19
Messages: 18
Registered: September 2005

Junior Member

Hi All,
I have a flat file contains some records, I have to add a header and trailer to that flat file and store that in a new file.
I am not aware of how to put the space using unix commands, like in RPAD('BILLS',10,' ') in Sql query.

The problem is that in header I have to use that date picked from the database and some other things.Here is the example what it should be in a query, but I don't know what unix command to use to do the same thing.
ex: select '00'|| to_char(sysdate,'YYYYMMDD')||RPAD('STORE',10,' ')||RPAD('BILLS',10,' ')||'004'
||RPAD(' ',59,' ') from dual;

And in the trailer I have to use the count of the records in the original file. Here is the example if the count is picked from a table, but I have to pick the count from flat file and use a unix command to put the trailer.
ex: select LPAD(count(*)+2,8,0)||'91'||LPAD(count(*),8,0)||LPAD(sum(TRN_AMOUNT),11,0)||LPAD(' ',56,' ')
from MyTable;

Could you please help me,
you will be greatly apprecialted.

Thanks
Raju

[Updated on: Tue, 27 September 2005 10:55]

Report message to a moderator

Re: Problem with space [message #139367 is a reply to message #139353]

Tue, 27 September 2005 12:25

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

generate_file is the script that takes two parameters
first one is name of flat file
second is name of the desired output file
You can make this script any fancier with less code.

oracle@mutation#generate_file emp.data another_emp.data
oracle@mutation#cat emp.data
 7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
 7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
 7566 JONES      MANAGER         7839 02-APR-81       2975                    20
 7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
 7839 KING       PRESIDENT            17-NOV-81       5000                    10
 7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
 7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
 7900 JAMES      CLERK           7698 03-DEC-81        950                    30
 7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
oracle@mutation#cat another_emp.data
header record from database followed by sysdate27-SEP-05
 7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
 7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
 7566 JONES      MANAGER         7839 02-APR-81       2975                    20
 7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
 7839 KING       PRESIDENT            17-NOV-81       5000                    10
 7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
 7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
 7900 JAMES      CLERK           7698 03-DEC-81        950                    30
 7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 is total count of lines excluding header and tail and is tail record
oracle@mutation#cat generate_file
#!/usr/bin/bash
header=`sqlplus -s scott/tiger <<EOF
set head off
select 'header record from database followed by sysdate'||sysdate from dual;
exit;
EOF`
line_cnt=`cat $1 | wc -l `
echo $header >$2
cat emp.data >> $2
echo $line_cnt is total count of lines excluding header and tail and is tail record >> $2

Report message to a moderator

Re: Problem with space [message #139464 is a reply to message #139353]

Wed, 28 September 2005 02:46

rajus19
Messages: 18
Registered: September 2005

Junior Member

Thanks for the help.

For header its ok, but for trailer I need to use the count of records in the flat file.

If my source is the table I can use the query, but the source is a flat file, I am not sure what command to use for the trailer.

Thanks in Advance
Raju

Report message to a moderator

Re: Problem with space [message #139524 is a reply to message #139464]

Wed, 28 September 2005 08:27

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I dont understand what your problem is?

#!/usr/bin/bash
header=`sqlplus -s scott/tiger <<EOF
set head off
select 'header record from database followed by sysdate'||sysdate from dual;
exit;
EOF`
line_cnt=`cat $1 | wc -l `
echo $header >$2
cat emp.data >> $2
echo `expr $line_cnt + 2` is total count of lines including header and tail and is tail record >> $2

line_cnt=`cat $1 | wc -l `=14 #If line_count_from_flatfile is 14
then
14+2 records should be 14 records +1 header+1 tail.

Report message to a moderator

Re: Problem with space [message #139645 is a reply to message #139524]

Thu, 29 September 2005 01:09

rajus19
Messages: 18
Registered: September 2005

Junior Member

hey sory for not explaining my problem properly.

The problem is I need help in writing trailer record.
ex: record_count=`cat $1 | wc -l ` (say 14 )
and if total_amount is 1500.

I have to write the trailer format as

Field---------Length---Format--Description
Account Type----2-------Constant:15
Record count----8--------N-------Count of all records in file.
Totol Amount-----11-------N------Sum total of all transactions
Filler----------------71-----------AN----------Spaces

Now I need a unix command to wirte the trailer record.

Thanks
Raju

[Updated on: Thu, 29 September 2005 01:57]

Report message to a moderator

Previous Topic:	shell scripting
Next Topic:	server alerts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 19:50:44 CST 2024