Home » Infrastructure » Unix » sql and plsql inside shellscripts
sql and plsql inside shellscripts [message #239140] Mon, 21 May 2007 09:55 Go to next message
saiora
Messages: 3
Registered: May 2007
Junior Member
Hello all,
I am not an expert firstof all but I would really appreciate if you ppl could help me with my question. I would really appreciate if you experts show me sample shell scripts which shows
1. how to connect to oracle database
2. how to execute a stored procedure from shell script
3. how do we schedule oracle jobs using cron


also please tell me if we can write a complete stored procedure or package or plsql block inside a shell scripts???
I am new naive to the concept of linking oracle with shell scripts. i want scripts which talks to sql and plsqlblocks

I hope you guyz have understood my problem and would really appreciate if you ppl could help me out as quckly as possible because my job is gonna start next week and iam supposed to be good at linking sql/plsql with shellscriptsPLEASE HELP ME GUYZ with the sample scripts which clarifies my doubts.

also please suggest me some books which deal with oracle with shell scripts
PLEASE DONT IGNORE THIS POST BY CONSIDERING IT AS A NAIVE ONE

thanks
sai





Re: sql and plsql inside shellscripts [message #239143 is a reply to message #239140] Mon, 21 May 2007 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Is GOOGLE broken for you?

Did you search this forum before posing this FAQ?

[Updated on: Mon, 21 May 2007 09:57] by Moderator

Report message to a moderator

Re: sql and plsql inside shellscripts [message #239148 is a reply to message #239140] Mon, 21 May 2007 10:07 Go to previous messageGo to next message
saiora
Messages: 3
Registered: May 2007
Junior Member
sorry I was tensed because my job needs that and more over iam new to shellscripts.

ishall search this forum and iam also googling it.


Re: sql and plsql inside shellscripts [message #239232 is a reply to message #239148] Mon, 21 May 2007 16:20 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
take a look at this example


#!/bin/ksh -x
# make env file avail to this script
. $HOME/.profile_mydb
$ORACLE_HOME/bin/sqlplus /NOLOG <<HERE
-- Exit with failure, if SQL, PL/SQL or OS error is raised in top level proc
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
CONNECT oracle/password
Select tablespace_name from dba_tablespaces;
Exit
Exit 0




just save the code in file and save it with .ksh


and simply run then from ur console.


like
oracle>. myfile.ksh

[Updated on: Mon, 21 May 2007 16:22]

Report message to a moderator

Re: sql and plsql inside shellscripts [message #239561 is a reply to message #239140] Tue, 22 May 2007 12:05 Go to previous messageGo to next message
saiora
Messages: 3
Registered: May 2007
Junior Member
hi
thanks
Re: sql and plsql inside shellscripts [message #239613 is a reply to message #239561] Tue, 22 May 2007 14:29 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
dev1>>cat sqlplus_proc.ksh
## create or replace procedure my_proc (p_rows in number) is
## begin
##    for i in (select object_id * rownum * rownum big_num, object_name, created
##                from user_objects where rownum <= p_rows) loop
##       dbms_output.put_line 
##           (lpad (i.big_num, 15, ' ')|| ' ' || rpad (i.object_name, 15, ' ')|| i.created);
##    end loop;
## end;
## /

user=xxx
pass=yyy
db=devdb

num_rows=5

sqlplus -s /nolog <<EOF > tmp.txt
  whenever sqlerror exit sql.sqlcode rollback
  connect $user/$pass@$db
  set feedback off
  --default date format (ideally not needed - should be controlled in proc)
  alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
  --preserve leading spaces and set dbms_output size
  --size maybe not needed in 10g server?
  set serveroutput on size 1000000 format wrapped 
    exec my_proc($num_rows);
  --exit sql.sqlcode;
EOF
rv=$?

echo "---- start output ----"
grep -v "Connected." tmp.txt
echo "---- end output ----"

echo Retval is $rv


then in crontab, just specify the env profile, sript and log...
# minute (0-59) = 00
# hour (0-23) = 16
# day of the month (1-31) = 8-14
# month of the year (1-12) = * (ALL)
# day of the week (0-6 with 0=Sunday) = 3
# Min Hr DayOfMon  Mon DayOfWk Script
# 00  16 8-14      *   3       . ~/cron_env; ~/sqlplus_proc.ksh >sqlplus_proc.cron.log 2>&1
Previous Topic: How to instal Oracle 10.2.0.2 on Solaris 10 SUn SPARC??
Next Topic: calling procedure in unix shell script-1
Goto Forum:
  


Current Time: Fri Dec 09 15:21:52 CST 2016

Total time taken to generate the page: 0.32190 seconds