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 →

This article focus on ORA_HASH function in Oracle. This function computes a hash value for a given expression.

SELECT
  ORA_HASH('test') oh,
  ORA_HASH('test1') oh1
FROM dual;

        OH        OH1
---------- ----------
2662839991 1531096017

Continue reading →

This short article presents how to solve ORA-22828 error.

ORA-22828 happens when you try to replace part of a CLOB with a CLOB bigger than 32K using standard functions like REPLACE, REGEXP_REPLACE.

Continue reading →

This article show how to translate string separated by a character

'STR1,STR2,STR3,STR4'

into rows

STR_REC               
-------------------
STR1
STR2
STR3
STR4

Continue reading →

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 →

This article shows how works table pseudocolumn ORA_ROWSCN. The column provides SCN (System Change Number) when last DML was executed on block or row level for a table . SCN system change number can be easily translated as timestamp using function SCN_TO_TIMESTAMP. The SCN as timestamp is approximate to 3 seconds.

Continue reading →

This article presents constraints types that can be created on view level:

• check option
• read only
• primary key
• unique key
• foreign key

Continue reading →