Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Getting from Oracle to become a text file

by Zo (Scribe)
on Oct 04, 2001 at 20:59 UTC ( #116761=perlquestion: print w/replies, xml ) Need Help??

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

Hello everyone.
I have a problem that might be very simple, I'm probably forgetting/overlooking the obvious... but here I am. I want to connect to an Oracle 8i database, extract a table's data and save the information as a text file on my computer. Simple it seems, but I think I'm missing something.
In the code, I do make a connection, I do get the proper query and info from viewing it scroll up on my prompt from the 'print' statements that I have, but it doesn't want to write it to the file that I want to create. My desktop is running the latest version of ActivePerl I just downloaded recently and of the DBI-1.2 module. My desktop platform is Windows 2000 professional. (if you need any of this information).

#!perl -w ######################################################### # This perl script will go to the oracle database, run a query, extrac +t data and # create a text (.txt) file with data information for a batch # process to use. # # ######################################################### use DBI; # output file $newfile = ">./batch_test.txt"; # open the new .txt file open(BATCH, "$newfile") || die "Can't open $newfile : the batch_test f +ile. $!"; # initialize variables # variable with query to get data from admin.batch_lines table $asql = "SELECT batch_line FROM admin.batch_lines"; # connect to oracle my $dbh = DBI->connect("dbi:Oracle:D001", "develop", "developerPW"); my $statement = $dbh->prepare($asql); $statement ->execute(); while ($newfile= $statement->fetchrow_array) { # remove the all leading 'D:' in the file $newfile=~s/D://g; # shows the correct data print $newfile; print "\n"; } # close oracle connection $statement->finish(); $dbh->disconnect(); # close new .txt file close (BATCH);

What am I missing, doing wrong? Thanks in advance for any/all help!
Brother Zo.

Replies are listed 'Best First'.
Re: Getting from Oracle to become a text file
by tommyw (Hermit) on Oct 04, 2001 at 21:09 UTC

    You're simply not printing anything to your file!

    Try adding:

    print BATCH $newfile, "\n";
    within the body of your while loop.

    Just because you've reused the variable name ($newfile) doesn't connect the data with the file you've opened earlier. That's what BATCH is for.

Re: Getting from Oracle to become a text file
by runrig (Abbot) on Oct 04, 2001 at 21:11 UTC
    print $newfile;
    It helps if you print to the open filehandle:
    print BATCH $newfile;
    BTW, you are not checking the status of your connect or any other DBI statement. The simple thing to do would be to take a look at RaiseError in the DBI docs and use it in the connect statement.
Re: Getting from Oracle to become a text file
by Rhose (Priest) on Oct 04, 2001 at 21:14 UTC
    I would change:

    # output file

    $newfile = ">./batch_test.txt";

    # open the new .txt file

    open(BATCH, "$newfile") || die "Can't open $newfile : the batch_test file. $!";

    to

    # output file

    $newfile = "./batch_test.txt";

    # open the new .txt file

    open(BATCH, ">$newfile") || die "Can't open $newfile : the batch_test file. $!";

    and

    print $newfile;

    print "\n";

    to

    print BATCH $_,"\n";

Non-Perl way...
by Rhose (Priest) on Oct 04, 2001 at 21:19 UTC
    You can also do this through Oracle's SQL*Plus -- connect to D001 as develop/developerPW and run the following SQL script:

    SET FEEDBACK OFF SET PAGESIZE 0 SELECT batch_line FROM admin.batch_lines SPOOL batch_test.txt / SPOOL OFF
Re: Getting from Oracle to become a text file
by Zo (Scribe) on Oct 04, 2001 at 21:19 UTC
    Thank you!.. boy I feel silly... I knew I was overlooking something very simple.
    I haven't used Perl since atleast last February. I convinced my new boss to let me do a project in Perl because it would be easier than what he was describing, so I'm refamiliarizing myself as I go.
    Thank you all again.
    Brother Zo.
Re: Getting from Oracle to become a text file
by jbert (Priest) on Oct 05, 2001 at 19:07 UTC
    And to indulge my "but is that what you should be doing?" personality flaw^H^H^H^Htrait - is it worth mentioning that doing Oracle->text file rings alarm bells?

    You want the data in a text file so it can be processed by a batch job. Is the batch job already written? If not, why not code it in perl and go directly to the Oracle DB? Lose the text file entirely.

    If the batch job is already written, then is it complex? Is it possible to re-implement in perl, instead of implementing the oracle->text thing? I kind of dislike tempfiles etc since they tend to cause intermittent problems/require admin overhead. (Oh...the /blah disk is full....hmm...what are all these oracle.YYMMDDHHMMSS.txt files?)

    It's a practical extraction and _reporting_ language...:-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://116761]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (5)
As of 2019-10-21 22:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?