Sponsored Content
Top Forums Shell Programming and Scripting Facing problem in the sqlldr & shell script Post 302590935 by xal_kaushi on Wednesday 18th of January 2012 05:00:03 AM
Old 01-18-2012
Quote:
Originally Posted by durden_tyler
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 Smilie

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


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
 

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Facing issue in Solaris OS in crontab for running shell script

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)
Discussion started by: mabrar
2 Replies

2. Shell Programming and Scripting

Rsh & Sqlldr

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)
Discussion started by: epall
8 Replies

3. Shell Programming and Scripting

facing problem in starting a process in background using shell script.

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)
Discussion started by: dtomar
8 Replies

4. UNIX for Dummies Questions & Answers

Problem with xterm & tcsh & sourcing a script in a single command

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)
Discussion started by: sowmya005
1 Replies

5. Shell Programming and Scripting

sqlldr in shell script

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)
Discussion started by: vinoth_kumar
7 Replies

6. Shell Programming and Scripting

Problem facing command using shell

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)
Discussion started by: jojo123
1 Replies

7. Shell Programming and Scripting

Facing problem with Alias created through script.

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)
Discussion started by: venky.b5
3 Replies

8. Shell Programming and Scripting

Sqlldr call via shell script prompts error

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)
Discussion started by: V1l1h1
4 Replies

9. Shell Programming and Scripting

Facing issues with shell script changes

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)
Discussion started by: Rajesh A S
2 Replies
All times are GMT -4. The time now is 04:35 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy