chuckd has asked for the wisdom of the Perl Monks concerning the following question:

HI
I'm passing $text, which is text from a file with funny characters into a filehandle that has been opened for sqlldr like so:
-------------------------------
open(FILE,"c:\\file.txt") or die "Cannot open text file\n";
my $text = do {local $/; <FILE>};
close(FILE) or die "Cannot close file\n";

SQLLDR FILEHANDLE
-----------------------
open (my $sqlldr, "|sqlldr usr/pass \@db");
print $sqlldr "$text";
close($sqlldr);
The text in my file isn't in ascii format, it looks like this below:
                           !"   #

--------------------------------
it looks like a bunch of funny characters that contain mostly non ascii chars, I have no problem loading this data from a sqlldr dat/txt file, but when I try to pass this data as $text to $sqlldr file handle it will not load correctly. It seems as though STDOUT doesn't like the special characters in the scaler variable $text that gets passed to the file handle. Does anyone have any idea why it doesn't load/pass these funny characters correctly? How ca I can change the way STDOUT encodes reads characters? Someone I know mentioned converting everything to base 64 then try passing it.

Replies are listed 'Best First'.
Re: stdout and special characters
by almut (Canon) on Dec 16, 2008 at 01:46 UTC

    Setting binmode on $sqlldr (opened to the pipe, i.e. what you're calling stdout) and FILE should take care of the Perl side (to prevent linefeed translations otherwise done on Windows), but I'm not sure what sqlldr would do with the binary stream...  In other words, it seems more like a sqlldr issue than a Perl one.

    BTW, we're talking about Oracle's sqlldr, aren't we? Also, are you using a control file? If so, what does it look like? — Some more details might help.

[reply]
[d/l]
[select]
      Hi,
      Yes Oracle's sqlldr..
      I tried setting binmode(FILE) to open data in bin mode but it didn't seem to change the stream data.
      Here's the thing..what boggles me is that I can load the data correctly from a dat file into sqlldr, but not through stdout when I stream it like in the example. That's why I think it's a stdout issue and not a sqlldr issue.
      My .ctl file looks like this
      ---------------------
      LOAD DATA
      INFILE "-" "str '<endrec>\r\n'"
      APPEND

      INTO TABLE DOC_TMP_TEST
      FIELDS TERMINATED BY 'Ø'
      TRAILING NULLCOLS
      (

      document_id,
      text VARCHARC(9,100000000)
      )
[reply]
        I tried setting binmode(FILE)...

        Have you also set "binmode $sqlldr;"? (Both in- and output must operate in binary mode.)

        As you have a .ctl file, how are you telling sqlldr about it? There's no "control=..." argument in your pipe command in open(), and the reference docs say "default: none".

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom