Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

UNIX shell scripting for retrieving from oracle

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting UNIX shell scripting for retrieving from oracle
# 1  
Old 07-06-2006
UNIX shell scripting for retrieving from oracle
Hello folks,
Please find the below codeSmiliesample5.sh -> filename)

echo "Selecting dat afrom Cause code"
echo "set appinfo Causecode
$preamble
set serveroutput on size 10000
select * from RMI003_CAUSE_CODE /" | sqlplus -S $username@$hoststring/$password >> test2.dat

When i tried executing the code using "SH" i am getting a file "test2.dat",but the file doesn't have any records. Please suggest me where i am wrong.


Thanks,
Sundar.
# 2  
Old 07-06-2006
Try this script

X=`sqlplus -s user/password@host <<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select * from tablename;
EXIT;
eof`

echo $X>testing.dat

Here in X you will get the output and after exiting from sql session put that output in some file.May be this will solve your problem.....
# 3  
Old 07-06-2006
Quote:
Originally Posted by sundar_ravi4
select * from RMI003_CAUSE_CODE /" | sqlplus ...[/I]
What is the meaning of the slash character (/)? Its placement is wrong in your code. You either need to put it on a line by itself of replace it with a semicolon ( ; ).
# 4  
Old 07-06-2006
tmarikle:

Actually old form of Oracle sql statments were in a block ended by a slash. If he added a \n character it would work.

This is legal SQL, try it:
Code:
select 1 from dual
/

Smilie
# 5  
Old 07-06-2006
Yes / is absolutely right but that is required when pl/sql block is used from shell script.here ; would have done the job.
# 6  
Old 07-06-2006
Jim,

I must not have been as clear as I thought, that's actually what I was trying to communicate to the OP. Smilie I was trying to point it out but also prompt an answer so that perhaps the OP could see his own error. Oh well, perhaps next time I'll be more plain. Smilie

Thomas
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

2. Forum Support Area for Unregistered Users & Account Problems

Creation of Oracle table through shell scripting

Hi, I am trying to create a table through shell scripting . I used a command sqlplus -s to connect to sqlplus and user as sysdba. but there is a problem in it. can anyone please solve this . #!/bin/bash $(`sqlplus -s / as sysdba <<eof create table sample (id,int); insert into sample... (1 Reply)
Discussion started by: Unregistered
1 Replies

3. UNIX for Dummies Questions & Answers

Oracle cursors in UNIX shell scripting

Hello, I need to get all the members information from an oracle table whose flag value is enabled. Later on i need to perform several computation based upon the flag value and other columns. For example, Member ID Flag Frequency date 1 ... (2 Replies)
Discussion started by: Krishraj
2 Replies

4. Shell Programming and Scripting

How to update a Oracle table through shell scripting?

My Code is get_week_date() { `sqlplus -s ${DQM_SQL_LOGON}@${DQM_SID} << EOF SET ECHO OFF SET FEEDBACK OFF SET PAGES 0 SET SERVEROUTPUT ON SET VERIFY OFF SET TRIMSPOOL ON (update file_level_qc fq set FQ.DATA_FILE_NAME='Hyvee_Pharmacy_Solutions_201304_v1.txt'... (2 Replies)
Discussion started by: karthick.cho
2 Replies

5. Shell Programming and Scripting

scope of oracle plsql development /unix shell scripting

I am working in production support in an leading bank and more of my job is more in oracle/plsql. I am learning unix shell scripting and have started development in the same.I would like to know regarding the scope of these two in the industry.Also would like to know about other scripting... (2 Replies)
Discussion started by: jesthomas
2 Replies

6. 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

7. Shell Programming and Scripting

Help need urgently for oracle cursors in k shell scripting

Hi, My Oracle Stored procedure returns sys_refcursor to shell script. I have to iterate through it in script and use those retrieved values further in my script. I am using K Shell Scrpting. Stored Procedure is: create or replace PROCEDURE p_test(job_id IN VARCHAR2, c1 OUT SYS_REFCURSOR)... (4 Replies)
Discussion started by: rajeshorpu
4 Replies

8. Shell Programming and Scripting

difference between AIX shell scripting and Unix shell scripting.

please give the difference between AIX shell scripting and Unix shell scripting. (2 Replies)
Discussion started by: haroonec
2 Replies

9. Shell Programming and Scripting

Shell Scripting with Oracle...

Hi guys, I have searched all the forums and searched the net as much as possible, but I carn't find any tutorials about shell scripting with Oracle. Has anyone come across any? Thanks for reading and in advance of posts, B14... (2 Replies)
Discussion started by: B14speedfreak
2 Replies

10. Shell Programming and Scripting

Unix and Oracle scripting

Hello all, I have a script that is fully functional and satisfactory in oracle. I recently started spooling it's output into unix for processing and manipulation. I do have a few problems however: The output of some columns are altered in the unix output: 1) On oracle, the date comes with a... (7 Replies)
Discussion started by: Khoomfire
7 Replies
Login or Register to Ask a Question