Saturday, May 25, 2013
How to compile mutiple PLSQL Packages using Shell Script
Consider that we have bulk amount of packages(.pkg,.pkb,.sql) which needed to be compiled from development to production. Initial days, we download packaged from SVN and compile each of the packages one by one using SQL Developer and SQLPlus utility. As the days go by we mostly occupied with this bulk and repetitive package compilation activity.
We got a question ? Is there any way that we can write a shell script so that i can run all my packages in one go ?
Finally we have created a shell script, which would do the bulk compilation of Package Specification(.pkg),Package Body(.pkb) and SQL Packages(.sql) in one shot.To know more about it, just keep on reading !!
Flow of the script:
Script Content:
#!/bin/bash
tar_file_location=$1
clear_event()
{
echo "Clearing of temp files in progress!!"
rm *.pkg *.pkb *.sql
rm *.zip
}
download_file()
{
echo "Download Packages from SVN!!"
wget $tar_file_location
echo "Extracting Compressed Packages!!"
tar -xvzf *.zip
echo "Moving Packages!!"
mv packages_to_compile\/* .
}
translate_file_name()
{
ls * |grep -i .pkb > body_files.txt
ls * |grep -i .pkg > spec_files.txt
ls * |grep -i .sql > sql_files.txt
while read line
do
echo "Filename conversion is in progress....!!"
filename=`echo $line | awk '{print $1}'`
mod_filename=`echo $filename|tr 'A-Z' 'a-z'`
mv $filename $mod_filename
done < body_files.txt
while read line
do
echo "Filename conversion is in progress....!!"
filename=`echo $line | awk '{print $1}'`
mod_filename=`echo $filename|tr 'A-Z' 'a-z'`
mv $filename $mod_filename
done < spec_files.txt
}
get_filename()
{
ls * |grep -i .pkb > body_files.txt
ls * |grep -i .pkg > spec_files.txt
}
generate_slash_spec()
{
echo "Generating Slash for Specification packages!!"
while read line
do
filename=`echo $line | awk '{print $1}'`
slash_check="tail -n 1 $filename"
if [ "$slash_check" = "/" ]; then
echo "Change is not required"
else
echo -e "\n /" >> $filename
fi
done < spec_files.txt
}
generate_slash_body()
{
echo "Generating Slash for body Packages!!"
while read line
do
filename=`echo $line | awk '{print $1}'`
slash_check="tail -n 1 $filename"
if [ "$slash_check" = "/" ]; then
echo "Change is not required"
else
echo -e "\n /" >> $filename
fi
done < body_files.txt
}
package_compile()
{
echo "Compilation of Specification packages are in Progress!!"
nl -s:@/Packages/ sql_files.txt > seql_scripts.sql
cat seql_scripts.sql|cut -d':' -f2 |sort > seql_scripts_tmp.sql
nl -s:@/Packages/ packages_spec.txt > packages_spec.sql
cat packages_spec.sql|cut -d':' -f2 |sort > packages_spec_tmp.sql
echo "Compilation of Body Packages are in Progress!!"
nl -s:@/Packages/ packages_body.txt > packages_body.sql
cat packages_body.sql|cut -d':' -f2 |sort > packages_body_tmp.sql
}
generate_final_file()
{
echo "Generate File!!"
cat packages_spec_tmp.sql > final_file.sql
cat packages_body_tmp.sql >> final_file.sql
cat seql_scripts_tmp.sql > final_seql_scripts.sql
}
remove_temp_file()
{
rm packages_spec.txt packages_body.txt seql_scripts.sql
rm body_files.txt spec_files.txt
rm seql_scripts_tmp.sql packages_spec_tmp.sql packages_body_tmp.sql
}
run_packages()
{
while read line
do
filename=`echo $line | awk '{print $1}'`
filename1=`echo $filename|cut -d "/" -f5`
echo "Package Compilation:" $filename1
sqlplus -S DEV_USER/devpasswd <whenever sqlerror exit sql.sqlcode;
set define off
$filename
exit;
EOF
done < final_file_core.sql
}
help()
{
echo "Usage:$0 <>"
echo "Example:$0 http://localhost:7000/view/dest/CodingDEV_packages_20121120_062936.tar.zip"
}
if [ $# -eq 1 ]
then
clear_event
download_file
translate_file_name
get_filename
generate_slash_spec
generate_slash_body
package_compile
generate_final_file
remove_temp_file
run_packages_core
else
echo "$0 : You must apply correct no of arguement"
help
exit 1
fi
Subscribe to:
Post Comments (Atom)
run_packages_core is missing, Can you upload that one also
ReplyDelete