Home » Developer & Programmer » Reports & Discoverer » Using FTP in Report (Oracle 9.2.0.8.0 (OS: Unix), Report Server 10.1.2.2.0 (OS: Windows), Report Builder 10.1.2.0.2)
icon5.gif  Using FTP in Report [message #336301] Fri, 25 July 2008 10:06
kwaam_rak
Messages: 14
Registered: July 2007
Junior Member
I have this report that generates a PDF file in the report server. I need this PDF file to be copied to another server using FTP. Here is the scenario:

Environment: Database Oracle 9.2.0.8.0 in Unix, Report Server 10.1.2.2.0 in Windows, Report Builder 10.1.2.0.2

A report is generated in the Report Server (Windows).
PDF file is the output, and this is saved in a folder in the Report Server.
This PDF file needs to be copied to another server using FTP.
Destination server OS is Unix.

I am trying to use the FTP package from oracle-base.com.
I created that FTP package in the database.
I created a procedure in the report that calls the FTP package.

PROCEDURE ftp_report IS
  l_conn UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('destsrvr', '21', 'myuserid', 'mypasswd');
  ftp.binary(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'g:\reports\output\',
          p_from_file => 'reportoutput.pdf',
          p_to_file   => '/home/myuserid/reports/reportoutput.pdf');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;


This procedure is called from the After Report Trigger.

function AfterReport return boolean is
BEGIN
  ftp_report;
  return (TRUE);
END;


When the report is generated, this error occurs:

Quote:

REP-1401: 'afterreport': Fatal PL/SQL error occurred.
ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "MYDBUSERID.FTP", line 197
ORA-06512: at "MYDBUSERID.FTP", line 472



ORA-06512: at "MYDBUSERID.FTP", line 197 is in here:

185 FUNCTION get_local_binary_data (p_dir   IN  VARCHAR2,
186                                 p_file  IN  VARCHAR2)
187   RETURN BLOB IS
188 -- --------------------------------------------------------------------------
189   l_bfile   BFILE;
190   l_data    BLOB;
191 BEGIN
192   DBMS_LOB.createtemporary (lob_loc => l_data,
193                             cache   => TRUE,
194                             dur     => DBMS_LOB.call);
195    
196   l_bfile := BFILENAME(p_dir, p_file);
197   DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
198   DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
199   DBMS_LOB.fileclose(l_bfile);
200
201   RETURN l_data;
202 END;


ORA-06512: at "MYDBUSERID.FTP", line 472 is in here:
466 PROCEDURE put (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
467                p_from_dir   IN             VARCHAR2,
468                p_from_file  IN             VARCHAR2,
469                p_to_file    IN             VARCHAR2) AS
470 -- --------------------------------------------------------------------------
471 BEGIN
472   IF g_binary THEN
473     put_remote_binary_data(p_conn => p_conn,
474                            p_file => p_to_file,
475                            p_data => get_local_binary_data(p_from_dir, p_from_file));
476   ELSE
477     put_remote_ascii_data(p_conn => p_conn,
478                           p_file => p_to_file,
479                           p_data => get_local_ascii_data(p_from_dir, p_from_file));
480   END IF;
481   get_reply(p_conn);
482 END;


Any ideas on how this could be solved?

Or if you have other ideas on how to transfer the file to the destination server, other than by the FTP package, please make the suggestion.

Thanks.
Previous Topic: Number Conversion Fails in Excel
Next Topic: date to year and quarter
Goto Forum:
  


Current Time: Sun Dec 11 02:43:13 CST 2016

Total time taken to generate the page: 0.09669 seconds