PL/SQL TEXT_IO package

I am trying to write to a local file from a PL/SQL script. In order to do this, I am attempting to use the TEXT_IO package in PL/SQL.

DECLARE
file_out text_io.file_type;
len number;
blob_file blob;
my_var RAW(50);
bstart NUMBER := 1;
bytelen NUMBER := 50;

开发者_JS百科BEGIN

SELECT xxx
INTO blob_file
FROM yyy
WHERE zzz

dbms_lob.read(blob_file, bytelen, bstart, my_var);    
file_out := text_io.fopen('local_file_path', 'w');
text_io.put_raw(file_out, my_var);
text_io.fflush(file_out);
text_io.fclose(file_out);

END;
/

quit

However, when I run this script I get the error,

PLS-00201: identifier 'TEXT_IO.FILE_TYPE' must be declared

Does anyone know how I can fix this error, and how I can write the contents of the blob to a file as I am attempting to do?

Thanks,

ktm

---

TEXT_IO exists only in Oracle Forms which had (in the old client/ server days) a client-side PL/SQL interpreter. If you are using SQL*Plus to execute PL/SQL, as it appears you are doing here, the TEXT_IO package will not be available and you will not be able to write to a file on the client machine (barring the odd setup where the server mounts a drive that your client is exposing and then proceeds to write to that mount).

Now, you can generally use SQL*Plus to directly write to a local file using the SPOOL command. Unfortunately, it's probably unlikely that you could do this for a BLOB in the general case.

---

If you want to create a file on the server UTL_FILE is a good choice. This package can write files in any DIRECTORY specified in the database. A DIRECTORY is created in Oracle using CREATE DIRECTORY and can be linked to any writable directory accessible by the DBMS (server-side).

---

The general approach is: write a file on the server and download it. Or event better, don't write it down, just stream it. Quite complicated, yes.