title: Perl Script to List Accounts in EBS Database link: https://onemoretech.wordpress.com/2007/07/02/perl-script-to-list-accounts-in-ebs-database/ author: lembobro description: post_id: 676 created: 2007/07/02 20:48:00 created_gmt: 2007/07/02 20:48:00 comment_status: open post_name: perl-script-to-list-accounts-in-ebs-database status: publish post_type: post

Perl Script to List Accounts in EBS Database

Just when you thought it was safe to go in the water!

Here’s a quick DBD::Oracle script I cooked up to do what I did using sqlplus in the previous article. I use an external config file to store the Oracle SID (i.e. service name), user (in this case ‘APPS’) and password. Notice how straightforward it is to plug a standard Oracle query into the code and get results back. The only shortcoming with this script is that the sqlplus page formatting doesn’t come through, so if you wanted to do a report you’d need to insert your own.

`

#!/usr/bin/perl
use strict;use DBI;
our($orclsid,$orclusr,$orclpw);
my $HOME = $ENV{’HOME’};
$ENV{’TNS_ADMIN’} = “/etc/oracle”;
require “$HOME/etc/orclapp.conf”;
#
my $dbh = DBI->connect(”dbi:Oracle:$orclsid”,
           “$orclusr”,
           “$orclpw”,
#
          ) or die “Database not connected: $DBI::errstr”;
#
my $sql = qq[ SELECT USER_NAME FROM FND_USER ];
#
my $sth = $dbh->prepare($sql);
#
$sth->execute();
#
while (my($user_id)  = $sth->fetchrow()) {
#
    print “$user_idn”;
#
}
#
$sth->finish;
$dbh->disconnect;
#
__END__;

`

My next task will be to script the linking of OID orclguids to EBS user accounts in FND_USER. Should be fun.

Some resources I used to do this were An Introduction to DBD::Oracle on the Pythian site. I also used my own Oracle Instantclient and DBD::Oracle to properly make and install the module.

Note: Important addition! The line “$ENV{’TNS_ADMIN’} = “/etc/oracle” sets the $TNS_ADMIN environment variable so the script can use an external tnsnames.ora file — which is handy if you’re working in a multiple database environment and want to be able to test your connect strings using sqlplus (or listener status with tnsping).

Copyright 2004-2019 Phil Lembo