Thursday, May 31, 2012

Loading images into Oracle XDB

There are a number of different ways to load images (and other filetypes) into Oracle XDB. You can use FTP, WebDAV or PL/SQL.

For APEX installations using the Embedded PL/SQL Gateway (EPG) images are loaded onto the server, and then loaded into XDB using PL/SQL script apxldimg.sql. With a couple of minor edits to change destination folders, the apxldimg.sql script can be re-purposed for your own applications.

The script creates a database directory, and then reads a XML file (default is imagelist.xml) to identify the directories to create and files to upload.

To generate your own imagelist file, you can use the following Windows batch script from a command window directing the output to a filename.

   imagelist.bat > imagelist.xml


@echo off
@REM *****************************************************************
@REM * File:    imagelist.bat
@REM * Author:  Mark Lancaster May 2012
@REM * Purpose: For APEX applications using the Embedded PL/SQL Gateway.
@REM             Generate XML list of directories and files suitable
@REM             for loading into Oracle XDB.
@REM             
@REM             Refer APEX file "apxldimg.sql" for example usage.
@REM
@REM             Direct output to a file e.g.  imagelist.xml
@REM *****************************************************************

setlocal enabledelayedexpansion

echo ^<upload^>
echo     ^<directories^>

REM list directories with relative path

for /F "tokens=*" %%c in ('dir /ad /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<directory^>!relpath!^<^/directory^>
)

echo     ^<^/directories^>
echo     ^<files^>

REM list files with relative path and leading slash

for /F "tokens=*" %%c in ('dir /a-d /ogn /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<file^>^/!relpath!^<^/file^>
)

echo     ^<^/files^>
echo ^<^/upload^>