Shell Programming and Scripting

Hi

I am writng a script to spool some account nos information to .txt file but wants that information for each and every account no to be written in a different .txt file with account no as part of the file name of its associated file.

Thanks

Hi ss_ss,

What are the other contents of the file (source file)?

You may want to include more details about your problem so that it would be easier for others to help

Hi,

I've written a query whose output is:


D A/C No Mob NO Elem ID Profile ID

D 44083669 0192652286 10866 1

D 44083455 0192300318 10867 1

Now i want to create 2 files containing 1 row each and their names should be like for 1st row File1_44083669_<YYYYMMDDHHMISS>.txt
and for 2nd row File2_44083455_<YYYYMMDDHHMISS>.txt

And in those files column header should not come.

Thanks

Assuming your source file is tab delimited...

Code:

SRCFILE="srcfile.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
        ((COUNT=COUNT+1))
        OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
        echo "$X" > $OUTFILE
done

Just update the SRCFILE variable. Also, there are other ways you can do this like using cut, sed, etc...So if you want a different solution, you can use those commands also

Thnx a lot for ur help


But after that also im getting some errors like:

SQL> SQL> SQL> SQL> SP2-0734: unknown command beginning "SRCFILE="T..." - rest of line ignored.
SQL> SP2-0042: unknown command "COUNT=0" - rest of line ignored.
SQL> SP2-0734: unknown command beginning "sed '1d' ..." - rest of line ignored.
SQL> 2 3 4 SQL> SP2-0042: unknown command "done" - rest of line ignored.

I am putting the whole code can you just look into:

#!/bin/sh

sqlplus uname/pwd@dbname <<EOF

set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000

spool /SYSTEM/custom/modules/testing/THS_PROF.txt

select 'D' "D", ciam.external_id "Kenan Account No", ciem.external_id "Mobile MSISDN", p.element_id "Element Id", case
when (p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id)) then '1'
else '200'
end "Ths Profile Id"
from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
where ciam.account_no(+)=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=c.account_no
and c.account_no=ciam.account_no
and c.bill_period='G24'
and ciem.external_id_type=1
and ciam.external_id_type=1
and ciem.external_id_type=ciam.external_id_type
and p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id);

spool off;

SRCFILE="THS_PROF.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
((COUNT=COUNT+1))
OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE
done

quit

EOF

Hi ss,

I wasn't aware that you'll be using it with sqlplus. Try inserting the script after the 'EOF' token.

The whole script will look like this:

Code:

sqlplus uname/pwd@dbname << EOF

set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000

spool /SYSTEM/custom/modules/testing/THS_PROF.txt

select 'D' "D", ciam.external_id "Kenan Account No", ciem.external_id "Mobile MSISDN", p.element_id "Element Id", case
when (p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id)) then '1'
else '200'
end "Ths Profile Id"
from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
where ciam.account_no(+)=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=c.account_no
and c.account_no=ciam.account_no
and c.bill_period='G24'
and ciem.external_id_type=1
and ciam.external_id_type=1
and ciem.external_id_type=ciam.external_id_type
and p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id);

spool off;

done

quit

EOF 

SRCFILE="THS_PROF.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
((COUNT=COUNT+1))
OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE

Another solution would be to use a t-sql looping inside the sql segment of the code but you'll have to redefine your output for each loop. But I'm not much familiar with oracle (or is it not oracle?)

Try the modified one

Thanx it worked just done needs to be put at the end.

But need to ask you 1 more thing that is, as i metioned if the output of the query is 3 rows then i want 3 different files with a\c no in the name of the file for each of them containing their respective values.