Writes data to a file from a SQL statement.
filename Use this LONG VARCHAR parameter to specify the file name.
file_contents Use this LONG BINARY parameter to specify the contents to write to the file.
The function writes file_contents to the file filename. It returns 0 if successful, and non-zero if it fails.
The filename value can be prefixed by either an absolute or a relative path. If filename is prefixed by a relative path, then the file name is relative to the current working directory of the database server. If the file already exists, its contents are overwritten.
This function can be useful for unloading long binary data into files.
DBA authority required
This example uses xp_write_file to create a file accountnum.txt containing the data 123456:
CALL xp_write_file( 'accountnum.txt', '123456' );
This example queries the Contacts table of the sample database, and then creates a text file for each contact living in New Jersey. Each text file is named using a concatenation of the contact's first name (GivenName), last name (Surname), and then the string .txt (for example, Reeves_Scott.txt), and contains the contact's street address (Street), city (City), and state (State), on separate lines.
SELECT xp_write_file( Surname || '_' || GivenName || '.txt', Street || '\n' || City || '\n' || State ) FROM Contacts WHERE State = NJ;
This example uses xp_write_file to create an image file (JPG) for every product in the Products table. Each value of the ID column becomes a file name for a file with the contents of the corresponding value of the Photo column:
SELECT xp_write_file( ID || '.jpg' , Photo ) FROM Products;
In the example above, ID is a row with a UNIQUE constraint. This is important to ensure that a file isn't overwritten with the contents of subsequent row. Also, you must specify the file extension applicable to the data stored in the column. In this case, the Products.Photo stores image data (JPGs).