How to I interleave logs from two different Oracle tables?

Question

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's going on in both pieces of code. Both tables have a time-stamp column (although the columns have different names). Here's a stripped-out version of the two tables, and what I'd like as a result:

Table LOG_1:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3

Table LOG_2:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824005  Log_2 entry 3

New table LOG_COMBINED:

TIME                          ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3
11-SEP-12 12.39.16.037824005  Log_2 entry 3

(Note the [artificial] difference in the timestamps!)

I'm sure this can be done in a simple SQL statement - perhaps involving a union all?

Answer 1

select 'LOG_1' AS LOG_SOURCE, TIME_CREATED,ENTRY_TEXT FROM LOG_1

UNION ALL

select 'LOG_2', TIME_CREATED,ENTRY_TEXT FROM LOG_2

ORDER BY 2

I formalize this kind of logic as a view so you can document and comment it.

CREATE OR REPLACE FORCE VIEW COMBINED_LOGS

(LOG_SOURCE,

TIME_CREATED,

ENTRY_TEXT ) AS

select 'LOG_1' , TIME_CREATED,ENTRY_TEXT FROM LOG_1

UNION ALL

select 'LOG_2', TIME_CREATED,ENTRY_TEXT FROM LOG_2;

COMMENT ON TABLE COMBINED_LOGS IS 'Explanatory comment goes here';

This works for me on Oracle 11g R2 and Oracle 9i, perhaps it was the use of keyword SOURCE as a column name which I have corrected to LOG_SOURCE

Edit: Corrected to UNION all after the comment. See here for more discussion on the details of UNIONS. This should be done for performance reasons and to ensure identical records are not removed.