GK One-Line Question & Answer

An approach where privacy protections are built into database systems from the beginning (by design) rather than added later (by afterthought); includes: minimizing data collection (only store what is needed) purpose limitation (data used only for stated purpose) data retention policies (auto-delete after retention period) privacy-preserving queries (differential privacy k-anonymity) and privacy impact assessments before schema changes

Stored procedure: a named block that executes logic and may return values via OUT/IN OUT parameters called with EXECUTE/CALL cannot be used in SQL expressions. Stored function: MUST return a single value via RETURN can be called from within SQL expressions (SELECT WHERE) and is side-effect restricted in SQL contexts

A compiler directive that associates a user-defined exception name with an Oracle error number allowing you to catch specific Oracle errors by name rather than using WHEN OTHERS and checking SQLCODE

Implicit cursor: automatically created by PL/SQL for single-row SELECT INTO or DML statements (attributes: SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN). Explicit cursor: developer-declared named cursor for multi-row queries with full control over OPEN FETCH CLOSE lifecycle

BULK COLLECT: fetches multiple rows from a cursor into a collection in a single context switch. FORALL: executes a DML statement for all elements of a collection in a single context switch. Both minimize expensive PL/SQL-to-SQL engine context switches

A pointer to a query result set that can be passed as a parameter between programs or returned from a function; advantage: the query can be determined at runtime (dynamic) and the cursor can be returned to a calling program (Java/Python) as a result set

Used to execute dynamic SQL strings built and executed at runtime allowing PL/SQL to execute DDL DML with runtime-determined table names/conditions or PL/SQL anonymous blocks not known at compile time

Associative Array (INDEX BY): unbounded sparse indexed by PLS_INTEGER or VARCHAR2 exists only in PL/SQL memory. Nested Table: initially dense can have gaps (DELETE) can be stored in database column. VARRAY: fixed maximum size always dense (no gaps) ordered can be stored in database column

A pragma that makes a PL/SQL block run in its own independent transaction separate from the calling transaction - used for: writing audit/log records that persist even if the main transaction rolls back

Statement-level trigger: fires ONCE for the entire DML statement regardless of how many rows are affected (cannot access :NEW/:OLD). Row-level trigger (FOR EACH ROW): fires ONCE for each affected row has access to :NEW (new values) and :OLD (old values) for validation and auditing

An error (ORA-04091) that occurs when a row-level trigger tries to read from or write to the table that fired the trigger - the table is in an inconsistent state during the DML so Oracle prevents the trigger from querying it to avoid reading partial/inconsistent data

When an exception is raised in an inner block PL/SQL searches for a handler in that block first; if not found the exception propagates to the enclosing outer block; if no handler is found at any level it propagates to the calling environment as an unhandled exception

A package is a schema object that groups related PL/SQL types variables constants cursors exceptions procedures and functions into a single logical unit with a specification (public interface) and body (implementation); advantages: encapsulation state persistence via package variables improved performance and overloading

Definer rights (default): procedure executes with the privileges of the owner/definer regardless of who calls it. Invoker rights (AUTHID CURRENT_USER): procedure executes with the privileges of the caller enforcing caller-specific access controls; Invoker rights: better security (no privilege escalation) Definer rights: simpler deployment

Opens a REF CURSOR for a dynamically-built SQL query then uses BULK COLLECT to fetch all rows into a collection in a single context switch combining the flexibility of dynamic SQL with the bulk processing performance of BULK COLLECT

A function that returns rows incrementally (one at a time) rather than building the entire result collection in memory before returning; the SELECT statement can begin processing rows as they are produced by PIPE ROW enabling streaming and reducing memory requirements

Pct TYPE: declares a variable with the same data type as a specific column automatically adapting if the column type changes. Pct ROWTYPE: declares a record with the same structure as a table row or cursor result with fields matching all column names and types

A preprocessor feature that uses $IF/$ELSIF/$ELSE/$END IF directives evaluated at compile time to include or exclude code sections; used for: maintaining different versions of code for different environments (dev/test/prod) including debug logging in dev only handling version-specific features

A package that provides PL/SQL access to OS filesystem files (read and write) allowing PL/SQL programs to read/write files on the database server; security controlled by the DIRECTORY object which must be granted to users - prevents access to arbitrary paths

A comprehensive job scheduling framework supporting: complex schedules (calendaring syntax: FREQ=DAILY;BYHOUR=2;BYMINUTE=0) job chains (workflow) job classes with resource limits event-based job triggering and detailed job logging - more feature-rich and reliable than the simpler DBMS_JOB package

A PL/SQL performance hint that passes OUT/IN OUT parameters by reference instead of by default copy (pass by value) avoiding expensive copying of large collections/records; should be used when: parameters are large (LOBs big collections) and the overhead of copying would be significant

A cursor FOR loop automatically handles OPEN FETCH and CLOSE operations and declares an implicit record variable reducing boilerplate code and eliminating the risk of forgetting to close cursors; also allows implicit cursor (inline SELECT) without a named cursor declaration

A clause indicating that the function always returns the same result for the same input parameters allowing Oracle to: cache the result and skip re-execution for duplicate inputs within a SQL statement enable use in function-based indexes and skip re-execution for same inputs in a query

RESULT_CACHE: Oracle caches function results in the SGA result cache shared across sessions with automatic invalidation when dependent tables change. DETERMINISTIC: hints to Oracle that results can be cached within a single SQL statement/query no cross-session sharing no automatic invalidation on table changes

A PL/SQL package providing cryptographic functions: symmetric encryption/decryption (AES 3DES DES) hashing (SHA-1 SHA-256 MD5) MAC generation and random key/data generation - enabling column-level application encryption separate from TDE

A package for writing debug/informational messages to a server-side buffer that can be displayed by client tools like SQL Plus; limitations: buffer has a maximum size (default 20000 bytes max 1000000) messages only displayed AFTER the PL/SQL block completes (no streaming) not suitable for real-time monitoring and adds overhead

Static SQL: SQL statements written directly in PL/SQL code at compile time (SELECT col INTO var FROM t WHERE id=x); validated and optimized at compile time gives better performance and error detection. Dynamic SQL (EXECUTE IMMEDIATE NDS): SQL constructed as strings at runtime; necessary when table/column names or statement type are not known until runtime but has higher overhead and risk of SQL injection if not using bind variables

FORALL sends the entire collection of DML operations to the SQL engine in a single context switch (one round-trip) vs. a regular FOR loop which sends each DML operation individually (one round-trip per iteration); FORALL is typically 10-100x faster for bulk DML because it eliminates N-1 context switches for N rows

A PL/SQL record type is a composite data type that groups related fields of different data types into a single structure (similar to a struct in C or row in a table); a collection is an ordered set of elements all of the same type. Records group heterogeneous fields; collections group homogeneous elements

DBMS provides built-in concurrency control ensuring consistent simultaneous multi-user access; file systems provide no such mechanism leading to race conditions and data corruption under concurrent access

The process of transforming a conceptual ER model into a concrete relational schema following systematic rules for entities, relationships, and attributes

An architectural pattern separating applications into presentation tier (UI), application/logic tier (business rules), and data tier (DBMS); enables independent scaling, better security, and technology flexibility per tier

The process of copying and maintaining database objects in multiple databases simultaneously; primary use cases: high availability (failover replica), read scalability (read replicas), geographic distribution, backup, and analytics reporting on replica

A group of database servers working together to provide higher availability, scalability, and fault tolerance than a single instance; includes shared-nothing clusters (sharding), shared-disk clusters (Oracle RAC), and active-passive failover clusters

A software layer between the application code and the database that abstracts specific database implementation details allowing the application to work with multiple database systems or switch databases without major code changes; examples include ORM frameworks and database adapter libraries

The process of transferring data between storage types, formats, or systems while ensuring data integrity, consistency, and completeness; key challenges include schema mapping differences, data type incompatibilities, referential integrity violations, handling NULL values, and performance for large datasets

Hot standby: a replica that is continuously synchronized and can accept connections immediately on failover (seconds); warm standby: synchronized but not fully ready until activated (minutes); cold standby: requires restoration from backup (hours)

A technique that maintains a pool of pre-established database connections that can be reused by application threads, avoiding the expensive cost of creating and destroying a new connection for each request; solves connection overhead, connection limits, and connection storms under high load

A pattern where write operations go to the primary database and read operations go to one or more replica databases; trade-off: replicas may lag behind primary so reads may return slightly stale data - applications must decide which reads require strong consistency versus which can tolerate stale data

A middleware component that sits between the application and the database server providing connection pooling, query routing (read-write splitting), load balancing, query caching, query filtering/firewall, and transparent failover without application changes