Hopefully you can help. This is what I'm trying to achieve:
Obtain a list of usernames out of an Oracle Database
Based on this list, link each username with an Oracle Internet Directory (OID) GUID
Using the username and GUID perform a database update for all users
Here are the commands:
This is SQL which is not the scope of this forum. Let's say I'll have a flat text file that has each username listed on a single line as the output
Now I have to run this list against OID to obtain the so so called GUID. For this I'll run: text $ ldapsearch -h $OIDHOST -p $OIDPORT -w $OIDPWD "(sn=<USERNAME>)" orclguid sn
The output looks like (used jdoe as a username example):
cn=doe\, john
orclguid=B2E3F3CADE9EF026E0430AFE192AF026
sn=jdoe
Now I need to pass this back to SQL
Ideally I have one file that takes the output above and prints it like
jdoe B2E3F3CADE9EF026E0430AFE192AF026
Any thoughts how to do this easily? Remember that the output of step 1 will be a list of app 100 users, and the final output of step 3 should basically have the GUID printed next to this (I assume in a new flat file)
awk can be piped to our script specified inline using ' ', or use -f to specify a awk script file.
If your ldap results in multiple lines, I would save that entire output to a file, then run it through awk.
the key here is awk since you have multiple lines that convey a single context.
while read -r U
do
ldapsearch -h $OIDHOST -p $OIDPORT -w $OIDPWD "(sn=$U)" orclguid sn
done < ldap.txt | while IFS="=" read NAME VAL
do
cn=doe\, john
orclguid=B2E3F3CADE9EF026E0430AFE192AF026
sn=jdoe
if [ -z "$NAME" ]
then
printf "%s\t%s\n" "${sn}" "${orclguid}"
cn=""
orclguid=""
sn=""
else
read $NAME <<EOF
$VAL
EOF
fi
done > flatfile
Thanks all for the help. The code below is doing what I am trying to accomplish. I figure to share this in case it helps someone, and I am open for suggestions for any further tuning
# Section 1: Obtain list of user names from EBS
echo "
set echo off;
set heading off;
set feedback off;
set pagesize 3000;
set linesize 200;
set termout off
set trimout on;
set trimspool on;
spool $CLONING_LOG/ldap.txt
select user_name from fnd_user where user_id<>3304
and (END_DATE > SYSDATE or END_DATE is null)
and user_name <> 'ASADMIN'
order by user_name;
spool off
exit" > $CLONING_LOG/ldap.sql
sqlplus apps/$APPS_PASSWORD @$CLONING_LOG/ldap.sql
grep -v "^$" $CLONING_LOG/ldap.txt > $CLONING_LOG/ldap2.txt
mv $CLONING_LOG/ldap2.txt $CLONING_LOG/ldap.txt
# Section 2 obtain the orclguid from OID for these users
while read -r U
do
ldapsearch -h $OIDHOST -p $OIDPORT -w $OIDPWD "(sn=$U)" orclguid sn | awk 'NR==2,NR==3' | sed 's/^.*\=//' | xargs
done < $CLONING_LOG/ldap.txt | while IFS="=" read NAME
do
# Section 3 updating the GUID in EBS from OID
echo "Now Updating" $NAME
sqlplus -s apps/$APPS_PASSWORD @$CLONING_SCRIPTS/ebsclone_target_guid.sql $NAME
done
It's cleaner to use a here-document for large multi-line strings.
I'm guessing xargs was to strip off quotes? That's cool. That can be moved outside the loop so it only needs to be run once instead of n times.
It's simple to skip blank lines in the loop instead of editing the temp file with a grep and a mv.
If you're editing the string with awk, don't bother with sed | cut | kitchen | sink -- solve the whole thing in one awk. It'd be nice if we could strip the quotes off too, but xargs really is better at that.
The sed was unnecessary with the code I originally gave you. It split the data on "=" into NAME and VAL. When you got rid of one of those variables you ended up having to do the splitting yourself again, with sed. But never mind, since we need awk, it can do that too.
# Section 1: Obtain list of user names from EBS
cat > $CLONING_LOG/ldap.sql <<EOF
set echo off;
set heading off;
set feedback off;
set pagesize 3000;
set linesize 200;
set termout off
set trimout on;
set trimspool on;
spool $CLONING_LOG/ldap.txt
select user_name from fnd_user where user_id<>3304
and (END_DATE > SYSDATE or END_DATE is null)
and user_name <> 'ASADMIN'
order by user_name;
spool off
exit
EOF
sqlplus apps/$APPS_PASSWORD @$CLONING_LOG/ldap.sql
# Section 2 obtain the orclguid from OID for these users
while read -r U
do
[ -z "$U" ] && continue # Skip blank lines
ldapsearch -h $OIDHOST -p $OIDPORT -w $OIDPWD "(sn=$U)" orclguid sn | awk -F"=" 'NR==2,NR==3 { print $2 }'
done < $CLONING_LOG/ldap.txt | xargs | while read NAME
do
# Section 3 updating the GUID in EBS from OID
echo "Now Updating" $NAME
sqlplus -s apps/$APPS_PASSWORD @$CLONING_SCRIPTS/ebsclone_target_guid.sql $NAME
done
awk can be moved completely outside the loop, so awk and xargs only need to be run once instead of n times. Since you're only using sn= (I think!) that simplifies it a lot too.
# Section 1: Obtain list of user names from EBS
cat > $CLONING_LOG/ldap.sql <<EOF
set echo off;
set heading off;
set feedback off;
set pagesize 3000;
set linesize 200;
set termout off
set trimout on;
set trimspool on;
spool $CLONING_LOG/ldap.txt
select user_name from fnd_user where user_id<>3304
and (END_DATE > SYSDATE or END_DATE is null)
and user_name <> 'ASADMIN'
order by user_name;
spool off
exit
EOF
sqlplus apps/$APPS_PASSWORD @$CLONING_LOG/ldap.sql
# Section 2 obtain the orclguid from OID for these users
while read -r U
do
[ -z "$U" ] && continue # Skip blank lines
ldapsearch -h $OIDHOST -p $OIDPORT -w $OIDPWD "(sn=$U)" orclguid sn
done < $CLONING_LOG/ldap.txt | awk -F"=" '/^sn=/ { print $2 }' | xargs |
while read NAME
do
# Section 3 updating the GUID in EBS from OID
echo "Now Updating $NAME"
sqlplus -s apps/$APPS_PASSWORD @$CLONING_SCRIPTS/ebsclone_target_guid.sql $NAME
done
