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!

Query to Display Contents of Attached Word, Excel, Pdf,Jpeg etc...

Discussion in 'Oracle SCM & Manufacturing' started by kiran.marla, Sep 26, 2012.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi All,

    All we know that we have an option to attach a file from front end. We can attach word , excel, Jpeg etc.

    Now the query is to get display the content from back end.

    Code (SQL):
    SELECT
      AD.SEQ_NUM                  ,
      DCT.USER_NAME               ,
      DAT.USER_NAME               ,
      AD.ATTACHED_DOCUMENT_ID     ,
      DET.USER_ENTITY_NAME        ,
      DAT.NAME                    ,
      D.DOCUMENT_ID               ,
      AD.ENTITY_NAME              ,
      AD.PK1_VALUE                ,
      D.MEDIA_ID                  ,
      D.URL                       ,
      DT.TITLE                    ,
      l.FILE_DATA                 ,
      DBMS_LOB.SUBSTR(L.file_data,2000,1) file_data
    FROM FND_DOCUMENT_DATATYPES DAT,
      FND_DOCUMENT_ENTITIES_TL DET    ,
      FND_DOCUMENTS_TL DT             ,
      FND_DOCUMENTS D                 ,
      FND_DOCUMENT_CATEGORIES_TL DCT  ,
      FND_ATTACHED_DOCUMENTS AD       ,
      FND_LOBS  L                        
    WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID
    AND DT.DOCUMENT_ID           = D.DOCUMENT_ID
    AND DCT.CATEGORY_ID          = D.CATEGORY_ID
    AND D.DATATYPE_ID            = DAT.DATATYPE_ID
    AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE
    AND L.FILE_ID                = D.MEDIA_ID
    AND DAT.name                 = 'FILE';
    Now the data is displaying in hexadecimal (I think).

    How to display the data in normal text from word, pdf, excel from attached file.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Any updates on this ....
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kiran,

    Thanks for that query. It works fine. I have tested on sales order and purchase order attachments and it retrieved successfully.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Bharat,

    Are you able to retrieve the file contents also? Please reply ...
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes, but File data is retrieved in Hexadecimal format.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yes Bharat, but I need its contents in normal characters for Excel, Pdf, Word etc.

    I am able to retrieve for normal txt files.
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Dear Kiran,

    We need to convert the data from hexadecimal values to normal text. Thats bit hard to code it down here. I will try to do it ASAP. If you resolved it then try to post it here.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kiran,

    Do you got any solution for converting hexadecimal to plain text?
     
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Bharat,

    We haven't got any query to convert hex into text. We raised a SR and is under process.

    Surely 'll share info once I got solution.
     
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thanks Kiran.. Iam also trying to decode it but it seems to be hard to do so..
     
  11. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Painful SR :( ....
     
  12. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    What happened mate??
     
  13. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    The SR handler is eating my brain with spoons. I clearly explained her the problem, uploaded the screen shots. I don't understand where the problem lies , problem lies at her side or my side :( . Giving Very stupid answers...
     
  14. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    I hope there is nothing much to explain from your side also regarding this issue. This was just query issue. They can solve it easily. But be cool mate, they will provide you right solution at the end.
     
  15. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi ,

    Its very happy to say that I am getting file contents also. Here by giving raw code and modify according to your requirements .

    The following code creates a file in server /usr/tmp path.

    Parameters : FILE_ID

    Code (SQL):
    DECLARE
    l_file UTL_FILE.FILE_TYPE;
    l_buffer RAW(32767);
    l_amount BINARY_INTEGER := 32767;
    l_pos INTEGER := 1;
    l_blob BLOB;
    l_blob_len INTEGER;
    l_blob_fnme VARCHAR2(200);
    BEGIN

    SELECT file_name, file_data
    INTO l_blob_fnme, l_blob
    FROM FND_LOBS
    WHERE FILE_ID = 784505;



    l_blob_len := DBMS_LOB.getlength(l_blob);
    -- Open the target file.
    l_file := UTL_FILE.fopen('/usr/tmp',l_blob_fnme, 'A', 32767);
    -- Loop through the BLOB and write to file
    -- until complete.
    WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
    END LOOP;

    -- Close the file.
    UTL_FILE.fclose(l_file);

    EXCEPTION
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
    UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
    END;
    /
    Happy 'FILE'ing :)
     
  16. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Does that source file should in /usr/tmp folder or anywhere in server?
     
  17. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    The following command gives the valid path for creating file .

    Code (SQL):
    SELECT * FROM v$parameter WHERE name LIKE 'utl_file_dir';
     
  18. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Unlikely Oracle SR Team fails to provide solution as there is no option to get word, excel, pdf contents.

    Metalink id: 436308.1 provides the description about it.