Visit Our UNIX and Linux User Community


Starting a script from a Database procedure


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Starting a script from a Database procedure
# 1  
Old 01-18-2002
Data Starting a script from a Database procedure

Hey guys,

I have this problem : I can run on an sqlprompt

!ftp_file2.ksh test.xml 172.16.204.81 Anonymous Anonymous

which will ftp the file from Unix to an NT machine, but if I do

exec shell('sh ftp_file2.ksh test.xml 172.16.204.81 Anonymous Anonymous') it does NOTHING.

I have no .netrc file in my home directory and don't know how to build it, but I even don't know if this file is necessary for me ?

Can u please help ??????Smilie
# 2  
Old 01-18-2002
I've used this package and daemon process to execute system commands from the database, it worked for what I wanted. it may be worth lookin at. I needed to execute a shell script after some sql was triggered

hth

http://www.greatchinatelecom.com/card/oracle/14082.html
# 3  
Old 01-18-2002
Thx edog !

But the problem is that it took already one month to let dba's and unix people set up the method with the shell procedure and next week we'll go in production already so I can't use your option (But I will definitely use it in a next project) !

Smilie

by the way, here 's the code from the script I start


###############################################################################
# --------------
# Parm's control
# --------------
INPUTFILE=test.xml
DESTINATION=172.16.204.81
USERID=Anonymous
PASSWD=Anonymous
DIRECTORY=""
CHANGE_DIR='No'
# ---------------
# Execute the FTP
# ---------------
if [[ "$CHANGE_DIR" = "No" ]] ; then
ftp -n ${DESTINATION} >templog1$$ >>templog2$$ << EOF
user ${USERID} ${PASSWD} account
put ${INPUTFILE}
EOF
RCOD=$?
# ---
else
# ---
ftp -n ${DESTINATION} >templog1$$ >>templog2$$ << EOF
user ${USERID} ${PASSWD} account
cd ${DIRECTORY}
put ${INPUTFILE}
EOF
RCOD=$?
fi
# --> Save return code
RCOD=$?
# --> Remove logs file (to comment for debug)
rm templog1$$ templog2$$
# --> check FTP run
if [ $RCOD -ne 0 ] ; then
echo "ERROR: Load of file $INPUTFILE crash with error $RCOD"
exit 2
fi
# ---------------------
# Now it's time to exit
# ---------------------
exit $RCOD
# 4  
Old 01-18-2002
exec shell('sh ftp_file2.ksh test.xml 172.16.204.81 Anonymous Anonymous') it does NOTHING.



what does your sql procedure, 'shell' look like? I assume its a custom procedure, maybe if you enter the full path of the ksh script, but I'd need to see the procedure venture any other guesses.

when you execute it from sqlplus your .profile probably has the path to ftp_file2.ksh or you were in that directory when you started sqklplus, but from a sql procedure there is no profile to find the script? just a thought
# 5  
Old 01-21-2002
(sorry for the delay - just passed the weekend) :-)

The shell procedure :

procedure shell (cmd IN CHAR)
as external
name "sh"
library shell_lib
language C
parameters (cmd string);

This is just the standard one provided by Oracle.

That from the .profile is right i gess because
!ftp_file.ksh is executed on the unix prompt
while
exec shell('...'); goes through the Listener.

I did send the system people the procedure to setup the listener - maybe they just have forgotten. Anyway, i did send them a mail about that.

Previous Thread | Next Thread
Test Your Knowledge in Computers #201
Difficulty: Easy
Perl is implemented as a core interpreter, written in C, together with a large collection of modules, written in Perl and C.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Why i can't execute the procedure in shell script?

i have the following code inside a shell script .prog in oracle server when i call the program DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id); will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i... (1 Reply)
Discussion started by: feilhk
1 Replies

2. Shell Programming and Scripting

Sub script calling procedure

Hi, I have a doubt regarding how sub scripts will be executed and interested to know internal workflow, For example - My main script is A,it calls a script B then B will call some C....and so on. Then B script run parallel to A or it will wait B to execute then A will continue. ... (1 Reply)
Discussion started by: nag_sathi
1 Replies

3. Shell Programming and Scripting

Executing procedure using script

Hi, I want to have a automted script to exceute 20 procedures one by one. Suppose below is one procedure once it get executed script will write "PL/SQL procedure successfully completed." to a log for ex- exec dbms_stats.gather_table_stats(); Now later procedure starts executing... (1 Reply)
Discussion started by: sv0081493
1 Replies

4. Programming

Help in Database Procedure returns value

We have a database procedure that returns a value by reference. This is how it is used in ABF: h_status = pr_get_passphrase (h_passphrase = BYREF(:gv_passphrase)); Is it possible to do the same in PHP? (1 Reply)
Discussion started by: AimyThomas
1 Replies

5. Shell Programming and Scripting

Need help with procedure and parameters in a script

Hi, I'm trying to create a procedure within my script and what I want is something like: myproc () { PARM1=value after -t PARM2=value after -y } myproc -t "PARM1 Value" -y "PARM2 Value" Of course that won't work since there's no such command as "value after -t". But this is... (4 Replies)
Discussion started by: adshocker
4 Replies

6. Shell Programming and Scripting

Executing Procedure from shell script..

Hello, I created a sql file to create a Procedure, and it was successfully created. I created a sql file to execute the procedure, and it did without any errors, but i dont see the data been updated. The Execute procedure.sql script is: BEGIN set serveroutput on size 1000000 execute... (5 Replies)
Discussion started by: msrahman
5 Replies

7. Shell Programming and Scripting

Execute stored procedure through script in sybase database and store the output in a .csv file

Hi, I have a sybase stored procedure which takes two input parameters (start_date and end_date) and when it get executed, it gives few records as an output. I want to write a unix script (ksh) which login to the sybase database, then execute this stored procedure (takes the input parameter as... (8 Replies)
Discussion started by: amit.mathur08
8 Replies

8. Shell Programming and Scripting

Shell Linux to connect to a database and execute store procedure

HI, i want to write a script (Linux) that: 1) connect to a database oracle 2) execute some store procedure. Can anybody help me, please? Thanks a lot Andrew (3 Replies)
Discussion started by: manichino74
3 Replies

9. Shell Programming and Scripting

getting proper o/p from a procedure in a script

foll. is my code snippet. #!/bin/ksh retVal=`sqlplus -s user/passwd@oracle_sid <<EOF SET SERVEROUTPUT ON SIZE 100000 DECLARE STATUS_VALUE CHAR; BEGIN SELECT temp1 INTO STATUS_VALUE FROM sai; DBMS_OUTPUT.PUT_LINE(STATUS_VALUE); END; / SET... (1 Reply)
Discussion started by: sainathdeg
1 Replies

10. Shell Programming and Scripting

calling procedure from script

Hi All, My script will call a storedprocedure #!/bin/bash # # Script for ........... . ../conf/setting.env # Environment file to set the DATABASE #TODAY=`date '+%Y%m%d_%H'` #echo TODAY = $TODAY sqlplus -s $DATABASE <<EOF spool $TRACKING_LOGDIR/CalcFreqPgsVues_H.log exec... (3 Replies)
Discussion started by: scorpio
3 Replies

Featured Tech Videos