Oracle -> Write excel file from PL/SQL -> Tutorial 6
 

Tutorials

Tutorial

Hyperlinks and formulas

Deutsche Version

LOB

All functions of the previous tutorials are also available, if you don't want to write to a file but into a LOB, e.g. to attach it to a mail.

Usually you just have to replace the file handle of the parameterlist by a CLOB, which is always defined as IN OUT NOCOPY.

DECLARE
  l_cursor NUMBER := dbms_sql.open_cursor;
  v_clob   CLOB;

BEGIN
  dbms_sql.parse(
     l_cursor
    ,'SELECT   username, user_id, created ' ||
     'FROM     all_users ' ||
     'WHERE    UPPER(username) = ''HR'' '
    ,dbms_sql.native
    );
  xml_spreadsheet.abfrage(
     p_cursor => l_cursor
    ,p_file => v_clob
    ,p_sheetname => 'query'
    );
END;
/

Now the LOB contains exactly the file of the tutorial 'First Steps' at test 2. To check this you have to write it into a file. So add in the declaration part:

  v_fileHandle utl_file.FILE_TYPE;

And at the end of the PL/SQL block:

  v_fileHandle := utl_file.fopen ('DOC', 'clobtest.xml', 'wb', 32767);
  utl_file.put_raw(v_fileHandle, utl_raw.cast_to_raw(v_clob));