There was a need to ensure the Oracle database interaction with the system that can not work with Oracle, but is able to put the files on the FTP -server in the file system. I decided to use the Oracle the XML the DB. With FTP -server focus was not successful, because the system has involved some unsupported command server. He remembered that Windows is able to connect the WebDAV-resource in the form of a drive letter. With this it turned out. So…

In Oracle Standard Edition One, established by default, pre-installed everything you need to run the XML the DB. Tweaking is necessary.

Configuring ACL for TEST_USER user (required in upper case!):

DECLARE 
aclxml VARCHAR2 (4000): = 
'
<Acl description = "All privileges to TEST_USER" 
         xmlns = "http://xmlns.oracle.com/xdb/acl.xsd" 
         xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" 
         xsi: schemaLocation = "http://xmlns.oracle.com/xdb/acl.xsd 
         http://xmlns.oracle.com/xdb/acl.xsd " 
         shared = "true"
>
        <Ace>
                <Grant> true </ grant>
                <Principal> TEST_USER </ principal>
                <Privilege>
                        <All />
                </ Privilege>
        </ Ace>
</ Acl>
'
;
retb BOOLEAN;

BEGIN
        retb: = DBMS_XDB.CREATERESOURCE ( '/sys/acls/all_test_user_acl.xml', aclxml);
END;
/

Create a folder:

DECLARE retb BOOLEAN;
BEGIN
        retb: = DBMS_XDB.CREATEFOLDER ( '/ home');
        retb: = DBMS_XDB.CREATEFOLDER ( '/ home / test_user');
END;
/

Assign ACL to create a resource (folder):

exec DBMS_XDB.SETACL ( '/ home / test_user', '/sys/acls/all_test_user_acl.xml');

Create a resource (file) with a certain contents, as follows:

DECLARE retb BOOLEAN;
BEGIN
retb: = DBMS_XDB.CREATERESOURCE ( '/home/test_user/intest/2.txt', 'The contents of the new file.');
END;
/
commit;

Delete resource (file):

exec DBMS_XDB.DELETERESOURCE ( '/home/test_user/intest/2.txt');
commit;

To see all of the resources in the ” /home/test_user” you can request the following:

SQL> select path (1) from resource_view where under_path (res, '/ home / test_user', 1) = 1;

PATH (1)
-------------------------------------------------- --------------------------------------------------
intest
intest / 1.txt

View the contents of the resource (the file) can be the following query:

SQL> select r.res.extract ( 'Resource / Contents / text / text ()') as text 
  2 from resource_view r
  3 where equals_path (res, '/home/test_user/intest/1.txt') = 1;

TEXT
-------------------------------------------------- --------
12312312312

If the user has no authority to the resource, the result will be empty.

Includes support for HTTP on port, such as 8080:

exec dbms_xdb.SETHTTPPORT (8080);

For the FTP :

exec dbms_xdb.SETFTPPORT (2121);

See the FTP – and HTTP IR ports can request:

SQL> select dbms_xdb.GETFTPPORT () ftp, dbms_xdb.GETHTTPPORT http from dual;

        FTP HTTP
---------- ----------
        2121 8080

You can run the following command on the command line of the server:

lsnrctl status

If the pin is an indication of the port 8080, the database means that the port is listening. The output should look something like this:

...
Listening Endpoints Summary ...
  (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = dbhost) (PORT = 1521)))
  (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = dbhost) (PORT = 2121)) (Presentation = FTP) (Session = RAW))
  (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = dbhost) (PORT = 8080)) (Presentation = HTTP) (Session = RAW))
Services Summary ...
...

If possible, the HTTP -port better set up on port 80, as WebClient (WebDAV-client under Windows) can not work with non-standard ports. (At least, I could not make it to work.)
If this is not possible, then you need to use some reverse-proxy. We had nginx …
Append location to the configuration file:

location / home {
        proxy_pass http://127.0.0.1:8080$request_uri;
}

In our case, the oracle and nginx installed on the same computer.

Now we configure WebDAV support in Windows on a server, on which we need to connect the WebDAV-resource. To do this, add Features. Windows Server 2012 R2 is done as follows:

  • Administrative Tools, Server Manager, Manage, Add Roles and Features, next-next-next to Features
  • Expand User Interfaces and Infrastructure
  • Select and install the Desktop Experience

Then, you must enable Basic Authorization. To do this, edit the registry:

HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ WebClient \ Parameters

BasicAuthLevel” Assign a value of 2.

Many services are running on behalf of the SYSTEM“” and they need to mount the drive in a special way: to create a new Task Scheduler schedule, for example, Mount WebDAV“” with the launch of At startup“” and the implementation of the file, such as C:\map_webdav_drive.bat“”, the contents of which is as follows:

@echo on
net use / delete Z:
net use Z: "http: // dbhost / home / test_user" password / USER: test_user

Connect the disk ” Z:,” resource ” http://dbhost/home/test_user,” c password ” password” out of the user ” test_user.”

Reboot the server. After the reboot, the drive will be connected, but it will only be available to ” SYSTEM“. Accordingly, all services will be able to access it.

(Visited 37 times, 1 visits today)
adminOracleTips
There was a need to ensure the Oracle database interaction with the system that can not work with Oracle, but is able to put the files on the FTP -server in the file system. I decided to use the Oracle the XML the DB. With FTP -server focus was...

Do you want to be notified about new DBA updates, releases, jobs and free tips? Join our email newsletter. It's fast and easy. You will be among the first to know about hot new DBA updates and stuff, it will help you enhance your DBA skills.
We take your privacy very seriously