Home » Developer & Programmer » Reports & Discoverer » Using FTP in Report (Oracle (OS: Unix), Report Server (OS: Windows), Report Builder
icon5.gif  Using FTP in Report [message #336301] Fri, 25 July 2008 10:06
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 in Unix, Report Server in Windows, Report Builder

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;
  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');

This procedure is called from the After Report Trigger.

function AfterReport return boolean is
  return (TRUE);

When the report is generated, this error occurs:


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)
188 -- --------------------------------------------------------------------------
189   l_bfile   BFILE;
190   l_data    BLOB;
192   DBMS_LOB.createtemporary (lob_loc => l_data,
193                             cache   => TRUE,
194                             dur     => DBMS_LOB.call);
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);
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 -- --------------------------------------------------------------------------
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.

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

Current Time: Thu Jun 29 00:49:10 CDT 2017

Total time taken to generate the page: 0.12890 seconds