I am building a file upload package (or actually it has been built and I am expanding it). The file should be uploaded by a third party and when it\u2019s done, then this code should kick off and upload the file to a clob for further processing.<\/p>\n
The problem is that the third party can still be in the process of uploading the file while the package is trying to read it to upload it into a clob. How can you be sure the file is complete before you start reading it?<\/p>\n
<\/p>\n
One option is to check for the existance of a different file. If this one exists, then the third party is still uploading. Another option is to try to rename the file. If this fails, then the third party is still uploading.<\/p>\n
The idea I came up with, is to try to open the file for appending, effectively locking the file. If this works out, then close the file right away and execute the normal processing. If there is an error of some sort, then just try again. To make sure I don\u2019t end up in an endless loop, I decided to exit my check loop, when 2 minutes or more have passed. (They must be using a really slow connection when this is the case, something like an ancient 1200baud modem \ud83d\ude09 )<\/p>\n
1:<\/span> ...<\/pre>\n 2:<\/span> l_StartTime := CurrentTimeInSeconds;<\/pre>\n 3:<\/span> l_CurrentTime := CurrentTimeInSeconds;<\/pre>\n 4:<\/span> -- 0. Try to acquire a lock on the file for max 2 minutes<\/span><\/pre>\n 5:<\/span> -- locking is done by trying to append to the file.<\/span><\/pre>\n 6:<\/span> while<\/span> l_lock and<\/span> ((l_CurrentTime - l_StartTime) <= 120) loop<\/pre>\n 7:<\/span> begin<\/span><\/pre>\n 8:<\/span> l_CurrentTime := CurrentTimeInSeconds;<\/pre>\n 9:<\/span> l_Handle := utl_file.fopen( location => r_file_definitions.directory_object<\/pre>\n 10:<\/span> , filename => l_dir_list(i)<\/pre>\n 11:<\/span> , open_mode => 'A'<\/span><\/pre>\n 12:<\/span> );<\/pre>\n 13:<\/span> l_lock := false<\/span>;<\/pre>\n 14:<\/span> exception<\/span><\/pre>\n 15:<\/span> when<\/span> utl_file.file_open then<\/span> l_lock := true<\/span>;<\/pre>\n 16:<\/span> when<\/span> utl_file.invalid_path then<\/span> l_lock := true<\/span>;<\/pre>\n 17:<\/span> when<\/span> utl_file.invalid_mode then<\/span> l_lock := true<\/span>;<\/pre>\n 18:<\/span> when<\/span> utl_file.invalid_filehandle then<\/span> l_lock := true<\/span>;<\/pre>\n 19:<\/span> when<\/span> utl_file.invalid_operation then<\/span> l_lock := true<\/span>;<\/pre>\n 20:<\/span> when<\/span> utl_file.read_error then<\/span> l_lock := true<\/span>;<\/pre>\n 21:<\/span> when<\/span> utl_file.write_error then<\/span> l_lock := true<\/span>;<\/pre>\n 22:<\/span> when<\/span> utl_file.internal_error then<\/span> l_lock := true<\/span>;<\/pre>\n 23:<\/span> when<\/span> utl_file.invalid_maxlinesize then<\/span> l_lock := true<\/span>;<\/pre>\n 24:<\/span> when<\/span> utl_file.invalid_filename then<\/span> l_lock := true<\/span>;<\/pre>\n 25:<\/span> when<\/span> utl_file.access_denied then<\/span> l_lock := true<\/span>;<\/pre>\n 26:<\/span> when<\/span> utl_file.invalid_offset then<\/span> l_lock := true<\/span>;<\/pre>\n 27:<\/span> when<\/span> utl_file.delete_failed then<\/span> l_lock := true<\/span>;<\/pre>\n 28:<\/span> when<\/span> utl_file.rename_failed then<\/span> l_lock := true<\/span>;<\/pre>\n 29:<\/span> when<\/span> others then<\/span> l_lock := true<\/span>;<\/pre>\n 30:<\/span> end<\/span>;<\/pre>\n 31:<\/span> end<\/span> loop; <\/pre>\n 32:<\/span> if<\/span> not<\/span>(l_lock) then<\/span><\/pre>\n 33:<\/span> -- 0b. Release the file if it has been locked<\/span><\/pre>\n 34:<\/span> utl_file.fclose(file<\/span> => l_Handle);<\/pre>\n 35:<\/span> ...<\/pre>\n<\/p><\/div>\n<\/div>\nOf course, this is not \u2018waterproof\u2019 but it seems to get the job done. The l_lock variable is declared as a boolean and is initialized to true before the loop starts.<\/p>\n
I think I don\u2019t need this many exceptions, but during development I decided to handle all exceptions separately so I had an easy template to do some logging for which I created a separate log package. When I had it working, I removed the log messages.<\/p>\n","protected":false},"excerpt":{"rendered":"
I am building a file upload package (or actually it has been built and I am expanding it). The file should be uploaded by a third party and when it\u2019s done, then this code should kick off and upload the file to a clob for further processing. The problem is that the third party can […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-322","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=322"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/322\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}