Perl script to connect to an Oracle database/tables and create an output file in a csv format

I am new to Perl (5.10.0) and creating a Perl script, connect to Oracle database (11.2.0.4) and output a csv file after reading columns from a table.

The below code selects data from an Oracle table inside a stored procedure/package.

Select all_cols  from
emp 
where emp_name = 'SMITH' and partition_key  = '20170101_20170331'

Open in new window



Here's my first time perl script that I put together. Not sure of what changes I need to make to the script to get my desired output in a csv format.

use strict;
use warnings;
use 5.27;

use DBI;
use Text::CSV;

my $dbi = DBI->connect( $dsn, $user, $pass );
Date_Init('TZ=US/Central','Internal=1');

my $sth = $dbi->prepare("SELECT columns from EMP table" );
$sth->execute;
WHILE (my $row = $sth->fetchrow_arrayref )
 {
    dump_table($row->[0] );
 }

sub dump_table 
  {
    my ( $table_name ) = 'Schema_name.table_name';

    print "Extracting $table_name...\n";

    my @column_names;
    my $column_name_sth = $dbi->prepare(
        "SELECT column_name FROM USER_TAB_COLUMNS"
        . "WHERE table_name = '$table_name' AND PARTITION_KEY = '20170101_20170331'"
    );
    $ column_name_sth->execute;
    while ( my $row = $column_name_sth-> fetchrow_arrayref ) {
        push @column_names, $row->[0];
    }

    my $csv = Text::CSV->new ( { binary => 1 } );
    $csv-> eol ("\n");

    Open ( my $fh, 
        ">: encoding(utf8)", 
        "$table_name.csv" 
    ) or die "$table_name.csv: $!”;

    $csv->print ($fh, \@column_names);

    my $data_sth = $dbi->prepare( "select * from $table_name" );
    $data_sth->execute;
    while ( my $row = $data_sth->fetchrow_arrayref ) {
        $csv->print( $fh, $row );
    }
}

Open in new window



Desired Output in csv format
EMPID	EMPNAME	PARTTITION_KEY	HIREDATE
1	STEVE	20170101_20170331	20110401
2	SMITH	20170401_20170630	20120601
3	CHRIS	20170701_20170930	20130901
4	JOHN	20171001_20171231	20140825

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
Are you getting any errors?

What does the current output look like?

Do you want CSV or do you want tab-delimited (at least I'm assuming that's what your "desired output" table is) or something else?
0
Mark GeerlingsDatabase AdministratorCommented:
Do you have to use Perl for this task?  It may be simpler to just use SQL*Plus with a *.SQL script.  You could launch that with a one-line Bash shell script if you need to automate it.
0
steve2312Author Commented:
Yes. Perl is a requirement for the task.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
I find it very easy to get output files from Oracle in either CSV or fixed-length formats by using SQL*Plus.  But, I don't know or use Perl, so I can't help you with that.
0
steve2312Author Commented:
Appreciate your comment Mark!  Unfortunately, we use a job scheduler tool which works with Perl, hence the requirement.
0
Mark GeerlingsDatabase AdministratorCommented:
Your job scheduler tool cannot call a Bash shell script?
0
steve2312Author Commented:
Yes, would be a possibility, if I could prove that it cannot be done using Perl .
0
steve2312Author Commented:
How could it be done using bash shell script?
0
Mark GeerlingsDatabase AdministratorCommented:
I'm not saying that this job cannot be done with Perl.  

But, for me that is a tool that I've never needed to learn or work with.  I've been working in Oracle databases with SQL*Plus for 25 years.  So, for me, that is the combination I use unless that isn't capable of getting the job done.  This combination allows me to test Oracle queries directly and interactively, then put the query/ies into a *.SQL file.  And it is easy to launch SQL*Plus with a one-line Bash command that can be written into a one-line shell script.  One possible disadvantage of this approach is passing the Oracle username and password on the command line, or having it hard-coded in plain text in a shell script.  There are ways around that, if that is a problem in your system.

Assuming that you are logged into the database server as the Oracle software owner, and are in the directory that contains your *.SQL file, this command will launch SQL*Plus and run the file that contains the SQL commands and query/ies you have already tested:

sqlplus [oracle_user]/[password] @my_file.sql

(You can optionally include the full path to the sqlplus executable and the full path to your *.SQL file.)

Your *.sql file (named: "my_file.sql" in my example) should look something like this:
set pagesize 999;
set linesize 600;
set trimspool on;
set verify off;
set head on;
set termout off;
spool my_data.txt
Select all_cols  from
emp
where emp_name = 'SMITH' and partition_key  = '20170101_20170331';
spool off;
exit;

(You can include a full path in addition to the output file name on the first "spool..." line, if you want this file to be directed somewhere other than your current directory.
And the "set termout off;" line is not required, but it will save a bit of performance if the number of records involved is large.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
The solution is a workable one, though not in Perl.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.