Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

connecting to table to extract multiple rows into file from unix script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting connecting to table to extract multiple rows into file from unix script
# 1  
Old 01-06-2012
connecting to table to extract multiple rows into file from unix script
I need to extract the data from oracle table and written the below code.
But it is not working.There is some problem with the query and output is shown is No rows selected" . If I run the same query from sql developer there is my required output.

And if I run the shell script with simple sql query like "select * from emp" it is working.
Code:
ORACLE_SID=fpql;export ORACLE_SID
ORACLE_BASE=/dboracle/orabase ;export ORACLE_BASE
ORACLE_HOME=/dboracle/orabase/product/9.0.4.1_abc01i ;export ORACLE_HOME

ORA_USERNAME=abc@abc
ORA_PASSWD=abc
$ORACLE_HOME/bin/sqlplus -s $ORA_USERNAME/$ORA_PASSWD  << EOFSQL 
spool jobs123.dat           # for sending te jobs to te file jobs123
select * from process_histories WHERE pr_code='XYZ_job' and processed_date like '04-JAN-12';
spool off;
exit;
EOFSQL


Could you please have a look and share your views and any code change in the script.

Regards,
Giridhar


Moderator's Comments:
Mod Comment How to use code tags
Last edited by Franklin52; 01-06-2012 at 07:04 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 01-06-2012
Code:
... << EOFSQL
...
EOFSQL

Note that the here document delimiters (the two EOFSQL in your case) should be identical (trailing white space characters included).

Please post the exact output/error message that you're getting.
# 3  
Old 01-06-2012
Quote:
spool jobs123.dat # for sending te jobs to te file jobs123
This is not the correct syntax for a comment in Oracle.
Code:
/* for sending the jobs to the file jobs123 */
spool jobs123.dat

Worth checking the directory where you expect to find jobs123.dat in case you have a similar file but with a very long name!
Last edited by methyl; 01-06-2012 at 08:19 AM.. Reason: remove quit/exit question. Both are valid! Correct mispaste
This User Gave Thanks to methyl For This Post:
radoulov
# 4  
Old 01-06-2012
The space is unexceptional, try the below one.

Code:
ORACLE_SID=fpql;export ORACLE_SID
ORACLE_BASE=/dboracle/orabase ;export ORACLE_BASE
ORACLE_HOME=/dboracle/orabase/product/9.0.4.1_abc01i ;export ORACLE_HOME

ORA_USERNAME=abc@abc
ORA_PASSWD=abc
$ORACLE_HOME/bin/sqlplus -s $ORA_USERNAME/$ORA_PASSWD <<EOFSQL 
spool jobs123.dat           # for sending te jobs to te file jobs123
select * from process_histories WHERE pr_code='XYZ_job' and processed_date like '04-JAN-12';
spool off;
EOFSQL

# 5  
Old 01-06-2012
@Rksiva
The extra space character before the "<<" is not a problem.

@giridhar276
Please post what Operating System and version you have and what Shell your are using.

I have come across Shells which expand the asterisk character in a "here" document.
If you get a list of files from this test script, you need to protect the asterisk.
Code:
cat << EOF
Unprotected
*
Protected
\*
EOF

The normal output from a Posix Shell should be:
./scriptname
Unprotected
*
Protected
\*

# 6  
Old 01-06-2012
Quote:
Originally Posted by methyl
This is not the correct syntax for a comment in Oracle.
Code:
/* for sending the jobs to the file jobs123 */
spool jobs123.dat

Good catch! This will definitely cause an error.
For comments that don't span multiple lines, you could use also double dash (--).
# 7  
Old 01-06-2012
Or even the old way.
Code:
REMARK A single line of comment in an Oracle SQL Plus program
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parameterizing to dynamically generate the extract file from Oracle table using Shell Script

I have below 2 requirements for parameterize the generate the extract file from Oracle table using Shell Script. Could you please help me by modifying the script and show me how to execute it. First Requirement: I have a requirement where I need to parameterize to generate one... (0 Replies)
Discussion started by: hareshvikram
0 Replies

2. Shell Programming and Scripting

Using Isql for SQL SERVER to get the table rows counts in UNIX shell script to

need to create shell script to read the table's name from file and connect SQL SERVER using isql (odbcunix) i 'm able connect to database with below command line syntex but i could not get working in shell script with SQL and storing the row count in variable. isql -v DSN USERNAME PASSWD ... (6 Replies)
Discussion started by: pimmit22043
6 Replies

3. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

4. Shell Programming and Scripting

Retrieve multiple rows from mysql and automatically create a table

Hi, i want to create a table automatically based on another table (sms_key). For example; If user create a new row with sms_keyword field: IRC then a table created automatically (with some field on it, like: name, ph_number, messages). select * from sms_key; +-------------+ |... (1 Reply)
Discussion started by: jazzyzha
1 Replies

5. Shell Programming and Scripting

Connecting sqlplus from UNIX with multiple select statement

hi, i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command: cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID << EOF set pagesize 0 set feedback off set verify off ... (1 Reply)
Discussion started by: lovelysethii
1 Replies

6. Shell Programming and Scripting

extract complex data from html table rows

I have bash, awk, and sed available on my portable device. I need to extract 10 fields from each table row from a web page that looks like this: </tr> <tr> <td>28 Apr</td> <td><a... (6 Replies)
Discussion started by: rickgtx
6 Replies

7. Shell Programming and Scripting

Shell script to extract rows from table

I have an Employee with EID, ENAME and ESTATUS as columns in SQL. I want to extract the status of an employee and update the details if the status is 'A'. Can anyone help in writing the shell script. (1 Reply)
Discussion started by: vkca
1 Replies

8. Shell Programming and Scripting

Connecting to multiple unix server from unix server using shell script

Hi Gurus, I'm a unix newbie and I would like to connect to multiple unix servers from unix server using shell script i.e from server a to server b,c,d etc. I want to copy the files from unix server a to server b, c, d. I can access staright using ssh without the need to have password and user... (5 Replies)
Discussion started by: sexyTrojan
5 Replies

9. Shell Programming and Scripting

Unix command to extract a record from a table

Suppose there is a table like the following...I just wanted to know if there is any command using which we can get the record/name of the person who joined before 2005.. Sl Name des y.o.joining 1 Ram Engineer 2001 2 Hari Doctor 2004 3 David Plumber 2005 4 Rahim painter 2007 5 gurmeet... (1 Reply)
Discussion started by: satyajit007
1 Replies

10. Shell Programming and Scripting

extract multiple cloumns from multiple files; skip rows and include filenames; awk

Hello, I am trying to write a bash shell script that does the following: 1.Finds all *.txt files within my directory of interest 2. reads each of the files (25 files) one by one (tab-delimited format and have the same data format) 3. skips the first 10 rows of the file 4. extracts and... (4 Replies)
Discussion started by: manishabh
4 Replies
Login or Register to Ask a Question