CODEWITHSUNDEEP
sqloracle-databasefile
Laszlo Marton
Laszlo Marton

Reputation: 1

oracle sql script access file from mapped drive

I need to read some large data from a file into a blob field.

With some testing, I managed to do so as long as the file was on the C: drive (ex. Desktop).

However my script that needs this functionality is in a project located on a mapped drive, and the file I want to read is also located there within a different folder. Whenever I run the script, it returns with file not found error "ORA-22288: file or LOB operation FILEOPEN failed. The system cannot find the file specified.". (All examples I've found on the net used some predetermined file paths like /home or similar)

Here's part of the script:

CREATE OR REPLACE DIRECTORY FILEUPLOADS AS 'X:/Path/To/File';

DECLARE
dest_loc  BLOB := empty_blob();
src_loc   BFILE := BFILENAME('FILEUPLOADS', 'fileToRead.txt');
BEGIN
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => true, dur => dbms_lob.session);
    DBMS_LOB.LOADFROMFILE(dest_lob => dest_loc, src_lob => src_loc, amount => DBMS_LOB.getLength(src_loc));
    UPDATE MY_TABLE SET MY_COLUMN = dest_loc WHERE ID = 3;
END;

Could someone point out how I can access my file on the mapped drive? I also should mention that several databases need the same data.

UPDATE: We cannot access the server, so a directory cannot be created and mapped to it. (thanks for those who suggested it though.)

Folder setup:

X:/Path/To/File

X:/Path/To/Script

Upvotes: 0

Views: 1876

Answers (2)

David D'Lima
David D'Lima

Reputation: 111

You would need to create a directory on your server and then map that directory.

The command would be as : CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name'; Path_name would be your operating system directory path.

Check the link below. IT is very well explained: http://www.dba-oracle.com/t_oracle_create_directory.htm

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

I don't think that it'll work that way; if it is a mapped drive, create a directory using UNC, such as in this example:

create directory slike_4010 as '\\my_server\d$\Home\GIS\slike_4010';

Upvotes: 0

Related Questions