Generating file from sqlplus

Shell Programming and Scripting
1

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:

#----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:

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)

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:

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

2

How do you expect us to help?
You havent given us the query you passed nor the format of the tables...

3

Hi,

apologize...:frowning:

PFB the query:

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 :

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

4

what is the output your getting ( just 3 lines enough...) so I can understand...

5

It appears to be sqlplus settings are a problem.

set linesize [LARGE value]

try a LARGE value of something like 999

Then add:

set trimspool on

All of this before the qww.sql executes.

6

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

m getting data like this:

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

8

so add in your script after page size set linesize 500 and see what the output gives now