How can I change the output of SqlPlusresults with PLSQL?

Question

I have a script that contains plsql codes. The output of the code is so complex. The code is here:

#!/usr/bin/ksh
$ORACLE_HOME/bin/sqlplus<<EOF
x/xx@xxx
set serveroutput on
set sqlnumber off
set sqlblanklines off
set feedback off
DECLARE
is_found_rec boolean := false; 
CURSOR c1
IS
select name from rdsolucadm.BPM_T_SEMAPHORE where value in  ('0') and description like 'R%'; 
BEGIN
    FOR r1 IN c1
    LOOP  
        is_found_rec := true;
        update  RDSOLUCADM.BPM_T_SEMAPHORE set value ='2' where value in ('0') and description like 'R%';
        commit;
        dbms_output.put_line ('BPM_T_SEMAPHORE table is updated.');
    END LOOP; 
    if not is_found_rec then 
        dbms_output.put_line ('BPM_T_SEMAPHORE table is NOT updated!!!');
    end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END;
/
exit
EOF

And the result is as below:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> BPM_T_SEMAPHORE table is NOT updated!!!

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

I dont want to see lots of "SQL> SQL> SQL> SQL> SQL> ....." How to set this properties? Could you help me please ?

Thanks, Best Regards

mmmmmpie · Accepted Answer · 2015-03-13 12:15:08Z

You're simply missing a -s flag in your call to sqlplus.
Example code:

oracle@***:/home/oracle/testing> cat test.sh
$ORACLE_HOME/bin/sqlplus -s<<EOF
XXX/XXX
set serveroutput on
set sqlnumber off
set sqlblanklines off
set feedback off
BEGIN
        dbms_output.put_line('testing');
END;
/
exit
EOF

Example output without the -s flag:

oracle@***:/home/oracle/testing> sh test.sh

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 13 08:12:21 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> testing
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Example with the -s flag:

oracle@XXX:/home/oracle/testing> sh test.sh
testing

Collectives™ on Stack Overflow

How can I change the output of SqlPlusresults with PLSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related