{
$DCITS_SQL << EOF
set head off
select a.FIN,a.STAFF_NAME_X,b.FIN,b.DEPT_C from ci_5day_staff a, ci_cits_consol_dtls b where a.FIN=b.FIN;
exit
EOF
}|while read line
do
if [ "$line" ] #check if line is not null
then
#if line not null, parse the line into words/variables
set $line #set the line to positional variables, in this case is $1 and $2
fin ="$1" #staff fin number
name="$2" #name of staff
gway="$3"
echo "$fin,$name,$gway" >> $final #changing single quotes to double
fi # not endif
done
hi!
the sql is finally returning something but the while loop is still not working...
error message:
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
}: Command not found
while: Expression syntax
Without the -s option, you will get display like this while connecting
Quote:
SQL*Plus: Release 10.2.0.2.0 - Production on Wed May 2 11:11:15 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
and display like this while disconnecting,
Quote:
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
With the -s option, this display gets surpressed. It is basically used in scripts to get only the sql statement output and to avoid all unwanted information.
If you can check the script that your '$DCITS_SQL' points to, you will be able to determine if that is the cause.
Last edited by ranj@chn; 05-02-2007 at 07:16 AM..
Reason: corrections
Without the -s option, you will get display like this while connecting
and display like this while disconnecting, With the -s option, this display gets surpressed. It is basically used in scripts to get only the sql statement output and to avoid all unwanted information.
If you can check the script that your '$DCITS_SQL' points to, you will be able to determine if that is the cause.
mine is without the -s
========================================================
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Apr 30 10:10:42 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
=======================================================
sorry but i dunno whether its the sql the cause of the error because the database is returning the results but its the while loop having error...
kindly advise...thanks!
==========================================================
{$DCITS_SQL << EOF
set head off
select a.FIN,a.STAFF_NAME_X,b.FIN,b.DEPT_C from ci_5day_staff a, ci_cits_consol_dtls b where a.FIN=b.FIN;
exit
EOF} | while read line
do
if [ "$line" ]#check if line is not null
then
#if line not null, parse the line into words/variables
set $line #set the line to positional variables, in this case is $1 and $2
fin ="$1" #staff fin number
name="$2" #name of staff
gway="$3"
echo "$fin,$name,$gway" >> $final
fi
done
==========================================================
Hello ,
Im calling every single file inside my script like 1.sql,2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting errorUnexpected EOF] , can anyone please help me out in this..
How i can use for loop to invoke my... (6 Replies)
Need help.
Any reason why the update is not working in this sql:
#!/bin/ksh
#setup your environment
. /opt/lnpsite/nm00/scripts/setup_env nm00
for tn in `cat /home/cpac/Resync/sv_tn.list`
do
`sqlplus -s ${DB_USERID} << EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF... (5 Replies)
I want to spool files from unix using a sql script that would
take values from another sql query within the KSH script..
unix loop
select order_date from date_tbl
for each order_Date i need to call this spool script with the value
sqlplus scott/tiger@order_db @/ordspool/order_date.sql... (2 Replies)
Hi,
I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data.
Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Trying to compile all SQL files using a shell script. But the below code is not working. Below Code works fine when for loop is not there(commenting line no: 1,2 and 9).
1. sq_lfile=`ls *.sql`
2. for current_sql_file in $sql_file
3. do
4. sqlplus uname/pass@Service>>SQLLOG << -ENDOFSQL... (3 Replies)
hi all,
i am experiencing an error which i think an incorrect syntax for the where clause passing a variable was given. under is my code.
sqlplus -s ${USERNAME}/${PASSWORD}@${SID} << END1 >> $LOGFILE
whenever sqlerror exit
set serveroutput on size 1000000
declare
l_rc ... (0 Replies)
Hi i want to run a query in loop(unix loop) every 2 hours and spool the result to a file.
i am trying but getting error here is the script
$ while true
do
sqlplus ank/ank<<!
spool ank.lst
select * from v$database;
exit
sleep 7200
done
:D (2 Replies)
Hello,
I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea?
Here is idea about the code:
my... (1 Reply)
I am currently trying to find a way to loop through files in a given directory and for each file modify a ctl file and sql load it. I have been using the sed command to change the infile, badfile parameters of the control file. I have not yet tried to sql load it.
Requirement: files are ftp to... (1 Reply)
Hi,
I am getting the error "No matching <<", when i run the below. Is it that we can't execute SQL in a Unix loop. I am executing a SQL in a loop. Is it the EOF is written wrongly.
. /opt/app/wlsconfigv61/domains/profiles/oracleV901.profile
export DBUSER=ecdb01
set -A DBINSTANCE... (3 Replies)
