Hi @Ramadass ,

If you just what to know the file extension you can do something, not so elegant, like this 

select edm_file_api.Get_File_Type(s.doc_class, s.doc_no, s.doc_sheet, s.doc_rev, s.doc_type, s.file_no) as file_type,
EDM_APPLICATION_api.Get_File_Extention(edm_file_api.Get_File_Type(s.doc_class, s.doc_no, s.doc_sheet, s.doc_rev, s.doc_type, s.file_no)) as file_extention,
s.* from edm_file_storage s
where s.doc_no = '1004956'

but IFS has no knowledge of what is actually in the blob. You cannot check-in a .pdf and get it out as .doc.

HI @Hans Andersen ,

if .pdf file checked in and I need to take that .pdf file not as doc file.

in edm_file_storage_tab ‘file data’ column we have blob data.

whether possible to extract the blob data as respective file format.

Hi,

I think you have to explain you scenario. What are you trying to do?

Ramadass,

Is it that you want to lift the pdf from the blob field? If so, the following might be a starting point:

 /*******************************************************************************
 Description: Copy image of invoice to directory

 Date Author Description
 -------------------------------------------------------------------------------
 26-03-2018 Steve Alink Initial create
 *******************************************************************************/
 function Cpy_Invoice(ora_dir_ in varchar2,
 doc_no_ in varchar2, doc_sheet_ in varchar2, doc_rev_ in varchar2,
 doc_type_ in varchar2 default 'ORIGINAL', file_no_ in varchar2 default 1, doc_ext_ in varchar2)
 return varchar2
 is
 pdf_file utl_file.file_type;
 Stor_buf_ raw(32000);
 pdf_blob blob;
 pdf_blob_len integer;
 vstart number := 1;
 addmax binary_integer := 32000;
 x number;
 l_filename_ varchar2(100);

 cursor get_lob is
 -- Get LOB locator
 select file_data
 from ifsapp.edm_file_storage
 where doc_no = doc_no_
 and doc_sheet = doc_sheet_
 and doc_rev = doc_rev_
 and doc_type = doc_type_
 and file_no = file_no_;

 begin
 open get_lob;
 fetch get_lob into pdf_blob;
 close get_lob;

 pdf_blob_len := dbms_lob.getlength(pdf_blob);

 -- Open the destination files.
 l_filename_ := source_ERP_ || '_' || doc_no_ || '.' || doc_ext_;
 pdf_file := utl_file.fopen(ora_dir_, l_filename_, 'wb', 32764);

 vstart := 1;

 -- Save blob length
 x := pdf_blob_len;

 -- If small enough for a single write
 IF pdf_blob_len < 32760 THEN
 utl_file.put_raw(pdf_file, pdf_blob);
 utl_file.fflush(pdf_file);
 ELSE
 -- Doesn't fit, have to write it in pieces
 vstart := 1;
 WHILE vstart < pdf_blob_len and addmax > 0 LOOP
 dbms_lob.read(pdf_blob, addmax, vstart, Stor_buf_);

 utl_file.put_raw(pdf_file, Stor_buf_);
 utl_file.fflush(pdf_file);

 -- Set the start position for the next cut
 vstart := vstart + addmax;

 -- Set the end position if less than 32000 bytes
 x := x - addmax;
 if x < 32000 then
 addmax := x;
 end if;
 end loop;
 end if;

 -- Close the files.
 utl_file.fclose(pdf_file);
 return 'RANOK';

 exception
 when others then
 -- Close the files if something goes wrong.
 if utl_file.is_open(pdf_file) then
 utl_file.fclose(pdf_file);
 return 'ERROR';
 end if;
 raise;
 end Cpy_Invoice;

Good luck,

Steve

HI @eqbstal ,

Thanks for your solution...

Could you please tell me how can we find ‘ora_dir_’ original directory for a file which we are going to export?

best regards,

Ramadass

Ramadass,

Use the following to see if there are any available for owner IFSAPP:

SELECT * FROM all_directories;

If you want to create a new one use:

CREATE or replace directory DOC_TRANSFER AS 'C:/ifs/oradb/APPS10/Docs';

Directory C:/ifs/oradb/APPS10/Docs must exist on the database server

GRANT read, write on directory DOC_TRANSFER to IFSAPP;

In PLSQL you would assign the directory as:

ora_dir_    varchar2(250) := ‘DOC_TRANSFER’;

In the example above I have declared ora_dir_ as a global variable.

Steve

Hi @eqbstal ,

I am taking the exist document so I need to find the exist directory name.

I got directory names from all_directories but there are many names.

how can we find the particular directory name which linked to that particular file?

Could you please give one example..

Thanks in advance…

best regards,

Ramadass

Ok, this is basically a free build of software . Here we go.

Prepare a new directory

This is based upon the network directory:

Notice that it is possible to use a network directory as well. So doesn't have to be on the database server (this would be if you really use a drive letter as in one of my earlier posts.

Below find the total script that you need to run. Notice that in the bottom there are some hardcoded values used with regards to the document number etc.

declare

  MyDir_    varchar2(200) := 'MYEQEEP'; -- Same as created one
  err_msg_  varchar2(50);

/*******************************************************************************
 Description: Copy image of invoice to directory

 Date Author Description
 -------------------------------------------------------------------------------
 26-03-2018 Steve Alink Initial create
 *******************************************************************************/
function Cpy_Doc(ora_dir_ in varchar2,
  doc_no_ in varchar2, doc_sheet_ in varchar2, doc_rev_ in varchar2,
  doc_type_ in varchar2 default 'ORIGINAL', file_no_ in varchar2 default 1, doc_ext_ in varchar2)
  return varchar2
  is
  pdf_file utl_file.file_type;
  Stor_buf_     raw(32000);
  pdf_blob      blob;
  pdf_blob_len  integer;
  vstart        number := 1;
  addmax        binary_integer := 32000;
  x             number;
  l_filename_   varchar2(100);

  cursor get_lob is
  -- Get LOB locator
  select file_data
  from ifsapp.edm_file_storage
  where doc_no = doc_no_
  and doc_sheet = doc_sheet_
  and doc_rev = doc_rev_
  and doc_type = doc_type_
  and file_no = file_no_;

  begin
  open get_lob;
  fetch get_lob into pdf_blob;
  close get_lob;

  pdf_blob_len := dbms_lob.getlength(pdf_blob);

  -- Open the destination files.
  l_filename_ := 'NewFile_' || doc_no_ || '.' || doc_ext_;
  pdf_file := utl_file.fopen(ora_dir_, l_filename_, 'wb', 32764);

  vstart := 1;

  -- Save blob length
  x := pdf_blob_len;

  -- If small enough for a single write
  if pdf_blob_len < 32760 then
    utl_file.put_raw(pdf_file, pdf_blob);
    utl_file.fflush(pdf_file);
  else
    -- Doesn't fit, have to write it in pieces
    vstart := 1;
    while vstart < pdf_blob_len and addmax > 0 loop
      dbms_lob.read(pdf_blob, addmax, vstart, Stor_buf_);

      utl_file.put_raw(pdf_file, Stor_buf_);
      utl_file.fflush(pdf_file);

      -- Set the start position for the next cut
      vstart := vstart + addmax;

      -- Set the end position if less than 32000 bytes
      x := x - addmax;
      if x < 32000 then
        addmax := x;
      end if;
    end loop;
  end if;

  -- Close the files.
  utl_file.fclose(pdf_file);
  return 'RANOK';

  exception
  when others then
  -- Close the files if something goes wrong.
  if utl_file.is_open(pdf_file) then
   utl_file.fclose(pdf_file);
   return 'ERROR';
  end if;
  raise;
end Cpy_Doc;

begin
  -- Run the function with some parameter. 
  err_msg_ := Cpy_Doc(ora_dir_ => MyDir_, doc_no_ => '1202457', doc_sheet_ => '1', doc_rev_ => 'A1', doc_type_ => 'VIEW', file_no_ => 1, doc_ext_ => 'pdf');
end;

Notice that I used doc_type_ VIEW which generates the correct output as well.

I've only tested this with a pdf document that is stored in the database. The result can be found in windows explorer:

Steve

HI @eqbstal ,

I executed with valid directory but I am getting the error like below screenshot.

Please help to debug this error..

best regards,

Ramadass

My guess is that the windows directory that is connected to the Oracle MyDir_ value doesn't allow anything to be written from IFS Applications.

Check the security settings of the windows directory. In the above example \\fs01\apbio\bio\IFS\Eqeep should be checked and set the directory open for full access.

Altenative:

Try another directory as you got a lot of results back from select * from all_directories;

Steve  

HI @eqbstal ,

Thank you very much…

Its working fine.