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:
Code:
$
$
$ # 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 -
Code:
$
$ 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
Thnks Buddy
the issue is resolved .........thanks a lot
---------- Post updated at 05:00 AM ---------- Previous update was at 04:49 AM ----------
Quote:
Originally Posted by xal_kaushi
Thnks Buddy
the issue is resolved .........thanks a lot
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.
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
Hello
i have a shell script. it is running fine when i manually run at command prompt using following command
./script_file
but while running shell script from crontab, it is giving error in each line. (2 Replies)
Dear expert,
Can we invoke sqlldr command remotely.
When I try rsh command in machine 10.1.65.116, it's failed on sqlloader command. However, nothing wrong on the shell scripts or environment setting of the remote environment, I able to execute in the scripts in remote machine.
in machine... (8 Replies)
hey all,
i am working on sun solaris machine and i want to start a process in background using shell script (actually i wanna start tomcat server using shell script). please dont tell me that append a & at last because this is not working in the shell script. i have also used nohup and... (8 Replies)
Hi friends,
I have a script that sets the env variable path based on different conditions.
Now the new path variable setting should not done in the same terminal or same shell.
Only a new terminal or new shell should have the new path env variable set.
I am able to do this only as follows:
>cd... (1 Reply)
Hi
I'm using SQL*Loader in shell script as below
sqlldr $uname/$pword@$ORACLE_SID parfile=$test.par
for e.g. if $test is 'file1'
and getting the below error
LRM-00109: could not open parameter file 'file1'
LRM-00113: error when processing file 'file1'
SQL*Loader: Release... (7 Replies)
Dear Brothers!
Need your help for the case where I am running one command on prompt and its giving us the correct output, but when i use the same command from shell its directs no output.:wall:
the command on command prompt is
ls -ltrh * | nawk '{if ($5~ '/$'M'/') print $9}' | grep -v... (1 Reply)
Hi Guru's,
I am creating alias for db instance running on a server through script, am able to create them based on /etc/oratab entries and can use successfully with the below script.
#!/bin/bash
SCRIPT_PATH=${HOME}/scripts/db/script... (3 Replies)
Good morning,
I'm attempting to call sqlldr via shell script and it is prompting endIf is unec
#!/bin/sh
cd /tmp/v_tst
FILENAME_WANTED=`date +"HourlyData_%Y%m%d_%H00.txt"`
echo "FILENAME_WANTED = ${FILENAME_WANTED}"
LIST_OF_FILES=`ls -rt HourlyData*.txt |tail -1`
LIST_OF_FILES=`basename... (4 Replies)
My current requirement is to replace xxyxx string with value of date
date1 variable holds a date
and the current script writes html tags to a file as follows
echo date1
nawk 'BEGIN{
FS=","
print "<HTML>""<HEAD>""<p>Hi All,<br><br>There are no cases closed on the xxyxx"
print ... (2 Replies)