PL/SQL & T-SQL (Oracle and SQL Server)
Database programming on Oracle and SQL Server: PL/SQL blocks and packages, T-SQL procedures, errors, transactions, cursors, plans, security and tests.
51 lessons: 15 free with Pro answers and 36 with a free core.
Examples run with oracle-free 23.26.3.0.0 and mssql 17.0.5005.3.
Continue: PL/SQL and T-SQL: what they are and where they run
Who it is for
Developers, analysts and DBAs who write or maintain stored procedures, packages and triggers on Microsoft SQL Server or Oracle Database, including people moving between the two.
What you will be able to do
- Write T-SQL batches, stored procedures and functions with variables, control flow and table-valued parameters
- Handle errors and transactions in T-SQL with TRY...CATCH, THROW, XACT_ABORT and XACT_STATE
- Replace row-by-row cursors with set-based code built on temporary tables, MERGE with OUTPUT and window functions
- Write PL/SQL blocks, procedures, functions and packages whose exceptions propagate the way you intend
- Process rows in bulk in PL/SQL with BULK COLLECT, LIMIT, FORALL and SAVE EXCEPTIONS
- Build dynamic SQL in both engines that binds every value and checks every identifier against an allowlist
- Read execution plans in SQL Server and Oracle, and fix parameter sniffing and slow PL/SQL called from SQL
- Test database code with tSQLt and utPLSQL, and port code between PL/SQL and T-SQL
Before this track
Revise and practise
Modules and lessons
What the labels mean
- Free lesson, Pro answers
- the whole lesson is free; the model answers to its interview questions open with a pass
- Free core, Pro depth
- the core of the lesson is free; its in-depth parts open with a pass
- Pro
- opens with a Pro, Premium or Ultimate pass
Minutes are reading time: “3 of 10 min free” means 3 minutes of the lesson are free.
What a pass opens in this track
72 in-depth parts and 126 interview answers. The same pass opens every other track and every Pro tool.
- Module 1
Start here: two engines, versions and a verified lab
What PL/SQL and T-SQL are and which releases you will meet, a lab with both engines on your own machine, and the practice schema with the dialect differences that break moved code.
- PL/SQL and T-SQL: what they are and where they run Free lesson, Pro answers: 15 minutes
- Set up a lab with Oracle 26ai and SQL Server 2025 Free lesson, Pro answers: 15 minutes
- The practice schema and dialect basics Free lesson, Pro answers: 10 minutes
- Module 2
T-SQL foundations: batches, variables and routines
Batches and GO, variables and their scope, IF, WHILE and CASE, stored procedures with parameters, and scalar, inline and multi-statement functions.
- T-SQL batches, GO and variables Free lesson, Pro answers: 10 minutes
- T-SQL control flow: IF, WHILE and CASE Free core, Pro depth: 3 of 10 minutes free
- T-SQL stored procedures and parameters Free core, Pro depth: 3 of 10 minutes free
- T-SQL functions: scalar, inline and multi-statement Free core, Pro depth: 3 of 10 minutes free
- Module 3
T-SQL errors and transactions
Transactions, @@TRANCOUNT and savepoints, TRY...CATCH with THROW and RAISERROR, and XACT_ABORT with XACT_STATE for transactions that can no longer commit.
- T-SQL transactions, @@TRANCOUNT and savepoints Free lesson, Pro answers: 10 minutes
- T-SQL TRY...CATCH, THROW and RAISERROR Free core, Pro depth: 4 of 10 minutes free
- XACT_ABORT, XACT_STATE and doomed transactions Free core, Pro depth: 3 of 10 minutes free
- Module 4
Working with sets: temporary tables, MERGE and cursors
Local and global temporary tables, table variables and table-valued parameters, MERGE with OUTPUT for safe upserts, and cursors rewritten as set-based code.
- T-SQL temporary tables: #local and ##global Free lesson, Pro answers: 15 minutes
- Table variables and table-valued parameters Free core, Pro depth: 5 of 15 minutes free
- T-SQL MERGE, OUTPUT and safe upserts Free core, Pro depth: 4 of 10 minutes free
- T-SQL cursors and set-based rewrites Free core, Pro depth: 6 of 15 minutes free
- Module 5
Window functions in T-SQL
Ranking and offset functions, then window frames, the default frame and running totals that stay correct with ties.
- T-SQL ranking and offset window functions Free lesson, Pro answers: 20 minutes
- T-SQL window frames and running totals Free core, Pro depth: 7 of 20 minutes free
- Module 6
Dynamic SQL and triggers in T-SQL
Dynamic SQL with sp_executesql, bound parameters and QUOTENAME, and triggers with the inserted and deleted tables and the pitfalls of multi-row changes.
- Dynamic SQL with sp_executesql, safely Free lesson, Pro answers: 10 minutes
- T-SQL triggers: inserted, deleted and pitfalls Free core, Pro depth: 5 of 15 minutes free
- Module 7
Execution plans and performance in SQL Server
Reading estimated and actual execution plans, parameter sniffing, parameter-sensitive plans and Query Store.
- Reading SQL Server execution plans Free lesson, Pro answers: 10 minutes
- Parameter sniffing, PSP and Query Store Free core, Pro depth: 5 of 15 minutes free
- Module 8
Production T-SQL: concurrency, security, new features and tests
Locking, RCSI and deadlocks, permissions and ownership chains, JSON and regular expressions in SQL Server 2025, tests with tSQLt, and code review drills.
- Locking, RCSI and deadlocks in SQL Server Free lesson, Pro answers: 15 minutes
- Security for T-SQL code: permissions and chains Free core, Pro depth: 6 of 15 minutes free
- SQL Server 2025: JSON, regex and new functions Free core, Pro depth: 4 of 10 minutes free
- Testing T-SQL: tSQLt, CI and safe deployments Free core, Pro depth: 6 of 15 minutes free
- T-SQL code review and interview drills Free core, Pro depth: 6 of 15 minutes free
- Module 9
PL/SQL foundations: blocks, types, SQL and exceptions
Anonymous blocks, variables and data types, control structures and iterators, SELECT INTO and DML inside PL/SQL, and how exceptions propagate.
- PL/SQL blocks, variables and data types Free lesson, Pro answers: 25 minutes
- PL/SQL control structures and iterators Free core, Pro depth: 7 of 20 minutes free
- SQL inside PL/SQL: SELECT INTO and DML Free core, Pro depth: 7 of 20 minutes free
- PL/SQL exceptions and how they propagate Free core, Pro depth: 7 of 20 minutes free
- Module 10
Cursors, collections and bulk processing
Explicit cursors and cursor FOR loops, records and collections, BULK COLLECT with LIMIT, and FORALL with SAVE EXCEPTIONS and DML error logging.
- Explicit cursors and cursor FOR loops Free lesson, Pro answers: 15 minutes
- PL/SQL records and collections Free core, Pro depth: 5 of 15 minutes free
- BULK COLLECT with LIMIT Free core, Pro depth: 5 of 15 minutes free
- FORALL, SAVE EXCEPTIONS and DML error logging Free core, Pro depth: 8 of 25 minutes free
- Module 11
Subprograms, packages and robust error logging
Procedures and functions with parameter modes, cursor variables that return result sets, packages and session state, error logging with backtraces, and dependencies.
- PL/SQL procedures, functions and parameters Free lesson, Pro answers: 15 minutes
- PL/SQL packages, session state and ORA-04068 Free core, Pro depth: 7 of 20 minutes free
- Error logging, backtraces and autonomous logs Free core, Pro depth: 5 of 15 minutes free
- Dependencies, compilation and warnings Free core, Pro depth: 6 of 15 minutes free
- Module 12
Triggers in Oracle
Row, statement and INSTEAD OF triggers, then compound triggers and the mutating-table error they avoid.
- Oracle DML triggers: row, statement, INSTEAD OF Free lesson, Pro answers: 10 minutes
- Compound triggers and the mutating-table error Free core, Pro depth: 6 of 15 minutes free
- Module 13
Dynamic SQL and security in PL/SQL
EXECUTE IMMEDIATE and DBMS_SQL with bind variables and DBMS_ASSERT, and definer's rights, invoker's rights and roles.
- Dynamic SQL in PL/SQL without injection Free lesson, Pro answers: 15 minutes
- Definer's rights, invoker's rights and roles Free core, Pro depth: 6 of 15 minutes free
- Module 14
Performance: plans, PL/SQL called from SQL and table functions
Execution plans with DBMS_XPLAN, the cost of calling PL/SQL from SQL and how to cut it, and pipelined table functions and SQL macros.
- Oracle execution plans with DBMS_XPLAN Free core, Pro depth: 7 of 20 minutes free
- PL/SQL functions in SQL: avoiding the slowdown Free core, Pro depth: 6 of 15 minutes free
- Pipelined table functions and SQL macros Free core, Pro depth: 6 of 15 minutes free
- Module 15
Production PL/SQL: staging, concurrency, JSON and tests
Staging with temporary tables and MERGE, row locks, SKIP LOCKED and scheduler jobs, JSON in PL/SQL, tests with utPLSQL in CI, and code review drills.
- Staging in Oracle: temporary tables and MERGE Free core, Pro depth: 9 of 25 minutes free
- Row locks, SKIP LOCKED and scheduler jobs Free core, Pro depth: 7 of 20 minutes free
- JSON in PL/SQL: object types and 26ai features Free core, Pro depth: 8 of 20 minutes free
- Testing PL/SQL: utPLSQL, CI and coverage Free core, Pro depth: 5 of 15 minutes free
- PL/SQL code review and interview drills Free core, Pro depth: 5 of 15 minutes free
- Module 16
Across both engines: porting and concurrency
Porting code between PL/SQL and T-SQL construct by construct, and how the two engines differ in locking, versioning and isolation.
- Porting code between PL/SQL and T-SQL Free core, Pro depth: 6 of 15 minutes free
- Oracle vs SQL Server: reads, locks and deadlocks Free core, Pro depth: 10 of 25 minutes free
Related tools
Official documentation
- Oracle AI Database PL/SQL Language Reference 26ai (Oracle)
- Transact-SQL reference (Database Engine) (Microsoft)
- Oracle AI Database 26ai Free (Oracle)
- utPLSQL documentation (utPLSQL project)
- tSQLt user guide (tSQLt)