To spool output from a database query


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To spool output from a database query
# 1  
Old 04-07-2008
To spool output from a database query

Hi all,

I would want to spool file for a database query, however by using crontab, the file is not spooled. Below shows my script:

ORACLE_HOME="/u01/oraprod/perpdb/10.1.0/db_1"
OUTFILE="/tmp/invalid.out"
FILE="$HOME/admin/scripts"
$ORACLE_HOME/bin/sqlplus -s "/as sysdba" @$FILE/compile_inv_obj.sql
sleep 30
echo exit >>$FILE/comp_all.sql

contents inside $FILE/compile_inv_obj.sql

spool comp_all.sql
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects
spool off

I could not c comp_all.sql in anywhere of my file system. Any suggestions would be much appreciated. Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Database query

I am getting an error function in date formate while importing the date ccolumn in oracle sql developer tool My date format in table is 6-Nov-14 and while importing its converted to 6/11/14 eventhough the format is as 6-Nov-14 in excel. could you please let help me out what I need to change... (6 Replies)
Discussion started by: ramkumar15
6 Replies

2. UNIX for Dummies Questions & Answers

#Spool/Query executing with extra space.

Hello, I have a requirement where i have to spool some data to a file. i have achived the desired target but m facing one issue. i have attached the script and the output. i checked the data length in the table but it is only 45 for column 1. can you tell me how to remove these extra... (4 Replies)
Discussion started by: Mohammed_Tabish
4 Replies

3. Shell Programming and Scripting

SQL*PLUS Spool Output

Hi, Im writing a script to run a bit of sql(via sqlplus) that pulls back some data and spools it to a file, I want the spool file to only display the data, with no sql command at the top and no reports at the bottom ie(# of records recieved). I am currently doing it via a grep command but... (1 Reply)
Discussion started by: Magezy
1 Replies

4. Shell Programming and Scripting

Help supressing spool output from screen when calling sqlplus from script

I'm calling an embedded sql from my shell script file. This sql does simple task of spooling out the contents of the table (see below my sample code) into a spool file that I specify. So far so good, but the problem is that the output is also displayed on screen which I do NOT want. How can I... (3 Replies)
Discussion started by: MxC
3 Replies

5. Shell Programming and Scripting

manipulate & format the output of spool command

Hi All, I am spooling the data some sql queries into a single file but wanted to know how to format the data of the file generated by spool. #!/bin/sh unset -f USAGE USAGE () { clear echo "############################USAGE#######################\n" echo "Incorrect number of... (2 Replies)
Discussion started by: ss_ss
2 Replies

6. Shell Programming and Scripting

Need help with a sh script to spool directory and modify the output (Oracle cnt file)

Hi, I'm creating a shell script to dynamically create a recreate controlfile for an Oracle database. I need to read a cold backup file system, and make some changes to these files. Let's say for argument sake the directory name is /ebsprod_c/oradata and it looks like this:... (6 Replies)
Discussion started by: exm
6 Replies

7. Shell Programming and Scripting

add the output of a query to a variable to be used in another query

I would like to use the result of a query in another query. How do I redirect/add the output to another variable? $result = odbc_exec($connect, $query); while ($row = odbc_fetch_array($result)) { echo $row,"\n"; } odbc_close($connect); ?> This will output hostnames: host1... (0 Replies)
Discussion started by: hazno
0 Replies

8. Programming

Single sql query to spool to multiple files

Is there anyway to spool my select statement into spool files of max 10000 records each? eg I have a select statement that will return 45000 records. A normal spool command will output the 45000 into just one spool file. How can I make sqlplus do this? 00001 - 10000 records --- spool... (3 Replies)
Discussion started by: Leion
3 Replies

9. Shell Programming and Scripting

Database Query

Hi, Am using informix database. When i give "isqlrf <database name> - " from the command prompt , i get connected to the database and i can run sql queries.. I am moving to freeBSD and when i give the same command at the prompt i get an error : Index already exists on column Why is this... (0 Replies)
Discussion started by: jisha
0 Replies

10. Shell Programming and Scripting

query to get a value from database

Hi friends, I have written a script which retrieves one value from the database. It takes time for the sql query to get the desired output. So the query runs fine from TOAD or from UNIX console. However if I put this in a script it doesn't work. The script is as below. #! /bin/ksh... (2 Replies)
Discussion started by: vivek_damodaran
2 Replies
Login or Register to Ask a Question
CRONTAB(1)							   User Commands							CRONTAB(1)

NAME
crontab - maintains crontab files for individual users SYNOPSIS
crontab [-u user] file crontab [-u user] [-l | -r | -e] [-i] [-s] crontab -n [ hostname ] crontab -c DESCRIPTION
Crontab is the program used to install, remove or list the tables used to serve the cron(8) daemon. Each user can have their own crontab, and though these are files in /var/spool/, they are not intended to be edited directly. For SELinux in MLS mode, you can define more crontabs for each range. For more information, see selinux(8). In this version of Cron it is possible to use a network-mounted shared /var/spool/cron across a cluster of hosts and specify that only one of the hosts should run the crontab jobs in the particular directory at any one time. You may also use crontab(1) from any of these hosts to edit the same shared set of crontab files, and to set and query which host should run the crontab jobs. Running cron jobs can be allowed or disallowed for different users. For this purpose, use the cron.allow and cron.deny files. If the cron.allow file exists, a user must be listed in it to be allowed to use cron If the cron.allow file does not exist but the cron.deny file does exist, then a user must not be listed in the cron.deny file in order to use cron. If neither of these files exists, only the super user is allowed to use cron. Another way to restrict access to cron is to use PAM authentication in /etc/security/access.conf to set up users, which are allowed or disallowed to use crontab or modify system cron jobs in the /etc/cron.d/ directory. The temporary directory can be set in an environment variable. If it is not set by the user, the /tmp directory is used. OPTIONS
-u Appends the name of the user whose crontab is to be modified. If this option is not used, crontab examines "your" crontab, i.e., the crontab of the person executing the command. Note that su(8) may confuse crontab, thus, when executing commands under su(8) you should always use the -u option. If no crontab exists for a particular user, it is created for him the first time the crontab -u command is used under his username. -l Displays the current crontab on standard output. -r Removes the current crontab. -e Edits the current crontab using the editor specified by the VISUAL or EDITOR environment variables. After you exit from the editor, the modified crontab will be installed automatically. -i This option modifies the -r option to prompt the user for a 'y/Y' response before actually removing the crontab. -s Appends the current SELinux security context string as an MLS_LEVEL setting to the crontab file before editing / replacement occurs - see the documentation of MLS_LEVEL in crontab(5). -n This option is relevant only if cron(8) was started with the -c option, to enable clustering support. It is used to set the host in the cluster which should run the jobs specified in the crontab files in the /var/spool/cron directory. If a hostname is supplied, the host whose hostname returned by gethostname(2) matches the supplied hostname, will be selected to run the selected cron jobs subsequently. If there is no host in the cluster matching the supplied hostname, or you explicitly specify an empty hostname, then the selected jobs will not be run at all. If the hostname is omitted, the name of the local host returned by gethostname(2) is used. Using this option has no effect on the /etc/crontab file and the files in the /etc/cron.d directory, which are always run, and considered host-specific. For more information on clustering support, see cron(8). -c This option is only relevant if cron(8) was started with the -c option, to enable clustering support. It is used to query which host in the cluster is currently set to run the jobs specified in the crontab files in the directory /var/spool/cron , as set using the -n option. SEE ALSO
crontab(5), cron(8) FILES
/etc/cron.allow /etc/cron.deny STANDARDS
The crontab command conforms to IEEE Std1003.2-1992 (``POSIX''). This new command syntax differs from previous versions of Vixie Cron, as well as from the classic SVR3 syntax. DIAGNOSTICS
An informative usage message appears if you run a crontab with a faulty command defined in it. AUTHOR
Paul Vixie <vixie@isc.org> Colin Dean <colin@colin-dean.org> cronie 2012-11-22 CRONTAB(1)