Perl is all well and good, and I love it to bits and all that. But reading your database into a Perl hash when you have an expensive RDBMS sitting there, with all the data loaded, waiting to do such things for you, may not be the best approach. Have a google around Oracle exists or insert and perhaps Oracle update or insert and make some use of the other Larry's code too.

Cheers,
R.

Many of us would like to help you, but please read this: How do I post a question effectively?

Hello Perl experts, I am new to perl, I am writing a script which needs to check all the users from LDAP Active directory, and check the users in our database, if there is any user from LDAP is not present in Database then add that user in DB

here is my code

#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use Net::LDAP;
#use List::Compare;

my ($uid) = $ARGV[0];

if ($#ARGV > 0 ) {
        print " \n";
        print "\nUsage: SCRIPTNAME.pl UID (BJB UID- use the Uppercase 
+'U' letter)\n";
exit;
}
#
                                ##### DB Related #################
my $dbdriver = "Oracle";
my $dbname = "xxx";
my $dbhost="xxx";
my $dbport="1521";
my $dbdsn = "xxx";
my $dbuserid = 'xxx';
my $dbpassword = 'xxx';

my $dbh = DBI->connect($dbdsn, $dbuserid, $dbpassword ) or die $DBI::e
+rrstr;

my $sth = $dbh->prepare("SELECT users.id,users.displayname,users.enabl
+ed,users.name,regions.name AS defaultRegion FROM regions JOIN users O
+N users.regionid = regions.id WHERE users.regionid = regions.id") or 
+die "Can't prepare statement: $DBI::errstr";
$sth->execute();

## Display any printed messages from the sql query result
if (defined($dbh->err)) {
        if ($dbh->err eq 0) {
                print "Warning message : ", $dbh->errstr, "\n";
        } elsif ($dbh->err eq '') {
                print "Informational message : ", $dbh->errstr, "\n";
        }
}
print " \n";
print " \n";
print "##############  Database Query Results ##################\n";
## Print rows affected
printf "Rows affected: %s\n",$sth->rows,"\n";
## Print the column name.
#print "$sth->{NAME}->[0]\n";
my $nf = $sth->{NUM_OF_FIELDS};
print "This statement returns $nf fields\n";
for ( my $i = 0; $i < $nf; $i++ ) {
        print "$sth->{NAME}->[$i]\t";
}
print " \n";

## Fetch and display the result set value.
while ( my @row = $sth->fetchrow_array ) {
        foreach (@row) {$_ = '' unless defined}; # change all NULLs to
+ empty strings
        print "@row\t\n";
}

## Hashmap for the DB 
my $dbUsers = {};
$sth->execute();
while( my @row = $sth->fetchrow_array() ) {
  $dbUsers->{$row[0]} = {
    id => $row[0],
    displayName => $row[1],
    enabled => $row[2],
    name => $row[3],
    defaultRegionId => $row[4]
  };
}

foreach my $uid (keys %$dbUsers) {
  print $dbUsers->{$uid}->{'displayName'} . "\n";
  print $dbUsers->{$uid}->{'name'} . "\n";
  print $dbUsers->{$uid}->{'id'} . "\n";
  print $dbUsers->{$uid}->{'enabled'} . "\n";
  print $dbUsers->{$uid}->{'defaultRegionId'} . "\n";
}

$sth->finish();
$dbh->disconnect;


                                ######## LDAP Related ##############

my $server = "xxx";
my $ldap = Net::LDAP->new( $server ) or die $@;

my $ldapuser_dn = "xxx";
my $ldappass = "xxx";
$ldap->bind( $ldapuser_dn,  password => $ldappass );

my $result = $ldap->search(
# Searching for all the users from AD group
        base   => 'DC=xxx,DC=xxx',
        filter => "(&(CN=xxx))",
        attrs  => ['member']
);
die $result->error if $result->code;

my $ldapUsers = {};

foreach my $entry ($result->entries) {
        print $entry;
        print "---";
        my @members = $entry->get_value('member');
        printf "COUNT: %s\n", scalar @members;

        foreach my $member (@members) {
                my $user = $ldap->search(
                  base   => $member,
                  scope  => 'base',
                  filter => 'cn=*'
                );
                die $user->error if $user->code;
                foreach my $entry ($user->entries) {
                  my $id = $entry->get_value('sAMAccountName');
                  my $id2 = substr $id, 1;
                  $ldapUsers->{$id2} = {
                        id => $id2,
                        name => $entry->get_value('sAMAccountName'),
                        displayName => $entry->get_value('displayName'
+),
                        defaulteRegion => $entry->get_value('extension
+Attribute1')
                  };
                }
        }
}

foreach my $uid (keys %$ldapUsers) {
        print $ldapUsers->{$uid}->{'displayName'} . "\n";
        print $ldapUsers->{$uid}->{'name'} . "\n";
        print $ldapUsers->{$uid}->{'id'} . "\n";
        print $ldapUsers->{$uid}->{'defaulteRegion'} . "\n";
}

print "====================END===========================\n";
#$ldap->unbind;

##I am looking for the code here to compare the "name" from Ldap and D
+B, if the ldap user is not present in the DB, then add him to DB.. 

## DB query looks like below
insert into users (id, name, displayname, regionid) values ('123456', 
+'U123457', 'Doe, John', select id from regions where name='$user->{'d
+efaultRegion'}')
[download]

That is a good start, now what is your question? What exactly is your problem? Are you getting any error messages? I see that you are using strict and warnings, that is good. Help us zero in on what you think the problem is.

You must always remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.

Do the comparison with exists. I would run the LDAP routine first then the DB routines and use a hash lookup for the region id rather than a sub-select.

use DBI;
use Data::Dump 'pp';
my $dbh = DBI->connect($dbdsn, $dbuserid, $dbpassword, 
 {RaiseError => 1,PrintError => 1})           
 or die "Connection Error: $DBI::errstr\n";

# users 
my $sql_u = 'SELECT id from users';
my $dbUsers = $dbh->selectall_hashref($sql_u,'id');
print "Users\n";
pp $dbUsers;

# regions 
my $sql_r = 'SELECT id,name FROM regions';
my $regions = $dbh->selectall_hashref($sql_r,'name');
print "Regions\n";
pp $regions;

# prepare insert sql
my $sql_i = "INSERT INTO users (id, name, displayname, regionid) 
             VALUES (?,?,?,?)";
my $sth_i = $dbh->prepare($sql_i);
for my $uid (keys %$ldapUsers) {
  # check if user not exists in db
  if (! exists $dbUsers->{$uid}){
    my $ldap = $ldapUsers->{$uid};
    my @f = ($uid);
    $f[1] = $ldap->{'name'};
    $f[2] = $ldap->{'displayName'};
    $f[3] = $regions->{$ldap->{'defaulteRegion'}}{'id'};
    # uncomment the execute after testing
    #$sth_i->execute(@f); 
    print "Insert record : ".(join " | ",@f,"\n");
  }
}
[download]

poj