Help needed in retreiving records from Oracle

Shell Programming and Scripting
1

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

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

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

Try this:

SQL> select * from emp;

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

instead of:

SQL> select empid
           ,fname
           ,salary
       from emp;

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

try:

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

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