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?
 
Mark GeerlingsConnect With a Mentor Database 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
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
steve2312Author Commented:
Yes. Perl is a requirement for the task.
0
 
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
 
steve2312Author Commented:
The solution is a workable one, though not in Perl.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.