Solved

document attachments


Badge +5

Hi All,

 

I have found  blob format  as file data column in edm_file_storage_tab but I need to get file as respective file format like PDF,docs. Is there any conversion procedure there in IFS?

icon

Best answer by eqbstal 20 May 2020, 19:45

View original

11 replies

Userlevel 6
Badge +14

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.

Badge +5

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.

 

 

Userlevel 6
Badge +14

Hi,

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

Userlevel 6
Badge +14

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

Badge +5

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

 

 

Userlevel 6
Badge +14

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

Badge +5

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

 

Userlevel 6
Badge +14

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

Prepare a new directory

Figure 1 - Create the Oracle directory

This is based upon the network directory:

Figure 2 - The same directory but now from Windows Explorer

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:

Figure 3 - New file in directory generated

Steve

Badge +5

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

Userlevel 6
Badge +14

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  

Badge +5

HI @eqbstal ,

 

Thank you very much…

Its working fine.

Reply