Visit Our UNIX and Linux User Community


Facing problem in the sqlldr & shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Facing problem in the sqlldr & shell script
# 1  
Old 01-16-2012
Facing problem in the sqlldr & shell script

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

Code:
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

Last edited by radoulov; 01-16-2012 at 06:05 AM.. Reason: Code tags!
# 2  
Old 01-16-2012
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 :

Code:
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!

Last edited by radoulov; 01-16-2012 at 06:06 AM.. Reason: Code tags!
# 3  
Old 01-16-2012
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 ----------

Quote:
Originally Posted by archimedes
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 :

Code:
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!
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  
Old 01-17-2012
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
# 5  
Old 01-17-2012
Quote:
Yes dear
the format remains the same throughtout but the date change everyday
for eg today is 16-jan
the file is ERIC_VOUCHERDAT16
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  
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
# 7  
Old 01-18-2012
Quote:
Originally Posted by xal_kaushi
... 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
...
Use the "-s" or "silent" option with sqlplus. It suppresses the display of the blurb and prompts.

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

Or better still -

Code:
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #314
Difficulty: Easy
Pointers were not used in the original C programming language and were added later in in C++.
True or False?

9 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

Featured Tech Videos