Solved

What will be the best way to copy files from Oracle?

  • 24 January 2022
  • 5 replies
  • 372 views
ZTC ZTC JGOTA
Rank
Userlevel 6
Badge +14

Hello Community,
I hope everyone is doing fine.

I need to create a copy of about 18,000(+) documents. The main reason to perform this task is that another company bought part of our company. Therefore, I need to create a copy of the physical documents of the business section sold to send documents to the other company.

What will be the best to copy all the documents and create an extraction to move them to another database?

Any idea how could I do that? 

Any format?

Any type way to do that?

What tables in IFS will contain the physical documents?

Any advice on how to perform this project?

We are using IFS 9

 

Any advice will be appreciated.

Thanks,
JL 


 

 

 

icon

Best answer by Mathias Dahl 24 January 2022, 10:05

View original
JL
This topic has been closed for comments

5 replies

Mathias Dahl
Rank
Userlevel 7
Badge +30

Hi,

Attached is an SQL script I wrote 10 years ago. It was a proof of concept to see if I could extract files from the database and out to disk. The script does not say and I cannot remember how well it worked, but I think it worked :) The script relies on the fact that you can create a folder on the Oracle server and then create an Oracle DIRECTORY object connected to that.

I don’t know if there are any size limits to take into account, but perhaps you can start experimenting and see.

Make sure to read the comments!

 

1 Attachment
/Mathias
H
Rank
Userlevel 6
Badge +14

Hi @ZTC ZTC JGOTA 

I do not know of a IFS tool that can do that, but it can be done with the IFS data provider

if you are using the database repository the file will be stored as base64 in column edm_file_storage.file_data. The procedure will then be:

  • Create the sql that fetch the files.
  • Loop through the files.
  • Extract the bytes.
  • Send the bytes to a storage (stream, file, database)

Below is a code snippet on howto stream a file (httpResponse) in c#.

You can also save the bytes to a file.

 

Mathias Dahl
Rank
Userlevel 7
Badge +30

@Hans Andersen

Thanks for sharing that code. I want to comment on this though: 

if you are using the database repository the file will be stored as base64 in column edm_file_storage.file_dat

We do NOT Base64 encode the data before storing it in the table column. The bytes are stored as they are in the BLOB.

You might be mislead by having to use Base64 encoding and decoding when using certain tools and in some scenarios, but that is another thing. I would say that, if we can avoid using Base64 encoding, and we often can, we should do it. It just adds overhead.

 

/Mathias
H
Rank
Userlevel 6
Badge +14

@Mathias Dahl ,

 

We do NOT Base64 encode the data before storing it in the table column. The bytes are stored as they are in the BLOB

 

When I wrote the code I must have convinced myself otherwise. The above code is working, but better just to do this instead : 

 

Mathias Dahl
Rank
Userlevel 7
Badge +30

What looks to be happening in that commented line there is that you request for the binary attribute to be returned as a string (...ToString()). That will get you a string, for sure, not an array/collection of bytes. Then it is natural that you need to convert said string back into a byte array, which is what System….FromBase… does.

So, good for you that you avoided a totally unnecessary conversion to and from a string :)

 

/Mathias
Terms & ConditionsCookie settings