I have created one that creates a file based on a query passed as an IN parameter, and then SFTPs it to a remote server. The file created form the query contains both the column headers and rows, delimited by a separator selected as a parameter of the concurrent request. Since the query, separator, and destination directory are variables, this can be used as an universal package for interfacing via SFTP.
This works if you are authenticated by an authentication key for your server and user so you son't have to log to the remote server using username and password.
Here is an example on the authentication key setup:
Make sure that the authentication key includes all the UNIX users that you use on both the test and prod servers.
Before you go into the below code there some things you need to check:
- this solution will work on database 10.2.0.4 or higher. It will not work on DB 9. You may be able to make it work on lower versions of 10G, but that will require heavy DB patching. The most common issue in lower 10G versions is the UNIX job getting hanged.
- you must grant CREATE JOB and CREATE EXTERNAL JOB to the APPS user. I have seen the grant properly executed and the job would work fine for SYS user but still hang up on APPS user. The only fix it to upgrade to latest 10.2.0.4 with all proper patches.
Here is the code, optimized to have it run as a PL/SQL concurrent request from Oracle EBS.
procedure SFTP_FILE(
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
p_separator in varchar2 ,
p_dir in varchar2 ,
p_desdir in varchar2,
p_filename in varchar2,
p_query in varchar2, ,
parameter1 in varchar2 default null,
parameter2 in varchar2 default null,
parameter3 in varchar2 default null
)
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_desc_tab dbms_sql.desc_tab;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_query varchar2(2000);
l_program varchar2(50);
l_descTbl dbms_sql.desc_tab;
l_database varchar2(20);
l_conn UTL_TCP.connection;
l_filename varchar2(200);
v_to_file varchar2(2000);
status number;
begin
select name
into l_database
from v$database;
-- this part is to prevent accidental execution of the file uploaded from a test server
if l_database='PROD' then
-- the file can be comma, pipe or any other speparator delimited. This depends on the delimiter user selects
-- in the concurrent request parameter when submitting the job
l_separator :=p_separator;
-- this is the file name, it is taken from the IN parameters. Its first 9 characters are always replaced with
-- current date so the concurrent request can be scheduled to run daily
l_filename:=to_char(sysdate,'yyyymmdd')||substr(p_filename,9,length(p_filename));
-- this is the actual query that will be used to build the file that needs to be SFTP-ed
-- you could just write it here but I pass it as a parameter, and have a custom form where
-- i can adjust it in the application front end
l_query:=p_query;
-- if you want to manipulate the query for each run and add addtional AND conditions
-- there can be passed for the concurrent request parameters 1-3
-- those parameters can have default values
-- this is more convenient than changing the source query each time.
l_query:=l_query||' '||parameter1||' '||parameter2||' '||parameter3;
-- write to the concurrent request log to see the parameters and the passed query
Fnd_File.Put_Line(Fnd_File.LOG,'l_query = '||l_query);
Fnd_File.Put_Line(Fnd_File.LOG,'p_separator = '||p_separator);
Fnd_File.Put_Line(Fnd_File.LOG,'p_dir = '||p_dir);
Fnd_File.Put_Line(Fnd_File.LOG,'l_filename = '||l_filename);
-- I have taken core of this part for asktom.oracle com
-- it writes the headers and lines of the query into a delimited file
-- p_dir is directory where the file will be written
-- the directory is passed as the request parameter
-- make sure the request has the write privilege to that directory
-- if you do not have any specific directory in mind you can use one of the directories returned by
--select value from v$parameter where name='utl_file_dir'
l_output := utl_file.fopen( p_dir, l_filename, 'w' );
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
-- the separator is passed as variable l_separator variable to ensure flexibility of the solution
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_descTbl(i).col_name||l_separator );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_columnValue||l_separator );
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- write to the concurrent request output
fnd_file.put_line( FND_FILE.OUTPUT,'Your file '||l_filename ||' has been successfully created in staging directory at '||p_dir);
-- most likely this part will work only if you have database 10.2.0.4 or higher
-- it will not work on 9 or any database lower than 10G
--dbms_scheduler will start an OS (UNIX) job directly from the PL/SQL session
-- depending on yout database configuration the session can use different names to log in into UNIX
-- most likely it will be a user called oracle
--if the database has out-of-the-box configuration the user used to log in to UNIX is called 'nobody'
-- the nobody user does not have sufficient permissions to execute shell scripts
-- each instance (TEST, DEV, PROD, is most likely to be set up with a different UNIX user)
-- take that into consideration when testing
-- create sftp.sh file with the following syntax
--#!/bin/bash
--scp $1 $2
-- $1 and $2 are parameters that will be passed by DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
dbms_scheduler.create_job(
job_name=>'SFTP_UPLOAD',
job_type=>'executable',
number_of_arguments => 2,
job_action=>'/u01/oracle/PROD/xx/sftp.sh',--this is a sample directory where the sftp shell script resides
enabled=>FALSE
);
-- make sure that the file sftp.sh has proper permissions so
-- the oracle (or any other user that the database uses to log in into UNIX) can execute it
-- test it by opening an UNIX session and executing the file from the command prompt
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'SFTP_UPLOAD'
,argument_position => 1
,argument_value => p_dir||'/'||l_filename
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'SFTP_UPLOAD'
,argument_position => 2
,argument_value => p_desdir
);
dbms_scheduler.enable('SFTP_UPLOAD');
-- write to the output of the concurrent request
fnd_file.put_line( FND_FILE.OUTPUT,'scp '|| p_dir||'/'||l_filename ||' '||p_desdir);
fnd_file.put_line( FND_FILE.OUTPUT,'Connection to remote server has been successfully established');
-- check if the job has successfuly completed
select status ,nvl(substr(additional_info,1,32000), 'SUCCESS')
into r_status, r_additional_info
from USER_SCHEDULER_JOB_RUN_DETAILS where job_name='SFTP_UPLOAD'
and log_date = (select max(log_date) from USER_SCHEDULER_JOB_RUN_DETAILS where job_name='SFTP_UPLOAD');
if r_status <> 'SUCCEEDED' then
-- complete the concurrent request with warning if the job did not complete with success
l_completion:=fnd_concurrent.set_completion_status('WARNING','The SFTP process may have failed. Check the destination server to see if the file has been uploaded. '||r_additional_info);
else
fnd_file.put_line( FND_FILE.OUTPUT,'File upload success' );
End if;
-- this is the end of the IF from the beginning of the package. It checks for the test database
else
Fnd_File.Put_Line(Fnd_File.OUTPUT,'This is a test database, no action was taken');
end if;
EXCEPTION
when others then
Fnd_File.Put_Line(Fnd_File.OUTPUT,SQLERRM);
end ;
END;
can i get sftp.sh script
ReplyDelete