Transform SQL to XML in Oracle

This article presents methods to transform an SQL to XML format with minimum effort possible.

Let’s take simple SQL

SELECT owner, table_name 
  FROM dba_tables
 WHERE table_name LIKE 'USER%' and owner='SYS';

OWNER                          TABLE_NAME                   
------------------------------ ------------------------------
SYS                            USER_HISTORY$                 
SYS                            USER_ASTATUS_MAP              
SYS                            USER$

and try to transform it into XML format

Continue reading

Oracle GANTT report in SQL

This article presents how to generate GANTT reports using sql.

A GANTT charts are commonly used in project management or batch processing, as one of the most popular and useful ways of showing activities (tasks or events) displayed against time. On the left of the chart is a list of the activities and along the top is a suitable time scale. Each activity is represented by a bar; the position and length of the bar reflects the start date, duration and end date of the activity. This allows you to see at a glance:

  • What the various activities are
  • When each activity begins and ends
  • How long each activity is scheduled to last
  • Where activities overlap with other activities, and by how much
  • The start and end date of the whole project

Continue reading

String concatenation aggregation methods

This article describes method to aggregate and concatenate strings.

CREATE TABLE test_tbl
(
 grp_id   NUMBER,
 elm_id   NUMBER,
 elm_name VARCHAR2(25)
);

INSERT INTO test_tbl
SELECT 1, LEVEL, 'my name 1_'||LEVEL 
FROM dual 
CONNECT BY level < 5;

INSERT INTO test_tbl
SELECT 2, LEVEL, 'my name 2_'||LEVEL
FROM dual
CONNECT BY LEVEL < 5;

INSERT INTO test_tbl
SELECT 3, LEVEL, 'my name 3_'||LEVEL
FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

We have following data

SELECT * FROM test_tbl
ORDER BY 1, 2;

    GRP_ID     ELM_ID ELM_NAME                
---------- ---------- -------------------------
         1          1 my name 1_1              
         1          2 my name 1_2              
         1          3 my name 1_3              
         1          4 my name 1_4              
         2          1 my name 2_1              
         2          2 my name 2_2              
         2          3 my name 2_3              
         2          4 my name 2_4
         3          1 my name 3_1              
...
         3       1000 my name 3_1000

and purpose is to covert it to following layout

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4
      3  my name 3_1,...                    ,my name 3_1000

Continue reading

Status in AUD$ ACTION#

If you want to filter and analyze your audit data from AUD$ table you need to scan data using ACTION# column. It keeps information about audit actions but is NUMERIC columns. To find out meaning of the column check AUDIT_ACTIONS table

So using simple join between AUD# and AUDIT_ACTIONS tables filter data as you want

SELECT *
  FROM sys.aud$
 WHERE ntimestamp# > SYSDATE - 7
  AND action# IN (
        SELECT action 
          FROM audit_actions
        WHERE name IN 
          ('ALTER USER',
           'CREATE USER',
           'DROP USER'));

List of all audit actions

SELECT action, name 
  FROM audit_actions
ORDER BY 1;

Continue reading