I am trying to get the count of the record in a table into a file or a variable .
set heading off
set feedback off
set linesize 20
set pages 0
set newpage 0
set verify off
spool test.txt
select count(*) from test_data where dfu_load_date='23/05/2012' and campaign_code='sd1';
quit;
spool off;
END
cnt=`wc -l test.txt `
echo "The records uploaded= ${cnt}"
Now from above script i am trying to get the count into a spool file and the pic the count from there.
Could someone please suggest any way from which i can get the count of the records into a variable or txt file.
Note: That link contains some errors though, since EOF is not at the beginning of the line in the examples. And the double quotes are wrong and cannot be cut-and-pasted...
$ cat s.txt
SQL> select sum(salary) from emp;
SUM(SALARY)
-----------
30807
SQL> spool off
Jay@Jay-PC ~
$ grep " *[0-9]" s.txt
30807
---------- Post updated at 12:45 PM ---------- Previous update was at 12:32 PM ----------
To be precise, your text.txt contains all the lines between two spool commands.
Since you require only the digits from result set, below two lines will be suffice.
Read a value right into a shell variable. This example can process a line of results at a time.
$ cat x
#!/bin/ksh
( $ORACLE_HOME/bin/sqlplus -s login/password@database <<EOF
set heading off;
set pagesize 0;
select 'count='||count(*)
from dual;
exit;
EOF
)|
while read line # Read a line at a time. Could get blank lines.
do
if [[ -n $line ]] # If the line read is not blank...
then eval $line # eval causes $line to be evaluated twice, thus
# setting shell variables with the data returned.
## Print out what was read in. Quotes are needed around the
## variables so printf sees nulls.
printf "%d\n" "$count"
fi
done
exit 0
$ x
1
$
I would be remiss if I did not point out that if what is being eval'd could be manipulated into containing a valid shell command, it would be executed by eval. So be careful with eval.
