Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Generating file from sqlplus

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Generating file from sqlplus
# 1  
Old 08-30-2013
Generating file from sqlplus
Hi Frndz,

I have req in which i need to run the query stored in some file and then store the resultset of the query in some other file.

PFB the code i have written:
Code:
#----Start job log---

SQLLOG='/opt/app/vertica2/cdr_dev/logs/conn_orac_db.log'

ORA_USER='abc'

ORA_PASSWD='xyz'

ORA_CONN='pqr'

ORA_QUERY='qww.sql'

echo "Connecting to Oracle: `date +'%Y-%m-%d %H:%M:%S'`" > $SQLLOG

echo "SQL Script Name:$ORA_QUERY " >> $SQLLOG

echo " " >> $SQLLOG

cd $ORACLE_HOME

sqlplus -s /nolog << EOF >> $SQLLOG 2>&1

conn $ORA_USER/$ORA_PASSWD@$ORA_CONN

set colsep '^]'

set echo off

set feedback off

set pagesize 0

set wrap off

 spool $ORA_RESULT

 @$ORA_QUERY

 disconnect

EOF

RC="$?"

echo "Return code: $RC" >> $SQLLOG

echo " " >> $SQLLOG

echo "Connection end: `date +'%Y-%m-%d %H:%M:%S'`" >> $SQLLOG

echo " " >> $SQLLOG




Now i am able to genrate the file with required resultset...the only prob is.i am getting data in below format:
Code:
rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

SGSWKJJ6IXFH
SGSWNLDMW8VY
SGSW99N855JE
SGSWJG5K2WVJ

My actual data is (in oracle)
Code:
SGSWKJJ6IXFH|8/25/2012 2:31:20 AM|%|Single Use|||3rd Party|0011917||5% off $50 storewide products_9_1_12||9/1/2012 7:00:00 AM|12/1/2012 6:59:00 AM|8/25/2012 2:31:20 AM|8/25/2012 2:31:20 AM

Note:i used symbol "|" jus to differentiate my data.

and my expected data should be:
Code:
SGSWKJJ6IXFH^]8/25/2012 2:31:20 AM^]%^]Single Use^]^]^]3rd Party^]0011917^]^]5% off $50 storewide products_9_1_12^]^]9/1/2012 7:00:00 AM^]12/1/2012 6:59:00 AM^]8/25/2012 2:31:20 AM^]8/25/2012 2:31:20 AM

Please give ur inputs frndz..need it urgently>

TIA
Last edited by Scott; 08-30-2013 at 10:28 AM.. Reason: Code tags
# 2  
Old 08-30-2013
How do you expect us to help?
You havent given us the query you passed nor the format of the tables...
# 3  
Old 08-30-2013
Hi,

apologize...Smilie

PFB the query:
Code:
select coupon_code as coupon_cd,
              date_modified as modified_dt,
              discount_type,
              coupon_type,
              discount_value,
              distribution_method,
              distribution_type,
              hps_campaign_id,
              hps_campaign_description as hps_campaign_desc,
              short_description as short_desc,
              long_description as long_desc,
              redemption_start_date as redemption_start_dt,
              redemption_stop_date as redemption_stop_dt,
              ia_insert_dt as insert_dt, 
              ia_update_dt as update_dt
    from bidw.v_sas_coupon
    where rownum <= 100
	;

Table format is :
Code:
COUPON_CODE,-varchar2(128)
 COUPON_TYPE, -VARCHAR2 (10 Byte)
DATE_MODIFIED,-DATE
 DISCOUNT_TYPE,-VARCHAR2 (1 Byte)
DISCOUNT_VALUE, -NUMBER (15)
DISTRIBUTION_METHOD,-VARCHAR2 (255 Byte)
 DISTRIBUTION_TYPE,-VARCHAR2 (21 Byte)
 HPS_CAMPAIGN_DESCRIPTION,-VARCHAR2 (255 Byte)
 HPS_CAMPAIGN_ID, -VARCHAR2 (256 Byte)
LONG_DESCRIPTION, -VARCHAR2 (255 Byte)
REDEMPTION_START_DATE, -DATE
REDEMPTION_STOP_DATE,-DATE
 SHORT_DESCRIPTION, -VARCHAR2 (2000 Byte)
IA_INSERT_DT, - -DATE
IA_UPDATE_DT -DATE


Please let me know if i provided all details or not
TIA
Last edited by Scott; 08-30-2013 at 11:32 AM.. Reason: CODE tags NOT ICODE tags, please.
# 4  
Old 08-30-2013
what is the output your getting ( just 3 lines enough...) so I can understand...
# 5  
Old 08-30-2013
It appears to be sqlplus settings are a problem.

Code:
set linesize [LARGE value]

try a LARGE value of something like 999

Then add:
Code:
set trimspool on

All of this before the qww.sql executes.
# 6  
Old 08-30-2013
That is what I thought... but without seeing actual output it will be difficult to guess to correct size...
As I noticed linesize was not set...
# 7  
Old 08-30-2013
m getting data like this:
Code:
rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

SGSWKJJ6IXFH
SGSWNLDMW8VY
SGSW99N855JE
SGSWJG5K2WVJ

rest of the coloumn's data is not at all coming...
Last edited by Scott; 08-30-2013 at 11:51 AM.. Reason: Code tags, please...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Generating xml file from UNIX

i have a unix script which generates the csv file. the data in csv file is dynamic. how can i convert/move the data from csv file to xml. please suggest (1 Reply)
Discussion started by: archana25
1 Replies

2. Shell Programming and Scripting

Check to see if a file is generating

Hi guys, I am pulling my hair out here. I have a file that comes in, once it finishes i want to move it to a new location. This sounds all very easy but my solution is failing and moves the file before it has finished generating. isrun=`ps -ef | grep -i filename | grep -v grep | wc -l` ... (8 Replies)
Discussion started by: twinion
8 Replies

3. UNIX for Dummies Questions & Answers

Generating a CSV file from a text file

Hi Guys, I have a simple request. I have a file in w3c format. Each file has 2 header lines. Rest of the lines are 16 columns each. They are separated by Tab. I need to discard the first 2 lines and then write each column of the txt file into a seperate column of CSV. I tried the command below... (1 Reply)
Discussion started by: tinkugadu
1 Replies

4. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

5. UNIX for Dummies Questions & Answers

Generating a Config File

HI, I want to append some configuration statements to the existing file in unix to it's 3 line , 7th line and 28 line. There is a file generated by our system job, to this I need to add some lines as mentioned below. DIR=/usr/CDR - line 3 Source=/usr/src - line 7 Target=/usr/tgt - line... (2 Replies)
Discussion started by: mora
2 Replies

6. Emergency UNIX and Linux Support

String search and generating file

Hi Gurus, I have a requirement as below I have text file a.txt which contains hi hello process update status Output for file Ok to Proceed no issues good data arrangement My requirement here is i need to read the file and check for the words "OK to Proceed" and if it is available... (14 Replies)
Discussion started by: pssandeep
14 Replies

7. Shell Programming and Scripting

Performance issue in UNIX while generating .dat file from large text file

Hello Gurus, We are facing some performance issue in UNIX. If someone had faced such kind of issue in past please provide your suggestions on this . Problem Definition: /Few of load processes of our Finance Application are facing issue in UNIX when they uses a shell script having below... (19 Replies)
Discussion started by: KRAMA
19 Replies

8. UNIX for Dummies Questions & Answers

Generating different columns from same file

Hi, Our requirement is we have to create file from one file where all the rows from source file is converted into columns of the target file. For example Source file is : Status Report ABC Generated: 2009-05-05 08:49:47 Job start time=2009-05-05 08:43:43 Job end time=2009-05-05... (1 Reply)
Discussion started by: Amey Joshi
1 Replies

9. Shell Programming and Scripting

Ksh - finding pattern in file and generating a new file

I am trying to find for the pattern in first 5 bytes of every line in a text file and then generate a new file with the pattern found. Example: expected pattern is '-' to be serached in first 5 bytes of file only. Input File ab-cd jan-09 ddddd jan09 cc-ww jan09 dsgdq jan-09 ... (2 Replies)
Discussion started by: net
2 Replies

10. Shell Programming and Scripting

Bash Scripts - File generating

Forgive the daft requests - I'm still a learner :D I need a script so that I can test another script (I'm confused already) The script I am looking for should generate a new file in the same directory (called newfile1 or what ever) and also generate text within the new file (Hello world? What... (1 Reply)
Discussion started by: JayC89
1 Replies
Login or Register to Ask a Question