Storing query result in a table-CodePudding

Hy! Is there any option to storing query results in a table clob field? For example after opening a command window and running queries after I want to store any kind of script output (oracle error codes too if any script run would crash) to database table. Like a spool command but not in a file.

CREATE TABLE queries 
 (
  query_result CLOB
 );

After opening a command window

SQL> SELECT TO_CHAR
2    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
3    FROM DUAL;
NOW
-------------------
09-14-2021 20:18:16

And I want to store "09-14-2021 20:18:16" to queries.query_result field.

Thanks!

CodePudding user response：

What you've described is called auditing. I'd suggest you not to reinvent the wheel, but use what Oracle already provides.

CodePudding user response：

You can use DBMS_XMLGEN.GETXML to generate an XML representation for the results of any query.

For example, run the query in the below statement. Don't forget to remove the trailing semicolon.

insert into queries
select dbms_xmlgen.getxml
    (q'[
        SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL
    ]')
from dual;

The results will look like:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <NOW>09-14-2021 23:26:25</NOW>
 </ROW>
</ROWSET>

You could automate this with a PL/SQL script, but I'm not sure how you would get it to automatically run as part of a tool like SQL Developer.