Home » SQL & PL/SQL » SQL & PL/SQL » simple Shell Script - howto do the same in PL/SQL (Database is Oracle Database 10g Enterprise Edition Release on Solaris.)
icon3.gif  simple Shell Script - howto do the same in PL/SQL [message #575379] Tue, 22 January 2013 12:22 Go to next message
Messages: 88
Registered: May 2009
I have a simple shell script with SQL code which does:

- generate with SQL*Plus (SQL statement) a batch file
- checks if output from SQL*Plus more than 400 lines (if more than 400 lines exit and writes mail to Operations team)
- if less than 400 lines SQL*Plus output, executes the batch file automatically

This script works very well. I wish to write the same script with PL/SQL (without Shell code). Is this possible? Can you provide me the code (I am in process of learning PL/SQL).

. /opt/db/scripts/setpath.sh

generate_batch ()
sqlplus -S $DBUSER/$DBPASSWD@$ORACLE_SID <<EOF > /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh')

set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 20000

WITH data
    AS (SELECT user_id,
               RANK () OVER (PARTITION BY user_id ORDER BY upd_time ASC)
          FROM user_jc
         WHERE user_id IN (  SELECT user_id
                               FROM user_jc
                              WHERE JC_NAME LIKE 'CFF\_S\_%' ESCAPE '\'
                           GROUP BY user_id
                             HAVING COUNT (user_id) > 1)
               AND JC_NAME LIKE 'CFF\_S\_%' ESCAPE '\')
SELECT    'DISCONNECT ent_user  FROM job_code WITH user_id = "'
   || user_id
   || '", jc_name = "'
   || jc_name
   || '";'
  FROM data
 WHERE rk = 1;


sanity_check ()
line_nr=$(wc -l /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh') | awk ' { print $1 } ')
if [ $line_nr -gt 400 ]; then
        (cat /opt/db/scripts/tools/delete_connection/mail_body.txt) | mailx -s "Alert: please manually execute /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$DATE" -r test@example.com test2@example.com
        exit 1

run_batch ()
/opt/bmchome/bin/ess batchrun -A -i /opt/db/scripts/tools/delete_connection/batchrun/batchrun.$(/bin/date '+%d%m%Y.%Hh')

generate_batch && sanity_check && run_batch

what you think? Stupid idea to write in PL/SQL? Better in Shell?
Re: simple Shell Script - howto do the same in PL/SQL [message #575380 is a reply to message #575379] Tue, 22 January 2013 12:54 Go to previous message
Michel Cadot
Messages: 59811
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this possible?

Yes but executing the content of the file if it is not PL/SQL one (no easy way to do it in 10g).

Can you provide me the code (I am in process of learning PL/SQL).

No, it is a good exercise to learn PL/SQL.
Hint: use UTL_FILE package to write a file.

Better in Shell?

If you have it in Shell and it works and it does what you want it does, I don't see any reason to change for something you are ot sure it will work.

Previous Topic: checking a record exists
Next Topic: moving data to another table
Goto Forum:

Current Time: Thu Nov 27 11:22:57 CST 2014

Total time taken to generate the page: 0.08986 seconds