This forum is currently locked. You can't register or post questions at this time. (read more)

issue with ftp from PL/SQL

General suggestions and comments about the forums.

Moderator: Tim...

rbadillarx
Member
Posts: 2
Joined: Wed Jun 18, 2014 7:30 pm

issue with ftp from PL/SQL

Postby rbadillarx » Wed Jun 18, 2014 7:50 pm

Hi all:
Since I don't know in which seccion post about you articles/scripts I put on this (sorry if I mistake).
Our BD team is having issues with the PL/SQL code of http://www.oracle-base.com/articles/mis ... plsql.php; they are sending files some time ago, so the issue is new.

They make a query on the DB Server; that creates a file; this file is send over FTP to a remote site; the issue is that transmitted file has fewer lines than the original one.
We came down to the DB/PL/SQL code because when we transmit this file using ftp clients (both windows; unix) from either side (BD server, remote site) the size of the file is constant and the checksums (sha1) also. But when the file is transmitted with the PL/SQL code has fewer lines out. The issue (absent content) vary from 40 lines to 663 lines.
The files is ASCII varing from few KB to 48MB; the content is a result from a query and basically it has:
phone number, bytes, cellid, timestamp.

They have used the binary transfer and ascii and the issue persists. We also have captured network packets to see if there is something at that level but nothing is wrong (obviously the issue appears but no errors or packet miss).

Does anybody have an idea (buffers, encoding).
Thanks in advance

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: issue with ftp from PL/SQL

Postby Tim... » Wed Jun 18, 2014 8:50 pm

Hi.

Can't say I've seen any issues when using binary transfers.

I've seen a number of issues with ascii transfers, usually because of implicit character conversion with different character sets. Binary has been very consistent for me.

I think you will need to identify a pattern, so I can replicate it and attempt to fix the issue...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

rbadillarx
Member
Posts: 2
Joined: Wed Jun 18, 2014 7:30 pm

Re: issue with ftp from PL/SQL

Postby rbadillarx » Fri Jun 20, 2014 6:55 pm

Hi Tim:
Thanks a lot by your reply.
Since I am sysadmin, just the DBA people have access to the code and the data to make testing.
They worked around the issue using the variant of your code where there is a call to a shell script. Same machines and data, different results with the shell script.
As I described before we have helped in any possible way (without mess with the code, checked: format, encoding, lenght); the file is very simple 4 columns; comma delimited.
The DBA people thinks that varchar (2) variable is the head of the problem but I can't go further.
As academic effort I will try to setup a like environment to drill down and catchup the problem... hope have some input to share with you before year ends...
Thanks a lot anyway and keep working!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: issue with ftp from PL/SQL

Postby Tim... » Sat Jun 21, 2014 6:33 am

No problem. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Suggestions and Comments”

Who is online

Users browsing this forum: No registered users and 2 guests

cron