Facing problem in the sqlldr & shell script

Shell Programming and Scripting
1

Guys i am facing two problems :

(1) when i create the sql loader file the date format i m getting is this
28-DEC-11 12.03.14.107137 AM;
for this i m using this script but unable to load the files

trailing nullcols
(
SERIALNO,
AMOUNT,
CLASS,
MDN,
VDATE "to_date(:TIMESTAMP, 'DD-MON-YY HH24:MiS')"

)

(2) Another one is the name of the file is ERIC_VOUCHERDAT15
where 15 is the date
i want to do ftp this file from another server to my server
for this what syantax i have to use it .

Pls help mee guys

2

hi,

1) a bad file must be formed when your data is not being loaded. Can you please paste the error displayed in that? Also, try using the timestamp as :

VDATE "to_date(:TIMESTAMP, 'DD-MON-YY HH24:MiSS')"

2) In case of the files that need to be ftped, will the date only change and will it be 2 digits ?? If so, you can use - ERIC_VOUCHERDAT?? where ?? = 01,02... 31.
Else, please give a list of all diff filenames so that filepattern can be identified.

Regards,
A!

3

I am tried sm other method also but

I am getting this error

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jan 16 19:52:03 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL*Loader-291: Invalid bind variable :TIMESTAMP in SQL string for column VDATE.

---------- Post updated at 09:26 AM ---------- Previous update was at 09:23 AM ----------

Yes dear
the format remains the same throughtout but the date change everyday
for eg today is 16-jan
the file is ERIC_VOUCHERDAT16

4

Just specify the datatype ("timestamp") and the format in your control file and Oracle will perform the implicit conversion if your database column is a date.

An example follows:

$
$
$ # Data in table "t" before load
$
$ echo "select x, to_char(y, 'mm/dd/yyyy hh24:mi:ss') as y from t;" | sqlplus -s test/test

no rows selected

$
$
$ # Contents of my control file "t.ctl" with inline data
$
$ cat -n t.ctl
     1  load data
     2  infile *
     3  replace
     4  into table t
     5  fields terminated by ","
     6  trailing nullcols
     7  (
     8    x  integer external,
     9    y  timestamp 'DD-MON-RR HH.MI.SS.FF6 AM'
    10  )
    11
    12  BEGINDATA
    13  1,28-DEC-11 12.03.14.107137 AM
    14  2,29-DEC-11 09.23.57.123456 PM
    15  3,08-JAN-12 11.59.59.999999 PM
$
$
$ # Load data using sqlldr
$
$ sqlldr userid=test/test control=t.ctl silent=all
$
$
$ # Data in table "t" after load
$
$ echo "select x, to_char(y, 'mm/dd/yyyy hh24:mi:ss') as y from t;" | sqlplus -s test/test

         X Y
---------- -------------------
         1 12/28/2011 00:03:14
         2 12/29/2011 21:23:57
         3 01/08/2012 23:59:59

$
$

You can extract the current date from the "date" command of Unix/Linux, like so -

$
$ date '+%d'
16
$
$

Assign this to a shell variable and use that variable in your filename within the ftp command-list. Or you could use the output of the command above directly in your filename.

HTH,
tyler_durden

5

in this case as i mentioned earlier, the filepattern can be ERIC_VOUCHERDAT??, where ??=01,02,03....
If you have a configuration file, you can use this file pattern in that or directly in the shell.. it will work.

Regards,
A!

6

Thnks Buddy
the issue is resolved .........thanks a lot :b:

---------- Post updated at 05:00 AM ---------- Previous update was at 04:49 AM ----------

I want a little more help
when i am running the script for loading the data .Before the data there a some charters which i dont want to insert for removing them what syntax i should use.

awk or sed

eg ---
SQL*Plus:Release11.1.0.6.0-ProductiononThuDec2917:47:002011

Copyright(c)1982,2007,Oracle.Allrightsreserved.

Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-64bitProduction
WiththePartitioning,RealApplicationClusters,OLAP,DataMining
andRealApplicationTestingoptions

SQL>SQL>SQL>SQL>SQL>23456
55570000264691;1269; LN56;8765566051;28-DEC-1112.03.14.107137AM; Used
55570000248704;1269; LN56;8765291330;28-DEC-1101.10.32.909514AM; Used

I have to remove this lines which are coloured
and i have to insert the which is in csv format
Regards
Kaushal

7

Use the "-s" or "silent" option with sqlplus. It suppresses the display of the blurb and prompts.

sqlplus -s <user>/<password>@<db_connect_identifier>

Or better still -

sqlplus -s /nolog <<EOF
connect <user>/<password>@<db_connect_identifier>
-- your stuff here (sqlplus settings, DML etc)
exit
EOF

Have a look at the output of "sqlplus --help" for more information.

tyler_durden