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));