Home » SQL & PL/SQL » Client Tools » set echo on is not working in shell scripts (oracle 10g, linux)
set echo on is not working in shell scripts [message #557331] Mon, 11 June 2012 23:35 Go to next message
y.michael
Messages: 7
Registered: June 2012
Location: Boston
Junior Member
Hi,

SET ECHO ON is not working in shell script. It works in command prompt and also shell prompt. whenever i run the shell scripts, set echo on is not working.

I want output and its query in spool file.

could you give an idea ?



set -e
set +x

#Executes SVR parameter scripts Sets all the application specific properties
 . $SCRIPTS_PATH/svrparam.sh

  $ORACLE_HOME/bin/sqlplus -s ${SOLV_DB_USER}/${SOLV_DB_PASS}@${SOLV_DB_SERVER}<<EOF
        set echo on
        set termout on
        spool ${SVR_LOG_PATH}/solv_outbound_count_check.txt
         --das staging count
            SELECT COUNT(1)
                FROM das_security_stg2
                WHERE solv_ind='Y';

                --unique asset id count

           SELECT COUNT(1)
           FROM (SELECT DISTINCT sec_ssc_id
                 FROM holding_dynamic
                 WHERE as_of_dt = (SELECT TO_DATE(pkg_solv_common.fnc_get_valuation_dt,'yyyymmdd')
                                   FROM dual));


                --security outbound count
                SELECT COUNT(1)
                FROM v_security_outbound;
spool off
EOF


Re: set echo on is not working in shell scripts [message #557335 is a reply to message #557331] Tue, 12 June 2012 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not work in an interactive session.
"<<" simulates an interactive.
So "set echo" is irrelevant.
Put your script in a real SQL script file and call the file from you shell script.

Regards
Michel

Re: set echo on is not working in shell scripts [message #557564 is a reply to message #557335] Wed, 13 June 2012 21:47 Go to previous messageGo to next message
y.michael
Messages: 7
Registered: June 2012
Location: Boston
Junior Member
i am not able to passing variables from shell scripts to sql file. i struck in the middle of the process. could you please help on this?

sql file
CONNECT '&1'/'&2'/@'&3'
spool '&4'/solv_outbound_count_check.log
set feedback off
set echo on
set pagesize 0
set linesize 100
set space 0
set serveroutput on
--das staging COUNT
SELECT COUNT(1)
FROM   das_security_stg2
WHERE  solv_ind = 'Y';

--unique asset id COUNT
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec_ssc_id
        FROM   holding_dynamic
        WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
                           FROM   DUAL));

--security outbound COUNT
SELECT COUNT(1)
FROM   v_security_outbound;

--security dynamic COUNT
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec.sec_ssc_id
        FROM   security_dynamic secdy,
               sol_security sec
        WHERE  secdy.security_ky  = sec.security_ky
               AND secdy.as_of_dt = (SELECT TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
                                     FROM   DUAL));


shell script:
#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH

set -e

#Executes SVR parameter scripts Sets all the application specific properties
 . $SCRIPTS_PATH/svrparam.sh

$ORACLE_HOME/bin/sqlplus -s  /nolog @${SCRIPTS_PATH}/solv_outbound_veri_query.sql \
${SOLV_DB_USER} ${SOLV_DB_PASS} ${SOLV_DB_SERVER} ${SVR_LOG_PATH}


SUB="Solvency outbound file count of current month"
MESSAGE="${SVR_LOG_PATH}/solv_outbound_count_check.txt"
              /bin/mailx -s "$SUB" "$SOL2_SUPPORT_GRP_EMAIL" < "$MESSAGE"



Please help on this?

[Updated on: Wed, 13 June 2012 23:17] by Moderator

Report message to a moderator

Re: set echo on is not working in shell scripts [message #557565 is a reply to message #557564] Wed, 13 June 2012 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>CONNECT '&1'/'&2'/@'&3'
above is not valid syntax

First make it work, then make it fancy.
When you can't get logged into the DB, all else is nothing but clutter.
Re: set echo on is not working in shell scripts [message #557566 is a reply to message #557335] Wed, 13 June 2012 22:46 Go to previous messageGo to next message
y.michael
Messages: 7
Registered: June 2012
Location: Boston
Junior Member
This is not an issue. I have removed '-s' from sqlplus connection string afterwards set echo on is working in shell scripts.
Re: set echo on is not working in shell scripts [message #557567 is a reply to message #557566] Wed, 13 June 2012 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>This is not an issue.
Congratulations in solving your problem.
Re: set echo on is not working in shell scripts [message #557568 is a reply to message #557567] Wed, 13 June 2012 23:29 Go to previous messageGo to next message
y.michael
Messages: 7
Registered: June 2012
Location: Boston
Junior Member
I am getting output like below

SQL>         connect solvency/solvency@O03GPA0
Connected.
SQL> 		--das staging count
SQL> 		SELECT COUNT(1)
  2  		FROM   das_security_stg2
  3  		WHERE  solv_ind = 'Y';
!

SQL> 
SQL> 		--unique asset id count
SQL> 		SELECT COUNT(1)
  2  		FROM   (SELECT DISTINCT sec_ssc_id
  3  				FROM   holding_dynamic
  4  				WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
  5  						   FROM   DUAL));
!


i want to only query and its outputs.

my expected output


--unique asset id count
SELECT COUNT(1)
FROM   (SELECT DISTINCT sec_ssc_id
FROM   holding_dynamic
WHERE  as_of_dt = (SELECT  TO_DATE(pkg_solv_common.fnc_get_valuation_dt, 'YYYYMMDD')
 FROM   DUAL));

---
0



i want to suppress SQL> and line numbers.
Re: set echo on is not working in shell scripts [message #557571 is a reply to message #557568] Thu, 14 June 2012 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do as i said.

Regards
Michel
Re: set echo on is not working in shell scripts [message #557682 is a reply to message #557331] Thu, 14 June 2012 14:08 Go to previous messageGo to next message
y.michael
Messages: 7
Registered: June 2012
Location: Boston
Junior Member
You are not my mentor and not lengend. This is general forum for everyone. We will share own ideas for implement in different ways.

Have you ever write any oracle book?. Do you COE of this forum. why are you behalf like this?

Thanks,
Michael
Re: set echo on is not working in shell scripts [message #557712 is a reply to message #557682] Thu, 14 June 2012 23:17 Go to previous message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Like what?
I gave you THE solution.
You don't like it, at least tell why instead of unfairly flaming.

Regards
Michel
Previous Topic: CSV Export Functionality
Next Topic: SQLPLUS stops spool
Goto Forum:
  


Current Time: Thu Feb 22 08:37:59 CST 2018

Total time taken to generate the page: 0.02357 seconds