1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

read_image_file('path where the photos are','msg.mcn');

Discussion in 'SQL PL/SQL' started by ksjj, Dec 5, 2010.

  1. ksjj

    ksjj Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    hi,

    we have a admin wizard where in there are 850 officials whose details are like

    emp id
    date of birth
    date of joining
    basic pay
    photo
    etc........

    i have the photos of the officials out of the data base.

    i have created a report to generate the details of the officials. what i would like to have is to get the photo of each official from the file which is not in the data base. i use oracle 8i and forms and reports 6i

    i have the command in pre_form
    read_image_file('path where the photos are','msg.mcn');

    this gives the same photo to all the officials.

    how do i be able to link the photos to their emp id and get it


    thanks in advance
    ksjj
    Member

    Posts: 5
    Joined: Sun Aug 02, 2009 4:42 am
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: photo id

    HI ksjj

    It's very difficult to give you a suggestion based on the information you have provided. When you say the images are not stored in the database, where are they stored? In the filesystem? If they are in the filesystem, does there exist anywhere a mapping between the employee id and the filenames? If not, how can you search the files by employee id?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to store in the record the file locator for the desired image using the BFILE data type:

    Code (SQL):
    CREATE TABLE mytab(
    empid NUMBER,
    ename varchar2(40),
    ...
    empimg bfile);
     
     
    INSERT INTO mytab (empno, ename, empimg)
    VALUES(123455, 'Myrtlebug', bfilename('/u01/images/employee','mbug.jpg'));
     
     
    This creates a LOB locator for the external file and associates it with the desired empid. You can now access the exernal image through the dbms_lob package.
     
  4. ksjj

    ksjj Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    hi,

    thanks for the reply. Not much exposure in oracle since i am a beginner .

    I have some clarification since i have not used "empimg" till now. Do i have to modify the table by adding empimg as a column. Is this employee image?

    01) i have around 900 employees where some of them have not yet given their photos.

    02) Do i have to insert values for all the 900 employees using insert command. this might take a long time.

    "INSERT INTO mytab (empno, ename, empimg)
    VALUES(123455, 'Myrtlebug', bfilename('/u01/images/employee','mbug.jpg'));"


    03) "This creates a LOB locator for the external file and associates it with the desired empid. You can now access the exernal image through the dbms_lob package. "

    I created a CF in report like this

    function CF_1Formula return Char is
    begin
    Return ('D:\'||:EmpNo||'.GIF');
    end;

    this gives me the photo of only the first empno and the photos of subsequent empno does not get picked.

    Can i use dbms_lob in function?

    thanks in advance
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's clear you do not understand the concept of a BFILE; using a BFILE locator associates a given external file (image file, etc) with a given record in the table; you have a table full of employee data already, what you should be doing is modifying that table to include a BFILE type column of your own naming convention and then populate that column with a locator to the correct external file so that the all you need do is read from the locator and the correct image appears.

    Your function takes no arguments, so it's not surprising it doesn't work as expected. Were it to take a file name or empid as a parameter then you would return the proper image for the given employee id.