Hi!
Have anyone done a integration between Active Director and IFS10, where the user pictures are imported?
We have user pictures in AD and would like to have the same in IFS10.
/Karl
Page 1 / 1
I’m not aware of such a solution throughout my years but I guess it should be possible to do any of the following:
- Write a c# application that does this or
- Use PowerShell and import pictures into IFS using eg data migration
- Use some RPA tool out there
- Use Azure logic apps or BizTalk
- Write a PLSQL script that reads from AD and imports / updates it
Hi Karl.
Did you find any solution to use pictures from AD?
br
Tobias
Here's a complete solution. I’ve only tested this in IFS Enterprise Explorer in Apps 9.
Prerequisite: Oracle Directory
CREATE DIRECTORY ad_picture AS '\\some_server_name\some_directory_name';
Step 1: PowerShell
Using the Remote Server Administration Tools, export each user's picture as an individual file.
(To ensure removals cascade, you’ll want to empty the target directory first.)
$output_directory = '\\some_server_name\some_directory_name'
$ad_group_name = 'some_ad_group_containing_your_ifs_users'
$user_pictures = Get-ADGroupMember -Identity $ad_group_name | Get-ADUser -Property thumbnailphoto
$user_pictures = $user_pictures | Where-Object thumbnailphoto
$user_pictures | ? {Set-Content "$($output_directory)\$($_.samaccountname.ToLower()).png" -Value $_.thumbnailphoto -Encoding Byte}
Step 2: PL/SQL
This assumes your Person IDs are the same as your User IDs. Missing pictures are nullified to ensure AD removals cascade.
SET ECHO ON
SET TERMOUT ON
SET LINESIZE 32767
SET PAGESIZE 32767
SET SQLBLANKLINES ON
SET TAB OFF
SET TRIMSPOOL ON
SET VERIFY ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK ON
DECLARE
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2);
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2)
IS
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');
file_name_ := LOWER(user_id_) || '.png';
picture_bfile_ := BFILENAME('AD_PICTURE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);
SELECT picture_id
INTO blob_id_
FROM person_info
WHERE person_id = UPPER(user_id_);
binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);
SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info
WHERE person_id = UPPER(user_id_);
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END set_ad_picture;
BEGIN
FOR rec_ IN (
SELECT fu.identity
FROM fnd_user fu
JOIN person_info pi
ON pi.person_id = fu.identity
AND pi.user_id = fu.identity
ORDER BY 1
) LOOP
set_ad_picture(rec_.identity);
END LOOP;
END;
/
COMMIT;
EXIT;
(I recommend this gets moved to the Technology forum.)
This updated version works in both Apps 9 and Apps 10.
(Aurena throws an error if the user picture is present but empty. This now removes the picture if it’s empty.)
DECLARE
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2);
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2)
IS
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');
file_name_ := LOWER(user_id_) || '.png';
picture_bfile_ := BFILENAME('AD_PICTURE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);
SELECT picture_id
INTO blob_id_
FROM person_info
WHERE person_id = UPPER(user_id_);
IF picture_blob_ IS NOT NULL THEN
binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);
ELSIF blob_id_ IS NOT NULL THEN
binary_object_api.do_delete(blob_id_);
blob_id_ := NULL;
END IF;
SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info
WHERE person_id = UPPER(user_id_);
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END set_ad_picture;
BEGIN
FOR rec_ IN (
SELECT fu.identity
FROM fnd_user fu
JOIN person_info pi
ON pi.person_id = fu.identity
AND pi.user_id = fu.identity
ORDER BY 1
) LOOP
set_ad_picture(rec_.identity);
END LOOP;
END;
/
COMMIT;
You are really super hero of IFS.
I try to migrate image for single User_id.
Procedure got run successfully.
But image not inserted for that user. And also tried to bulk insert image .
Kindly look into this pls.
DECLARE
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');
file_name_ := LOWER('YB0C97972') || '.jpg';
picture_bfile_ := BFILENAME('CUST_IMAGE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);
SELECT picture_id
INTO blob_id_
FROM person_info_all
WHERE person_id = UPPER('YB0C97972');
binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);
SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info_all
WHERE person_id = UPPER('YB0C97972');
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END ;
This updated version works in both Apps 9 and Apps 10.
(Aurena throws an error if the user picture is present but empty. This now removes the picture if it’s empty.)
DECLARE
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2);
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2)
IS
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');
file_name_ := LOWER(user_id_) || '.png';
picture_bfile_ := BFILENAME('AD_PICTURE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);
SELECT picture_id
INTO blob_id_
FROM person_info
WHERE person_id = UPPER(user_id_);
IF picture_blob_ IS NOT NULL THEN
binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);
ELSIF blob_id_ IS NOT NULL THEN
binary_object_api.do_delete(blob_id_);
blob_id_ := NULL;
END IF;
SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info
WHERE person_id = UPPER(user_id_);
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END set_ad_picture;
BEGIN
FOR rec_ IN (
SELECT fu.identity
FROM fnd_user fu
JOIN person_info pi
ON pi.person_id = fu.identity
AND pi.user_id = fu.identity
ORDER BY 1
) LOOP
set_ad_picture(rec_.identity);
END LOOP;
END;
/
COMMIT;
Someone PM’ed me with a followup question about this. The error they were getting was ORA-29283.
The question had a a file share with a format that looks like \\SERVER\SHARE. (See footnote 
.) I'm guessing that your files are on a Windows server. Consequently, your Oracle database will also need to be running on Windows for this to be doable without a lot of hacking. (See footnote
.)
I highly recommend Don Burleson’s website for all things Oracle.
http://www.dba-oracle.com/t_ora_29283_invalid_file_operation.htm
In short, he says this is an OS issue. Unfortunately, because so much of his experience has been with UNIX and UNIX-like operating systems, you won’t find as much help there for Windows-specific issues. (There is some, just not as much.)
The process oracle.exe for your Oracle Database service is going to be running as some user. If you haven't messed around with it during installation, it's probably running as the SYSTEM user. This is the user that will need access to the file share.
You might well be scratching your head because SYSTEM is a local user, and you’d be correct. When SYSTEM talks to network resources, those network resources are going to see it as a Computer principal. If your database is running on a server called MYDBSERVER, then that principal would be MYDBSERVER$ on your network.
I’m going to assume both your file server and your database server are on the same Active Directory domain.
You’ll need to grant both the share and the folder so Oracle can see them.
Folder properties →Advanced Permissions → Add →”Select a principal”
In the Windows security dialog, when you go to type in your Principal name, Computer principals aren’t enabled by default. From here, you’ll need to click the button on the top right called “Object Types” and enable Computer principals to be selected. Enter your server name like MYDBSERVER$ (with the dollar sign), save it, and see if Oracle can read it.
If that still doesn’t work--and if you confirmed your database is running as SYSTEM--you may want to double check that the local SYSTEM user can see the folder. (You may have a permissions issue or a network configuration issue.) The PSTOOLS toolkit includes a utility PSEXEC, which includes a -S flag to run a process as the local SYSTEM user.
https://learn.microsoft.com/en-us/sysinternals/downloads/psexec
So, from the database server, you’ll do something like this to impersonate the SYSTEM user:
psexec -s cmd
pushd \\SERVER\SHARE
:: write test
echo “Hello world!” > some_new_file.txt
:: read test
type some_old_file.txt
popd
exit
Footnote 1: On Linux, I haven’t tried this yet, but I imagine it’s doable if you mount the share with an SMB driver, grant it to your oracle user, and point your directory object over to the mount point. I recommend avoiding FUSE; you probably want this mount in /etc/fstab so it’s visible to systemd-managed services.
Footnote 2: I recommend you always use hostnames instead of IP addresses.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.