unix shell with a parameter

Great, thank you for your quick response. My bad, I was running the script as

scriptname.ksh  missingtrades_20130530.csv  as parameter instead of
scriptname.ksh missingtrades_20130530

It works now..

Thank you for your help.

Now, I changed my sh as follows

#!/bin/ksh -xve


date

HOME_DIR=$tradehome/ods
DOWNLOAD_FILE_NAME=$1.csv
DOWNLOAD_FILE_NAME1=sample.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

echo   'Started sqlloader stg_citco_intraday_trade'


if ( test -e "$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME" )  then
   
   
   dos2unix $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
   mv $DOWNLOAD_FILE_NAME $DOWNLOAD_FILE_NAME1
   echo $odspass | sqlldr $odsuser control=$tradehome/ods/ctl/stg_citco_intraday_trade.ctl  log=$tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log  bad=$badfname  discard=$dscfname
   cat $tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log

fi

echo  'Ended sqlloader stg_citco_intraday_trade'

date

Select all Open in new window

Name of my shell script - stg_citco_intraday_trade_v1.sh
File I wanted to pass on as parameter - Citco_missingtrades_20130523
Am trying to rename the Citco_missingtrades_20130523 as sample.csv

I tried to execute  as

stg_citco_intraday_trade_v1.sh Citco_missingtrades_20130523

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_20130530, then the
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

This is kind of urgent issue, kindly respond.

Wonderful, thank you for your help.  It works perfect

I tried to execute  as

stg_citco_intraday_trade_v1.sh Citco_missingtrades_20130530



One last question, what if I wanted to execute as
stg_citco_intraday_trade_v1.sh 20130530, I mean to say if the parameter I wanted to pass is only the date and the file could be

Citco_missingtrades_20130530.csv
Citco_missingtrades_20130523.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

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_20130530

2013053000001
2013053000002
2013053000003 etc.,

and if input file is Citco_missingtrades_20130523

2013052300001
2013052300002
2013052300003 etc.,

What if I wanted to be in the continous sequence like

2013053000001
2013053000002
2013053000003
2013052300004
2013052300005
2013052300006

Please suggest.

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

stg_citco_intraday_trade_v2.sh 20130523

Select all Open in new window

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

Unnecessary to change to Number(38)...execute this in sql*plus:

SET NUM 23

Select all Open in new window

In Excel select the column, then Format -> Cells -> Number tab -> Select number -> Set decimal places to 0 (zero) -> Click OK.


It seems that you loose precision with excel.

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.

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"?

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

when I change the number in my shell script, it works fine.

STG_SEQUENCE_NUMBER="${1}00000"

However, thank you so much for your wonderful help!

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.
;)

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);

Select all Open in new window

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   . . .

Select all Open in new window

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')",
)

Select all Open in new window

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 !