Hi all,
I am trying to execute a shell script from within my PL/SQL block using DBMS_PIPE (Not sure if this is the correct way or not)
Pl Sql Script
==========
set serveroutput on size 100000
declare
cursor c1 is select * from mag_images
where image_name like 'comp2%';
v_path varchar2(100) := '/home/maguzzi/DOCUMENTS_DIR/';
v_result number := 0;
v_in_filename varchar2(100) := v_path||'test_file.jpg';
v_out_filename varchar2(100) := v_path||'test_file_out.jpg';
v_script varchar2(100) := 'tst_convert.sh';
v_command varchar2(200) := 'sh '||v_path||v_script||' '||v_in_filename||' '||v_out_filename;
begin
for a1 in c1 loop
dbms_output.put_line('v_command : '||v_command);
write_to_file('test_file.jpg','DOCUMENTS_DIR',a1.content);
dbms_output.put_line('File Write Result : '||v_result);
v_result := exec_host_command(v_command);
dbms_output.put_line('File Resize result :'||v_result);
end loop;
end;
DBMS_OUTPUT Result
====================
v_command : /home/maguzzi/DOCUMENTS_DIR/tst_convert.sh test_file.jpg test_file_out.jpg
File Write Result : 0
File Resize result : 0
PL/SQL procedure successfully completed.
Function exec_host_command
=======================
CREATE OR REPLACE FUNCTION exec_host_command( lc_cmd IN VARCHAR2 )
RETURN INTEGER IS
ln_status NUMBER;
lc_errormsg VARCHAR2(80);
lc_pipe_name VARCHAR2(30);
BEGIN
lc_pipe_name := 'Maguzzi_PIPE';
dbms_pipe.pack_message( lc_cmd );
ln_status := dbms_pipe.send_message(lc_pipe_name);
RETURN ln_status;
END;
/
The BLOB Image is written to the correct location on the server "test_file.jpg", and although the exec_host_command function returns 0 , the shell script (tst_convert.sh) is not being executed (i.e. No output file is created - outfile.txt and the image is not copied/converted to a new file name "'test_file_out.jpg" )
Shell Script
========
tst_convert.sh
echo 'Over Here' > outfile.txt
echo $1 >> outfile.txt
echo $2 >> outfile.txt
convert $1 -resize 200 $2
Could this be permissions problems on the files?
test_file1.jpg has permission -rw-rw-r--
test_convert.sh has permission rwxrwxrwx
when I run the script manually and pass in the 2 parameters ($1 = infilename and $2 = outfilename), it works..
I will appreciate if anyone can point out my mistake or suggest an alternative method that works.
Thanks in advance