it is count statement in SQL script.
select count(*) from abc where xyz<>2;
---------- Post updated at 07:05 AM ---------- Previous update was at 06:43 AM ----------
Quote:
Originally Posted by vbe
What is in your sql script?
---------- Post updated at 16:58 ---------- Previous update was at 16:55 ----------
e.g. at the beginning of script
VBE, I tried your script it is executed and working almost. Here is my script that i ran- #! /bin/ksh
export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2
/opt/oracle/app/oracle/product/9.2/bin/sqlplus -s abc/abc@mydb <<EOF
spool /mycount.txt
set heading off
set feedback 0
set verify off
set auto off
set echo off
@myquery.sql
spool off
!echo "Spool file generated..."
clear buffer
EXIT
EOF
Here was the output [aix2][/]# ./myquery.sh
0
Spool file generated...
[aix2][/]# cat mycount.txt
0
[aix2][/]#
I plan to put he script in crontab to run every 10 minutes. And hence I need the sql output in same file like below: Date Run count
2010-11-12 06:00 0
2010-11-12 06:10 10
2010-11-12 06:20 3
Please say how can I do it??
---------- Post updated at 11:36 AM ---------- Previous update was at 07:05 AM ----------
Hi Guys,
I need to spool the results in same file just after 10 minutes. I will put that script in crontab;
Hi,
I search all post...and no soluation about..if i would like to run a sql statement and output the result to txt file.
for example,
i usually run "sql" to logon the database and run select statement. Then I need to copy the output into the result.txt. Can I run the script to do this... (7 Replies)
Hi ,
I just found you while surfing for the string
'Redirecting sql select query output from within a shell script to txt file/excel file'
Could you find time sending me the code for the above question?
It'll be great help for me.
I have a perl file that calls the sql file... (1 Reply)
Hi Yogesh,
Lucky that i caught you online. Yeah i read about DBI and the WriteExcel module. But the server is not supporting these modules. It said..."Cannot locate DBI"..."Cannot locate Spreadsheet::WriteExcel"
I tried creating a simple text file to get the query output, but the... (1 Reply)
Hi All,
I want to format the result driven from the query into neat format.
For example pls find the below code,
#! /bin/sh
result='
sqlplus -s uname/passwrd@DBname
select no,name,address,ph_no, passport_no,salary,designation
from emp_table where salary>1000;
exit
EOF'
... (8 Replies)
Hi,
I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning.
success records to success.log file
failure record to failure.log file.
As of now i am doing like... (1 Reply)
Hi
I want ot save SQL query result in one txt file. for that i have written one code line
sqlplus -s $dbstring @/usr/local/bin/sched/nightly_Cronjob/exec_123.sql >> /usr/local/bin/sched/nightly_Cronjob/result.txt
but it is not working .
database : Oracle
so please advice me how can i... (7 Replies)
Hi,
I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen.
snapshot of my script:... (13 Replies)
How to Get SQL query result to file in putty?
I have one SQL query and I want that query output to be redirected to the file.
uname -a
SunOS XXX 5.8 Generic_117350-58 sun4u sparc SUNW,Sun-Fire-480R
Please suggest. (7 Replies)
Dear community,
please help with a query on Oracle. I'm using SQLPlus (but with SQLDeveloper is the same) to accamplish a sinple query like:
select count(*) from ARCHIT_D_TB where (TYP_ID=22 OR TYP_ID=23) and SUB_TM like '%SEP%' and CONS=1234This is a very simple query that works perfect until... (5 Replies)
I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line.
Main Code shell script:
this is my code:
#!/bin/bash
file="db_detail.txt"
. $file
rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies
LEARN ABOUT DEBIAN
stag-selectall_xml
SELECTALL_XML(1p) User Contributed Perl Documentation SELECTALL_XML(1p)NAME
stag-selectall_xml - Query all elements from database with an XML output
SYNOPSIS
stag-selectall_xml [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL
DESCRIPTION
This script will query a database using either SQL provided by the script user, or using an SQL templates; the query results will be turned
into XML using the DBIx::DBStag module. The nesting of the XML can be controlled by the DBStag SQL extension "USE NESTING..."
EXAMPLES
stag-selectall_xml -d "dbi:Pg:dbname=mydb;host=localhost"
"SELECT * FROM a NATURAL JOIN b"
TEMPLATES
A parameterized SQL template (canned query) can be used instead of specifying the full SQL
For example:
stag-selectall_xml -d genedb /genedb-gene gene_symbol=Adh
Or:
stag-selectall_xml -d genedb /genedb-gene Adh
Or:
stag-selectall_xml -d genedb /genedb-gene gene_symbol@=Adh,dpp,bam,indy
A template is indicated by the syntactic shorthand of using a slash to precede the template name; in this case the template is called
genedb-gene. the -t option can also be used.
All the remaining arguments are passed in as SQL template parameters. They can be passed in as either name=value pairs, or as a simple list
of arguments which get passed into the template in order
To use templates, you should have the environment variable DBSTAG_TEMPLATE_DIRS set. See DBIx::DBStag for details.
LISTING AVAILABLE TEMPLATES FOR A DB
stag-selectall_xml -d mydb -h
LISTING VARIABLES FOR A TEMPLATE
stag-selectall_xml /genedb-gene -h
ENVIRONMENT VARIABLES
DBSTAG_DBIMAP_FILE
A file containing configuration details for local databases
DBSTAG_TEMPLATE_DIRS
list of directories (separated by :s) to be searched when templates are requested
COMMAND LINE ARGUMENTS
-h|help
shows this page if no other arguments are given
if a template is specified, gives template details
if a db is specified, lists templates for that db
use in conjunction with -v for full descriptions
-d|dbname DBNAME
this is either a full DBI locator string (eg dbi:Pg:dbname=mydb;host=localhost) or it can also be a shortened "nickname", which is then
looked up in the file pointed at by the environment variable DBSTAG_DBIMAP_FILE
-u|user USER
database user identity
-p|password PASS
database password
-f|file SQLFILE
this is a path to a file containing SQL that will be executed, as an alternative to writing the SQL on the command line
-n|nesting NESTING-EXPRESSIONS
a bracketed expression indicating how to the resulting objects/XML should be nested. See DBIx::DBStag for details.
-t|template TEMPLATE-NAME
the name of a template; see above
-wh|where WHERE-CLAUSE
used to override the WHERE clause of the query; useful for combining with templates
You can append to an existing where clause by using the prefix +
-s|select SELECT-COLS
used to override the SELECT clause of the query; useful for combining with templates
-rows
sometimes it is preferable to return the results as a table rather than xml or a similar nested structure. specifying -rows will fetch
a table, one line per row, and columns separated by tabs
-pre SQL
a piece of SQL is that is executed immediately before the main query; e.g.:
-pre "SET search_path=myschema,public"
-o|out FILE
a file to output the results to
-w|writer WRITER
writer class; can be any perl class, or one of these
xml [default]
sxpr
lisp S-Expressions
itext
indented text
-color
shows results in color (sxpr and itext only)
-show
will show the parse of the SQL statement
perl v5.12.4 2011-10-14 SELECTALL_XML(1p)