Facing problem in the sqlldr & shell script Post: 302590581

Sponsored Content

Top Forums Shell Programming and Scripting Facing problem in the sqlldr & shell script Post 302590581 by durden_tyler on Monday 16th of January 2012 11:03:50 PM

01-17-2012

Registered User

Quote:

Originally Posted by xal_kaushi

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

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

$
$

Quote:

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

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

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

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

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

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

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

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

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

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

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

LEARN ABOUT OSX

osacompile

OSACOMPILE(1)						    BSD General Commands Manual 					     OSACOMPILE(1)

NAME

     osacompile -- compile AppleScripts and other OSA language scripts

SYNOPSIS

     osacompile [-l language] [-e command] [-o name] [-d] [-r type:id] [-t type] [-c creator] [-x] [-s] [-u] [-a arch] [file ...]

DESCRIPTION

     osacompile compiles the given files, or standard input if none are listed, into a single output script.  Files may be plain text or other
     compiled scripts.	The options are as follows:

     -l language
	   Override the language for any plain text files.  Normally, plain text files are compiled as AppleScript.

     -e command
	   Enter one line of a script.	Script commands given via -e are prepended to the normal source, if any.  Multiple -e options may be given
	   to build up a multi-line script.  Because most scripts use characters that are special to many shell programs (e.g., AppleScript uses
	   single and double quote marks, ``('', ``)'', and ``*''), the command will have to be correctly quoted and escaped to get it past the
	   shell intact.

     -o name
	   Place the output in the file name.  If -o is not specified, the resulting script is placed in the file ``a.scpt''.  The value of -o
	   partly determines the output file format; see below.

     -x    Save the resulting script as execute-only.

     The following options are only relevant when creating a new bundled applet or droplet:

     -s    Stay-open applet.

     -u    Use startup screen.

     -a arch
	   Create the applet or droplet for the specified target architecture arch.  The allowable values are ``ppc'', ``i386'', and ``x86_64''.
	   The default is to create a universal binary.

     The following options control the packaging of the output file.  You should only need them for compatibility with classic Mac OS or for cus-
     tom file formats.

     -d    Place the resulting script in the data fork of the output file.  This is the default.

     -r type:id
	   Place the resulting script in the resource fork of the output file, in the specified resource.

     -t type
	   Set the output file type to type, where type is a four-character code.  If this option is not specified, the creator code will not be
	   set.

     -c creator
	   Set the output file creator to creator, where creator is a four-character code.  If this option is not specified, the creator code will
	   not be set.

     If no options are specified, osacompile produces a Mac OS X format script file: data fork only, with no type or creator code.

     If the -o option is specified and the file does not already exist, osacompile uses the filename extension to determine what type of file to
     create.  If the filename ends with ``.app'', it creates a bundled applet or droplet.  If the filename ends with ``.scptd'', it creates a bun-
     dled compiled script.  Otherwise, it creates a flat file with the script data placed according to the values of the -d and -r options.

EXAMPLES

     To produce a script compatible with classic Mac OS:

	   osacompile -r scpt:128 -t osas -c ToyS example.applescript

SEE ALSO

     osascript(1), osalang(1)

Mac OS X							 November 12, 2008							  Mac OS X