✨ Experience Tip: Pull this repository and enjoy it with VS Code ✨
A Transition Guide for PostgreSQL Users 🚀
Dive deep into database development with this guide, designed to help individuals proficient in PostgreSQL and analytical SQL step smoothly into server-side programming using Oracle Database and its potent procedural partner, PL/SQL.
- Developers and analysts fluent in PostgreSQL seeking to master Oracle PL/SQL.
- Individuals transitioning roles where Oracle database development is key.
- Anyone wanting to grasp Oracle's specific SQL extensions and server-side coding prowess.
- Grasp and employ Oracle's unique SQL style and data kinds.
- Craft capable PL/SQL code, building blocks, procedures, functions, packages, and triggers.
- Guide data effectively with Oracle's DML, transaction handling, and advanced queries.
- Work with complex data types, notably XML, residing in Oracle.
- Understand and use Oracle's own concepts like the DUAL table and ROWNUM.
- Manage exceptions gracefully and use collections in PL/SQL.
- Implement techniques boosting performance, like bulk operations.
- Walk through and utilize the Oracle Data Dictionary.
- Gain a solid sense of Oracle performance tuning and reading EXPLAIN PLANs.
- Understand the conceptual weave of Oracle with tech like Java (JDBC) and XML.
01Crossing the Divide +
Here, we step across, finding Oracle's SQL voice for those who know PostgreSQL best.
- Key Differences & Core Syntax
- Data Types (Oracle Specific): VARCHAR2, NUMBER, DATE, TIMESTAMPs
- DUAL Table (Oracle Specific)
- NULL Handling: NVL, NVL2, COALESCE
- Conditional Expressions: DECODE, CASE
- ROWNUM Pseudo-column (Oracle Specific)
02Essential Functions & DML +
Unlock key Oracle functions and master the foundational dance of Data Manipulation Language (DML).
- Date Functions: SYSDATE, TO_DATE, ADD_MONTHS, etc.
- String Functions
- Set Operators: MINUS
- DML & Transaction Control: INSERT, UPDATE, DELETE, COMMIT, ROLLBACK
03Advanced Querying +
Ascend to advanced querying heights, tackling complex data patterns with Oracle's unique tools. 🏔️
- Hierarchical Queries (Oracle Specific - Very Important): CONNECT BY, LEVEL, PRIOR
- Analytic (Window) Functions: RANK, DENSE_RANK, LAG, LEAD
- MERGE statement (Oracle Specific)
04Conquering Complexity +
Face the challenge of intricate data forms like XML and JSON, vital for systems like Flexcube, exploring Oracle's modern touch.
- Large Objects: CLOB, BLOB
- XMLTYPE Data Type: Storage and Querying
- JSON Data Type: Native Storage and Querying
- Oracle 23ai Features: JSON Relational Duality Views ✨, JSON Binary Type 💾
05PL/SQL Awakening +
Awaken your inner Oracle programmer. This chunk lays the ground for PL/SQL, from block shape to basic flow, introducing a new 23ai speed boost.
- PL/SQL Block Structure: DECLARE, BEGIN, EXCEPTION, END
- Variables & Constants: %TYPE, %ROWTYPE
- Control Flow: IF, CASE, LOOPs
- SQL within PL/SQL
- Oracle 23ai Feature: SQL Transpiler for optimization ⏩
- Oracle® Database PL/SQL Language ReferenceThe core guide for PL/SQL syntax, block structure, and control flow.
- Oracle Database 23ai New Features GuideDetails the new SQL Transpiler feature.
06PL/SQL Precision +
Gain precision with PL/SQL cursors for fetching data and shaping reusable code blocks with procedures and functions.
- Cursors: Implicit, Explicit, Cursor FOR loops
- Stored Procedures & Functions: Syntax, Parameter Modes (IN, OUT, IN OUT)
- Oracle® Database PL/SQL Language ReferenceThe definitive reference for cursors and subprogram definitions.
- Oracle® Database Development GuideProvides practical context and design patterns for procedures and functions.
07PL/SQL Resilience +
Build resilience in your code! Organize with packages, handle errors surely, and automate actions with triggers.🛡️
- Packages: Specification & Body, Overloading
- Exception Handling: Predefined, User-defined, SQLCODE, SQLERRM
- Triggers: DML Triggers, :NEW & :OLD qualifiers
- Oracle® Database PL/SQL Language ReferenceThe complete guide to packages, exception handling syntax, and trigger creation.
08PL/SQL Mastery +
Master powerful PL/SQL moves! Handle complex data bunches and make things fly with bulk actions and dynamic SQL, key for big systems like Flexcube. 💪
- Collections & Records: Associative Arrays, Nested Tables, Varrays
- Bulk Operations for Performance: BULK COLLECT, FORALL
- Dynamic SQL: EXECUTE IMMEDIATE
- Oracle® Database PL/SQL Language ReferenceYour primary source for advanced data structures and high-performance PL/SQL techniques.
09PL/SQL Fusion +
Experience PL/SQL fusion! Explore standard packages for common tasks and see JavaScript step in as a code buddy in Oracle 23ai. 🤝
- Built-in Packages: DBMS_LOB, DBMS_XMLGEN, UTL_FILE, DBMS_AQ
- Oracle 23ai Feature: JavaScript Stored Procedures 🌐
- Oracle® Database PL/SQL Packages and Types ReferenceThe encyclopedia for all `DBMS_` and `UTL_` packages.
- Oracle Database JavaScript Developer's GuideThe key guide for integrating JavaScript into the Oracle database.
10The Oracle Blueprint +
Get the Oracle blueprint in your mind! This chunk brings vital database ideas for a consulting path – structure, data map, and fresh 23ai touches for schema and data. 🏛️
- Data Dictionary & Metadata Views: USER_, ALL_, DBA_
- Schema Objects Overview: Tables, Views, Indexes, Sequences
- Concurrency Control (MVCC) & Transaction Management
- Oracle 23ai Features: Usage Domains 🎯, Annotations ✍️, Wide Tables
↔️
- Oracle® Database ConceptsFoundational knowledge on Oracle's architecture.
- Oracle® Database ReferenceThe source of truth for all Data Dictionary views.
11Guardians of Oracle +
Stand guardian over your data! This chunk highlights database safety rules and new Oracle 23ai security shields, crucial for keeping sensitive info and systems safe. 🔒🛡️
- Oracle 23ai Feature: SQL Firewall 🔥
- Oracle 23ai Feature: Column-Level Auditing 🕵️ & Data Redaction 🎭
- Oracle 23ai Feature: Multicloud Authentication ☁️🔑
- Oracle 23ai Feature: Schema Privileges ✅
- Oracle® Database Security GuideThe primary source for all security-related features.
- Oracle Database SQL Firewall User's GuideA deep dive into the powerful new SQL Firewall.
12Speed Unleashed +
Unleash speed! This chunk digs into Oracle indexing tactics and how to peek into query speed using `EXPLAIN PLAN`, vital for making database work flow fast. 🏎️💨
- Indexing in Oracle: B-Tree, Bitmap, Function-Based, Composite
- Understanding Oracle’s EXPLAIN PLAN: Generating & Interpreting
- Oracle® Database SQL Tuning GuideThe go-to guide for understanding query optimization and execution plans.
- Oracle® Database ConceptsExplains the fundamental concepts behind different index types.
13Performance Symphony +
Conduct a performance symphony! This chunk explores deeper query tuning, touching on optimizer hints and managing table stats, plus new 23ai speed gains. 🎼📈
- Basic Query Tuning: SARGable predicates, join efficiency
- Optimizer Hints: Awareness and cautious use
- Table Statistics & DBMS_STATS
- Oracle 23ai Features: Real-Time SQL Plan Management 🚦, SQL Analysis Report 🩺
- Oracle® Database SQL Tuning GuideThe main source for advanced tuning, hints, and statistics management.
- Oracle® Database PL/SQL Packages and Types ReferenceDetails the `DBMS_STATS` package for statistics gathering.
14Oracle Horizons +
Look to Oracle's horizons! This chunk explores how Oracle links with tech like Java and XML, message systems, and new 23ai bits making connections shine brighter. ✨🔗
- Oracle & Java Connectivity (JDBC)
- Oracle & XML Processing
- Oracle Advanced Queuing (AQ) & JMS
- Oracle 23ai Features: Enhanced Connection Pooling 🏊♂️, Async Pipelining 📊, OpenTelemetry 🔭
- Oracle® Database JDBC Developer's GuideEssential for understanding Java-to-Oracle connectivity.
- Oracle ... Advanced Queuing User's GuideThe core document for understanding Oracle's messaging system.
- Oracle® Universal Connection Pool Developer's GuideKey for understanding modern connection pooling features.
15Future of Oracle +
Peek into Oracle's future! This chunk shines solely on the vibrant new SQL features arriving in Oracle 23ai, showing Oracle's path towards modern, sharper SQL power. ⭐🔮
- Boolean Data Type ✅❌
- Direct Joins for UPDATE/DELETE ➡️
- GROUP BY Column Alias 🔡
- IF [NOT] EXISTS for DDL 🚦
- SELECT without FROM Clause ✨
- Table Value Constructor 🛠️
- Oracle Database 23ai New Features GuideThe first and most vital source for all new SQL power in Oracle 23ai.
- Oracle® Database SQL Language ReferenceThe definitive reference where these new features will be fully documented.
Get a local copy of all the course materials and the essential PDF library.
Progress sequentially through the 15 numbered directories, as concepts build on each other.
In each chunk's directory, run `dataset.sql` or `NewSchema.sql` to prepare your database environment.
Tackle the problems in `exercises.md` to solidify your understanding. Then check your work in the `solutions/` folder.
Use the included `books/` directory. It's your offline, comprehensive source for deep dives into any Oracle topic.
The key to mastery is doing. Experiment, modify, and break the code. This is how you'll truly learn.
This course material is open for all under the MIT License. Share the knowledge!
Happy Learning! 😊