OraFAQ Forum: Unix » unix shell script to execute procedure with passing parameters

Home » Infrastructure » Unix » unix shell script to execute procedure with passing parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unix shell script to execute procedure with passing parameters [message #231487]

Tue, 17 April 2007 04:54

rajasekharam
Messages: 12
Registered: July 2005

Junior Member

Hi,

I have screen which was desined in PL/SQL Catridges in apps. In that screen some enterable fields these values r the passing parameters to create value sets, functions, menus etc in apps by using front end screens. Now in that screen i have a button. when i click that button it have to create unix shell script which i need to run this script in unix to compile the package which we have written and pass those screen feild values to the package.

Please give me some thing replated to this idea or if u have any script related script send to me.

Thanks
Rajasekharam

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #231530 is a reply to message #231487]

Tue, 17 April 2007 07:18

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You need to use Java or some external stored procedure to call OS commands from pl/sql.
Search the forum.

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #233241 is a reply to message #231487]

Wed, 25 April 2007 05:51

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

#!/bin/ksh
#@(#)hotbackup_start -- starts S2000 hotbackup
###
# Korn script to start hot backup of the S2000 database
#
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting hot backup of S2000 database
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
df -k /u26
echo `date`
thedir=`pwd`
echo " "
echo Enter password for S2000 ORADBA
sttyops=`stty -g`
stty -echo
echo "Password: "\\c
read password
echo " "
stty $sttyops
#
# Start the hot backup job
#
sqlplus ORADBA/$password << EOF
@$thedir/hotbackup_start
exit
EOF
echo `date`
fi

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #233370 is a reply to message #233241]

Wed, 25 April 2007 18:39

andrew again
Messages: 2577
Registered: March 2000

Senior Member

to run a script from an HTML button, you can invoke the CGI program directly - or if your button submits to your PL/SQL cartridge (mod_plsql) then call the OS command directly from PL/SQL.

 Windows example in 10G
Write your parameters to a temp file read by your script
else re-define the job_action with the actual parameter values passed in.

--   DBMS_SCHEDULER.create_job
--      (job_name        => 'testjob',
--       job_type        => 'EXECUTABLE',
------       job_action      => 'c:\WINNT\system32\cmd.exe /c c:\my_bat.bat',
--       job_action      => 'c:\windows\system32\cmd.exe /c c:\my_bat.bat',
--       enabled         => false
--      );

-- Sync
  DBMS_SCHEDULER.run_job
      (job_name        => 'testjob',
      use_current_session =>TRUE);

Or just use the JAVA route as mentioned...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

[Updated on: Wed, 25 April 2007 18:55]

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #357086 is a reply to message #231530]

Mon, 03 November 2008 17:39

pkumar_2008
Messages: 19
Registered: November 2008
Location: usa

Junior Member

Hi,

I got a requirement to read the text file in unix and pass the values to a procedure.Can you please,advice me..

procedure...is to audit the data in the tables and which will run everyday based on the input parameters.

parameters file contain(dates in this file change everyday)
10/31/2008
11/01/2008

please,help me...

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #357101 is a reply to message #357086]

Mon, 03 November 2008 23:18

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use a external table to read the parameter file and use it in your PL/SQL procedure.

Regards
Michel

Report message to a moderator

Re: unix shell script to execute procedure with passing parameters [message #357288 is a reply to message #357101]

Tue, 04 November 2008 10:57

andrew again
Messages: 2577
Registered: March 2000

Senior Member

to initiate the load while connected to the database, yes I'd use an external table or utl_file package to read the external file (available on the DB server machine). The main advantage is no passwd in external script. To initiate the load from a scipt, you could use sql*loader to load the data into a table and then process the data from there (using trigger to call the proc), or just use sqlplus. Simplified example (date format ommitted, default error handling etc)...

my_host>>cat t.txt           
10/31/2008
11/01/2008
my_host>>cat t.txt | sed "s/^/exec my_proc(\'/;s/$/\');/" > tmp.sql
my_host>>cat tmp.sql
exec my_proc('10/31/2008');
exec my_proc('11/01/2008');
my_host>>echo exit | sqlplus -s scott/tiger@dev @tmp.sql       

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

my_host>>

Report message to a moderator

Previous Topic:	help in AIX
Next Topic:	script not recognising charcters after $

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 16:43:39 CST 2024