|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
August 19th, 2004, 06:28 PM
|
|||
|
|||
|
truncate table script
Hi,
I'm new to unix. i know oracle. Can somebody help me in writing a unix script to truncate a table when we pass that table name as command line argument. Thanks in advance Sachin
|
|
#3
August 20th, 2004, 04:27 PM
|
|||
|
|||
|
It's just a little more than reading man pages....
Code:
#!/bin/ksh
# $1 = table name
# UID= user PSWD=password
exsql () {
awk '{ if(NR == 1) {
STR=sprintf("%s/%s",UI,PW)
print STR
print "set pagesize 0;"
print "set tab off;"
print "set arraysize 1;"
}
print $0}' UI=$UID PW=$PSWD | $ORACLE_HOME/bin/sqlplus -s
}
echo "TRUNCATE TABLE $1;"|exsql > truncate_table.log
exit
|
|
#4
September 9th, 2004, 10:27 AM
|
|||
|
|||
|
Hi Sachin,
I find it simplest to use a "here document" within a UNIX script to execute SQL commands. I've attached an example below that is using external o/s authentication (the whole passing usernames and passwords to Oracle via a script is a bit messy if you don't want the password hardcoded in the script, or showing up on the ps listing). Note that the $ is a special character in the UNIX shell so if you use it in a here document you need to escape it with a "\" (see how I've had to run "select * from v$instance;" below). Good luck, Andy #!/usr/bin/ksh #-- Get the table to be truncated TABLE_NAME=$1 #-- Exit if the table_name has not been supplied if [ -z "${TABLE_NAME}" ]; then echo "ERROR: table_name not supplied" exit 10 fi #-- Execute the SQL sqlplus / <<+ENDOFSQL+ select * from v\$instance; truncate table ${TABLE_NAME}; exit +ENDOFSQL+ exit 0
|
|
| Viewing: Dev Shed Forums > Operating Systems > UNIX Help > truncate table script |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
