Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help needed in retreiving records from Oracle

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help needed in retreiving records from Oracle
# 1  
Old 05-06-2011
Help needed in retreiving records from Oracle
Hello,

I am new to UNIX.

My Requirement: Need to connect to Oracle database from UNIX and execute an SELECT statement and store the records in a flatfile of Comma delimiter.

What I have Succeeded: I was able to connect to Oracle from UNIX.

Problem: I cannot fetch multiple records at a time. I can fetch only one column and one record.

Help Needed from you: I was to send all the input data that i returned from Oracle into flat file on UNIX system and of Comma delimiter.

Appreciate your help!
Thanks!
# 2  
Old 05-06-2011
Hi,

are you using sqlplus to connect to the database?
If so have a look at the sqlplus-command SPOOL to send the output to a flatfile. The sqlplus-command SET lets you specify a delimiter.
# 3  
Old 05-06-2011
Hi Cero,

Thanks for your reply.
Yes I am using sqlplus to connect to oracle. I can able to store the file. But the issue is i want to store the file in Comma delimiter. Can you please help me in getting the exact queries/statements.
For Ex: The file is of structure EMPNO ENAME SAL.
The data is storing in flat file in UNIX as
101 Cero 4000 200 Arun 3000 459 Chris 3500.

But I need to store the data in the file as
101, Cero,4000
200, Arun,3000
459,Chris,3500

Thanks!
# 4  
Old 05-14-2011
Try this:
Code:
SQL> select * from emp;

     EMPID FNAME          SALARY
---------- ---------- ----------
       101 Cero             4000
       200 Arun             3000
       459 Chris            3500

instead of:
Code:
SQL> select empid
           ,fname
           ,salary
       from emp;

     EMPID FNAME          SALARY
---------- ---------- ----------
       101 Cero             4000
       200 Arun             3000
       459 Chris            3500

try:
Code:
SQL> select empid||','||
            fname||','||
            salary
       from emp;

EMPID||','||FNAME||','||SALARY
--------------------------------------------------------------------------------
101,Cero,4000
200,Arun,3000
459,Chris,3500

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oracle pmon output needed

$ ps -ef | grep pmon | grep -v grep | awk '{ print $8 }' | cut -d '_' -f3 abc1 abc2 abc3 abc4 abc5 +ASM1 Please use code tags from above output i am looking exclude +ASM output and restout put is fine , also when i select any output and if is invalid from above output, shell script... (12 Replies)
Discussion started by: amar1208
12 Replies

2. Shell Programming and Scripting

Help Needed: UNIX shell variables to store Oracle table records

Hello Folks, I'm working on a requirement to automate the process of generating report(csv file) using metadata info stored in an Oracle table and E-mail it to respective people. Meta data table: Report_ID,Report_SUB_ID,Report_DB,Report_SQL,Report_to_email_Id 1,1,DEV,'select * From... (2 Replies)
Discussion started by: venkat_reddy
2 Replies

3. Shell Programming and Scripting

Extract records from Oracle to UNIX file with headers

Hi, I have a shell script which extracts records form oracle to unix file. sqlplus -s ${WMD_DM_CONNECT} <<EOF >$tmpfile set heading off set pagesize 0 set feedback off select CD_DESC||'|'||CD_ID||'|'||'Arun'||'|'||'Montu' from WMD_SYS_CD_LKUP where CD_TYP =... (5 Replies)
Discussion started by: Arun Mishra
5 Replies

4. Shell Programming and Scripting

shell script for saving oracle database records in variable

i want to retrieve value in each column of each row in sql plus and save them into array variable and echo the value in array variable (2 Replies)
Discussion started by: ramish
2 Replies

5. Shell Programming and Scripting

Help needed for shell scripting for oracle.

Hi, Please see contains both files created for automating the data from oracle through shell. 1)a_p.ksh #!/bin/ksh LOG=/home/A_P.log MESSAGE=/home/MESSAGE_A_P.txt mail_list=/home/AP_MAIL_LIST.txt data=/home/spooled_A_P.log echo "`date` Starting execution for A_P COUNT" > $LOG ... (2 Replies)
Discussion started by: fidelis
2 Replies

6. Shell Programming and Scripting

Oracle Function Needed

Dear Experts, Please find below the script in perl and can any body convert this script exactly in to oracle 9i fiunction which will return the required result same as perl. #!/usr/bin/perl $nof=@ARGV; @var2 = (); for($n=0; $n<$nof; $n++) { $filename = @ARGV; open... (3 Replies)
Discussion started by: shary
3 Replies

7. UNIX for Advanced & Expert Users

unix script for update or insert records from a file to a oracle table

Hi, I have delimited file(|). Sample data: 1|name|50009|DS24|0|12 2|name|30009|DS24|0|13 3|name|20409|DS24|0|14 4|name|20009|DS24|0|15 5|name|10009|DS24|0|16 I want to load this data into a oracle table (update and insert) Please help me the commands and also... (1 Reply)
Discussion started by: unihp1
1 Replies

8. Solaris

oracle on solaris, needed urgent help, require it for exams?

Hi all, I wanted to know how to install oracle on solaris (b83).. I also would like to know of a link that i can download oracle from? please reply fast.. I need it for my exams day after tomorrow... please reply (1 Reply)
Discussion started by: wrapster
1 Replies

9. Shell Programming and Scripting

Script needed to select and delete lower case and mixed case records

HELLO ALL, URGENTLY NEEDED A SCRIPT TO SELECT AND DELETE LOWER AND MIXED CASE RECORDS FROM A COLUMN IN A TABLE. FOR EXAMPLE : Table name is EMPLOYEE and the column name is CITY and the CITY column records will be: Newyork washington ... (1 Reply)
Discussion started by: abhilash mn
1 Replies

10. UNIX for Advanced & Expert Users

Duplicate records from oracle to text file.

Hi, I want to fetch duplicate records from an external table to a text file. Pls suggest me. Thanks (1 Reply)
Discussion started by: shilendrajadon
1 Replies
Login or Register to Ask a Question