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 10.2.0.4.0 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
oranooob
Messages: 88
Registered: May 2009
Member
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).

#!/bin/ksh
. /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,
               jc_name,
               upd_time,
               RANK () OVER (PARTITION BY user_id ORDER BY upd_time ASC)
                  rk
          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;

exit
EOF
}

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
        fi
}

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: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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).

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

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

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


Current Time: Sun Sep 21 09:50:23 CDT 2014

Total time taken to generate the page: 0.09470 seconds