d27m11y d27m11y
asked on
unix shell with a parameter
Here is my shell below. I am trying to basically fetch .csv file using ftp to get .csv into a directory. Name of the file could be possibly like
missingtrades_20130530.csv or
missingtrades_20130531.csv
Can I pass these .csv files as parameters since the file name is varying, how do I run the shell passing the parameters. Please help, this is kind of urgent
missingtrades_20130530.csv
missingtrades_20130531.csv
Can I pass these .csv files as parameters since the file name is varying, how do I run the shell passing the parameters. Please help, this is kind of urgent
#!/bin/ksh -xve
date
HOME_DIR=$tradehome/ods
#DOWNLOAD_FILE_NAME=test_0523.csv
DOWNLOAD_FILE_NAME=$1.csv
DATA_FILE_DIR=$HOME_DIR/data/csv
DATA_FILE=$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
dscfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.dsc
badfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.bad
#ftplog = $HOME_DIR/log/ftp_get_msg_data.log
remote=cnyopdsuat
USER_NAME=opdu
PASSWORD=snp500
export USER_NAME PASSWORD remote ftplog DATA_FILE_NAME
until
[ -f "$DATA_FILE" ]
do
sleep 2
date
echo 'Attempt to get $DATA_FILE from $remote' `date`
cd $DATA_FILE_DIR
echo "open $remote
user $USER_NAME $PASSWORD
verbose
asc
cd pranathi
get $DOWNLOAD_FILE_NAME
quit" | ftp -in > $ftplog 2>&1
done
date
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is kind of urgent issue, kindly respond.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wonderful, thank you for your help. It works perfect
I tried to execute as
stg_citco_intraday_trade_v 1.sh Citco_missingtrades_201305 30
One last question, what if I wanted to execute as
stg_citco_intraday_trade_v 1.sh 20130530, I mean to say if the parameter I wanted to pass is only the date and the file could be
Citco_missingtrades_201305 30.csv
Citco_missingtrades_201305 23.csv
Will there be change in shell and ctl file. Am attaching the control file and shell with your changes. Can you suggest the changes in these two if only date is passed as parameter.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2sh.txt
I tried to execute as
stg_citco_intraday_trade_v
One last question, what if I wanted to execute as
stg_citco_intraday_trade_v
Citco_missingtrades_201305
Citco_missingtrades_201305
Will there be change in shell and ctl file. Am attaching the control file and shell with your changes. Can you suggest the changes in these two if only date is passed as parameter.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2sh.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help., simply wonderful.
I see that records inserted into table in control file- stg_citco_intraday_trade gets inserted as per sequence of the inputfile like
Eg, if input file is Citco_missingtrades_201305 30
2013053000001
2013053000002
2013053000003 etc.,
and if input file is Citco_missingtrades_201305 23
2013052300001
2013052300002
2013052300003 etc.,
What if I wanted to be in the continous sequence like
2013053000001
2013053000002
2013053000003
2013052300004
2013052300005
2013052300006
Please suggest.
I see that records inserted into table in control file- stg_citco_intraday_trade gets inserted as per sequence of the inputfile like
Eg, if input file is Citco_missingtrades_201305
2013053000001
2013053000002
2013053000003 etc.,
and if input file is Citco_missingtrades_201305
2013052300001
2013052300002
2013052300003 etc.,
What if I wanted to be in the continous sequence like
2013053000001
2013053000002
2013053000003
2013052300004
2013052300005
2013052300006
Please suggest.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to make changes as suggested. Attached are the control, shell files but I get strange results. I also attached the .csv file which I used for execution.
here is my execution script
Here is the sequence number I get for all the records
2.0130523E17
I did attach the result in the table and you can see the sequence number. I defined the datatype for stg_sequence number in the table initially as number, now I changed it to Number(38).. still, it gives me the same result.
Please suggest.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2-sh.txt
CITCO-MISSING-TRADES-20130523.csv
Resultset-001.xls
here is my execution script
stg_citco_intraday_trade_v2.sh 20130523
Here is the sequence number I get for all the records
2.0130523E17
I did attach the result in the table and you can see the sequence number. I defined the datatype for stg_sequence number in the table initially as number, now I changed it to Number(38).. still, it gives me the same result.
Please suggest.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2-sh.txt
CITCO-MISSING-TRADES-20130523.csv
Resultset-001.xls
ASKER
since I am executing thru shell script, I am unable to determine where to set NUM 23.
In excel, I tried setting the datatype to number and precision to '0' and it did not work.
Kindly suggest.
In excel, I tried setting the datatype to number and precision to '0' and it did not work.
Kindly suggest.
You set the number to 23 digits in the utility you use to generate/display your report ( SQL*Plus?). The values are correct in the database.
And as also I mentioned, excel seems to only handle 18 significant digits.
Why not add a "load" date column to the table and thus you need 8 characters less for the "STG_SEQUENCE_NUMBER"?
And as also I mentioned, excel seems to only handle 18 significant digits.
Why not add a "load" date column to the table and thus you need 8 characters less for the "STG_SEQUENCE_NUMBER"?
ASKER
Thank you for your reply. I am still struggling to get the right sequence number. As I said before, I am loading the table stg_citco_intraday_trade thru sqlloader. Can I set number to 23 digits in my control file. Attached is my control file.
or is there anyway that I can change my shell to set the number to 23.
Please suggest.
stg-citco-intraday-trade-ctl.txt
or is there anyway that I can change my shell to set the number to 23.
Please suggest.
stg-citco-intraday-trade-ctl.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when I change the number in my shell script, it works fine.
STG_SEQUENCE_NUMBER="${1}0 0000"
STG_SEQUENCE_NUMBER="${1}0
ASKER
However, thank you so much for your wonderful help!
ASKER
Excellent and very beneficial, useful for unix beginners who use sqlloader as part of their shell script.
Be aware that "${1}00000" will only give you 99,999 sequences before it will add +1 to the date.
;)
;)
ASKER
that is my only concern. If the sequence exceeds "${1}00000" i.e 201305300000 --2013053099999.. what could be my next step to prevent this ?
Add a "load" date column to the table and thus you remove 8 characters from the "STG_SEQUENCE_NUMBER".
ALTER TABLE stg_citco_intraday_trade ADD (load_date DATE);
Change the shell script:. . . E t c . . .
DOWNLOAD_FILE_NAME="Citco_missingtrades_$1.csv"
LOAD_DATE="${1}"
. . . E t c . . .
sed "s/%LOAD_DATE%/$LOAD_DATE/" $tradehome/ods/ctl/stg_citco_intraday_trade_ctl.tmp > $tradehome/ods/ctl/stg_citco_intraday_trade.ctl
. . . E t c . . .
change the control file:. . . E t c . . .
trailing nullcols
(
STG_SEQUENCE_NUMBER EXPRESSION "stg_sequence.nextval",
ORD_STATUS "TRIM (:ORD_STATUS)",
. . . E t c . . .
MX_ORIGINAL_INB "TRIM (:MX_ORIGINAL_INB)",
TAKE_EXP_FLAG "TRIM (:TAKE_EXP_FLAG)",
TRADER_NOTES "TRIM (:TRADER_NOTES)",
LOAD_DATE EXPRESSION "TO_DATE('%LOAD_DATE%', 'YYYYMMDD')",
)
ASKER
Got it, perfect.. Initially I had a question to add the new column LOAD_DATE in stg_citco_intraday_trade
but now, when I load the records from stg_citco_intraday_trade to base table, I donot need to select this column LOAD_DATE, that makes things clear.
Thank you so much for your patience and help !
but now, when I load the records from stg_citco_intraday_trade to base table, I donot need to select this column LOAD_DATE, that makes things clear.
Thank you so much for your patience and help !
ASKER
scriptname.ksh missingtrades_20130530.csv
scriptname.ksh missingtrades_20130530
It works now..
Thank you for your help.
Now, I changed my sh as follows
Open in new window
Name of my shell script - stg_citco_intraday_trade_v
File I wanted to pass on as parameter - Citco_missingtrades_201305
Am trying to rename the Citco_missingtrades_201305
I tried to execute as
stg_citco_intraday_trade_v
and it works.
Now I have another issue, I included sqlloader as part of my shell and it is loading into the table STG_CITCO_INTRADAY_TRADE. The very first column of this table has the sequence number.
Whenever data gets loaded into this table STG_CITCO_INTRADAY_TRADE, sequence number column should be 2013052300001, 2013052300002 and so on..
If the file that is being as passed as parameter is Citco_missingtrades_201305
sequence number column should be 2013053000001, 2013053000002 and so on..
Can you suggest how to do that. I am attaching my control file
stg-citco-intraday-trade-ctl.txt