while running shell script it's including query also in the

June 28, 2012 at 00:16:40
Specs: Linux
I need to extract the data from the Db and want to import that data in a csv file using shell script. am getting the data from the database and able to import it in a csv file but that csv file is displaying the query also rather than only result. here is the my csv file

SQL>
SQL>
SQL> Select ACTOR_ID ||',' || ACTION_DATE ||',' || ACTION ||',' || AUTH_ID ||',' || STATUS ||',' || REFERENCE_TYPE ||',' || REFERENCE_ID ||',' || REQUEST_DESCRIPTION From (select DET.ACTOR_ID,DET.ACTION_DATE,DET.ACTION,AUTH.AUTH_ID,AUTH.STATUS,AUTH.REFERENCE_TYPE,AUTH.REFERENCE_ID,AUTH.REQUEST_DESCRIPTION from AUTHORIZATIONS AUTH,AUTHORIZATION_DETAILS DET where AUTH.AUTH_ID = DET.AUTH_ID and DET.ACTOR_TYPE = 'STAFF' and DET.ACTION_DATE between '10-MAR-12' and '20-MAR-12' and DET.ACTOR_ID = 'caswh00005');

ACTOR_ID||','||ACTION_DATE||','||ACTION||','||AUTH_ID||','||STATUS||','||REFEREN
--------------------------------------------------------------------------------
caswh00005,17-MAR-12 02.49.47.928029 PM,APPROVE,13319669868530.1037903777404855,
APPROVE,ENTL,13319669868220.7647535032360525,creating a authorization record

caswh00005,17-MAR-12 05.37.35.179592 PM,REJECT,13319768634930.48238336694400263,
REJECT,ENTL,13319768634070.8491164956511383,creating a authorization record

caswh00005,17-MAR-12 06.01.53.532173 PM,APPROVE,13319781770510.26663215063403056
,APPROVE,ENTL,13319781770070.31630752227186565,creating a authorization record

caswh00005,17-MAR-12 06.11.42.538459 PM,APPROVE,13319791024990.04915396818395823
,APPROVE,ENTL,13319791024400.16446275837131075,creating a authorization record

caswh00005,17-MAR-12 06.49.36.903569 PM,REJECT,13319809009220.1318485683360866,R
EJECT,ENTL,13319809008470.9026346873564711,creating a authorization record

caswh00005,17-MAR-12 02.39.28.386181 PM,APPROVE,13319659334030.2765199919258249,
APPROVE,ENTL,13319659333720.7651241175411788,creating a authorization record

SQL>
SQL>
SQL> Spool off!


See More: while running shell script its including query also in the

Report •


#1
July 1, 2012 at 11:49:24
Can you post a copy of the shell script?

Report •

#2
July 2, 2012 at 22:36:50
Hi Here is the my shell script


#! /bin/sh
#SCRIPT: Report.sh
password='sql'
username='sql'
Spool on
sqlplus $username/$password << ENDOFSQL
set colsep ,
set echo off
set show off
set feedback off
set termout off
set pagesize 1000
Spool /opt/report.csv
Select AID ||',' || DATE ||',' || ACTION ||',' || ID ||',' || STATUS ||',' || REFERENCE_TYPE ||',' || REFERENCE ||',' || REQUEST_DESCRIPTION From (select DET.ID,DET.DATE,DET.ACTION,AUTH.ID,AUTH.STATUS,AUTH.TYPE,AUTH.REFERENCE,AUTH.DESCRIPTION from AUTHO AUTH,DETAILS DET where AUTH.AUTH_ID = DET.AUTH_ID and DET.ACTORTYPE = '$2' and DET.ACTORID = '$1');

Spool off
ENDOFSQL
exit


Report •

#3
July 2, 2012 at 23:07:53
this is just guessing on my part, but if you want to try it...


#! /bin/sh
#SCRIPT: Report.sh
set echo off
password='sql'
username='sql'
sqlplus $username/$password << ENDOFSQL
set colsep ,
set show off
set feedback off
set termout off
set pagesize 1000
Spool /opt/report.csv
Spool on
Select AID ||',' || DATE ||',' || ACTION ||',' || ID ||',' || STATUS ||',' || REFERENCE_TYPE ||',' || REFERENCE ||',' || REQUEST_DESCRIPTION From (select DET.ID,DET.DATE,DET.ACTION,AUTH.ID,AUTH.STATUS,AUTH.TYPE,AUTH.REFERENCE,AUTH.DESCRIPTION from AUTHO AUTH,DETAILS DET where AUTH.AUTH_ID = DET.AUTH_ID and DET.ACTORTYPE = '$2' and DET.ACTORID = '$1');

Spool off
ENDOFSQL

I'm ENTIRELY IGNORANT! Just trying to help, so keep that in mind...
ENDOFSQL is piped into this script (?), then called or invoked? at end.
I am making the egregious assumption that everything after "sqlplus" is an sql script to be run. Hope this helps you...


Report •

Related Solutions

#4
July 2, 2012 at 23:18:58
Its not working giving the exception like

SP2-0606: Cannot create SPOOL file "//report.csv"
SQL> SQL> SQL> not spooling currently


Report •

#5
July 3, 2012 at 00:02:29
I'm sorry, like I said, I'm clueless. Here's my last contrib: swap these lines:

Spool /opt/report.csv
Spool on

to:
Spool on
Spool /opt/report.csv

Hope this helps or you can get some real SQL techs on board. I know it's simple.


Report •

#6
July 3, 2012 at 00:07:49
I tried in that way still its printing the sql statements in CSV files ... anyway thanks for your help

I have one more question

how i can pass named parameters from command line like

right now we are calling .sh file by running the below command

sh reportScript.sh vamshi STAFF 17-MAR-12 20-MAR-12 opt/report

how i can pass arguements with some specific names like

sh reportScript.sh -name vamshi -type STAFF -date 17-MAR-12 -enddate 20-MAR-12 -path opt/report


Report •

#7
July 3, 2012 at 22:06:16
can any one please help me out how to pass named parameters (arguements) to a shell script ?

sh report.sh -name vamshi

how we will get the above passed value vamshi in a shell script ?


Report •

Ask Question