Oracle Database

Oracle Database, developed by Oracle Corporation, is one of the world’s most popular and powerful relational database management systems (RDBMS). It is widely used in enterprise environments for its scalability, reliability, and advanced features.

Oracle Database stands out due to:

• High Availability: Features like Real Application Clusters (RAC) and Data Guard provide robust solutions for minimizing downtime and ensuring business continuity.
• Scalability: Supports massive data volumes and concurrent users, making it suitable for large-scale enterprise applications.
• Advanced Security: Offers comprehensive security features including encryption, auditing, and fine-grained access control.
• Comprehensive Tooling: Includes tools for backup, recovery, monitoring, and performance tuning.
• PL/SQL Support: Oracle’s procedural extension to SQL enables complex business logic within the database.
• Multi-Model Database: Supports relational, JSON, XML, spatial, and graph data models.
• Cloud Integration: Oracle Database is available both on-premises and as a cloud service (Oracle Cloud Infrastructure).
• Strong Community & Support: Backed by extensive documentation, a large user community, and professional support.

Oracle Database continues to evolve with regular updates, new features, and cloud-native capabilities, maintaining its position as a leading choice for mission-critical data management.

Web Site

• Oracle Database Official Site
• Oracle Documentation

Tools

• SQL*Plus
• Oracle SQL Developer
• Oracle Enterprise Manager
• Data Pump (expdp/impdp)
• RMAN (Recovery Manager)
• Oracle APEX

Basic

show all oracle table

SELECT TABLE_NAME FROM USER_TABLES;

Date manipulating

SELECT
    sysdate cur_date,          -- current datetime
    trunc(sysdate) date_str,   -- current date (without time)
    trunc(sysdate-2) two_days, -- two days from now
    -- datetime with tz
    TO_TIMESTAMP_TZ('2026-03-07T19:40:00 +08:00', 'YYYY-MM-DD"T"HH24:MI:SS TZH:TZM')
FROM DUAL;

DECODE

The DECODE function in Oracle SQL provides conditional logic similar to a simple CASE or a switch statement in programming languages. It allows you to return different values based on matching conditions.

Syntax:

DECODE(expr, search1, result1, [search2, result2, ...], [default])

• expr: The value to compare.
• searchN: The value to match against expr.
• resultN: The value returned if expr = searchN.
• default: (Optional) Value returned if no match is found.

Example: Suppose you have a table employees with a column grade:

You can use DECODE to map grades to descriptions:

SELECT name,
       DECODE(grade, 1, 'Junior', 2, 'Mid', 3, 'Senior', 'Unknown') AS grade_desc
FROM employees;

This returns ‘Junior’ for grade 1, ‘Mid’ for grade 2, ‘Senior’ for grade 3, and ‘Unknown’ for any other value.

Note: For more complex conditions, consider using the CASE expression, which is more flexible and ANSI SQL compliant.