BUDDHA SERIES

(Unit Wise Solved Question & Answers)

Course – BCA 5th Sem

College – Buddha Degree College
(DDU Code-859)

Department: BCA
Subject: Oracle PL/SQL
Faculty Name: Mr. Peeyush Sharma
 UNIT -3

Integrity and Security: General considerations, Controls, Audit trail, Data encryption, Integrity rules,
Granting and Revoking privileges, SQL support Tools.

1. What are the main goals of database security and integrity?

Answer:
The primary goals of database security and integrity are to protect data from unauthorized
access, modification, or destruction, and to ensure its accuracy, consistency, and reliability
throughout its lifecycle. This involves implementing measures to
maintain confidentiality, integrity, and availability, often referred to as the CIA triad, of the
data.
Here's a more detailed breakdown:
1. Confidentiality:
• Ensuring that sensitive data is accessible only to authorized users.
• Preventing unauthorized disclosure of information.
• Implementing measures like encryption, access controls, and authentication.
2. Integrity:
• Maintaining the accuracy, completeness, and consistency of data.
• Preventing unauthorized modification or deletion of data.
• Ensuring data is reliable and trustworthy for decision-making.
• Implementing measures like data validation, referential integrity, and audit trails.
3. Availability:
• Ensuring that authorized users can access data when they need it.
• Preventing disruptions to data access due to technical issues or malicious attacks.
• Implementing measures like backup and recovery, redundancy, and disaster recovery
planning.
4. Preventing misuse and corruption:
• Protecting against both intentional and unintentional threats to data.
• Implementing access controls to limit user privileges and prevent unauthorized
actions.
• Monitoring database activity for suspicious behavior.
5. Compliance with regulations:
• Ensuring that database security and integrity practices meet legal and industry
standards.
• Protecting sensitive information from unauthorized disclosure or misuse.
• Implementing measures like data masking, encryption, and access controls to comply
with regulations like GDPR.
2. Explain the difference between data security and data integrity.
Answer:
Data security and data integrity, while related, have distinct focuses. Data security is about
protecting data from unauthorized access, use, disclosure, disruption, modification, or
destruction. It's about keeping your data safe from external threats and internal misuse. Data
integrity, on the other hand, focuses on ensuring the accuracy, consistency, and reliability of
data throughout its lifecycle. It's about maintaining the quality and trustworthiness of your
data.
Here's a more detailed breakdown:
Data Security:
• Focus: Protecting data from unauthorized access, modification, or deletion.
• Concerns: Confidentiality (keeping data private), integrity (preventing unauthorized
changes), and availability (ensuring data is accessible when needed).
• Examples: Using strong passwords, implementing access controls, encrypting data,
and using firewalls.
 • Key objective: To prevent data breaches and other security incidents.
Data Integrity:
• Focus: Ensuring the accuracy, completeness, and consistency of data.
• Concerns: Preventing errors in data entry, storage, and transmission, maintaining
data quality over time, and ensuring data is reliable for its intended use.
• Examples: Implementing data validation rules, using checksums to verify data
integrity during transmission, and regularly auditing data for errors.
• Key objective: To ensure data is trustworthy and can be relied upon.

3. Describe the types of controls used in database systems (e.g., access, concurrency, and
inference controls).
Answer:
Database systems employ various control mechanisms to ensure data security, integrity, and
consistency. Key types of controls include:
• Access Controls:
These regulate who can access specific data and what operations they can
perform. Common models include:
• Discretionary Access Control (DAC): The owner of a data object determines
access permissions.
• Mandatory Access Control (MAC): Access is governed by system-wide
security policies, often based on security labels assigned to subjects and objects.
• Role-Based Access Control (RBAC): Permissions are assigned to roles, and
users are granted access by being assigned to specific roles.
• Concurrency Controls:
These manage simultaneous operations by multiple users or processes to prevent conflicts
and maintain data consistency. Techniques include:
• Lock-based protocols: Employ locks on data items to control concurrent access,
such as Two-Phase Locking (2PL), which ensures transactions acquire all
necessary locks before releasing any.
• Timestamp ordering: Transactions are assigned timestamps, and operations are
ordered based on these timestamps to resolve conflicts.
• Optimistic concurrency control: Transactions proceed without explicit locking,
and conflicts are detected and resolved at commit time, typically by rolling back
conflicting transactions.
• Inference Controls:
These prevent unauthorized users from deducing sensitive information by combining
permitted queries or statistical data, even if direct access to the sensitive data is
restricted. This is particularly relevant in statistical databases where users might infer
individual-level data from aggregate statistics. Techniques involve data perturbation, cell
suppression, or restricting query types.

4. Discuss the importance of security policies in database management systems.

Answer:
Security policies are fundamental to effective database management systems (DBMS) for
several critical reasons, primarily centered around protecting data assets and ensuring
business continuity.
1. Data Protection and Confidentiality:
Security policies define rules and procedures to prevent unauthorized access, manipulation, or
destruction of data. This includes specifying access controls, authentication methods, and data
encryption standards, ensuring confidentiality and guarding against data breaches.
2. Data Integrity and Availability:
Policies establish guidelines for maintaining data accuracy and consistency, preventing
unauthorized modifications that could compromise data integrity. They also address measures
for data backup and recovery, ensuring data availability even in the event of system failures or
disasters.
 3. Compliance and Regulatory Adherence:
Many industries and regions have strict regulations (e.g., GDPR, HIPAA) regarding data
protection. Security policies help organizations meet these compliance requirements, avoiding
legal penalties and reputational damage associated with non-compliance.
4. Risk Management and Threat Mitigation:
Policies identify potential vulnerabilities and threats, outlining strategies to mitigate risks
such as insider threats, malware, and injection attacks. They provide a framework for incident
response and recovery, minimizing the impact of security incidents.
5. Operational Efficiency and Consistency:
Clear security policies streamline operations by providing consistent guidelines for data
handling and access. This reduces human error, improves efficiency, and ensures that all
stakeholders understand their roles and responsibilities in maintaining database security.
6. Preserving Brand Reputation and Customer Trust:
Data breaches can severely damage an organization's reputation and erode customer
trust. Robust security policies demonstrate a commitment to protecting sensitive information,
helping to maintain customer confidence and loyalty.
5. What is an audit trail in Oracle? Why is it important?
Answer:
An audit trail in Oracle is a collection of records that log who did what, when, and how
inside the database. It is generated by Oracle’s auditing features (standard auditing, unified
auditing, or fine-grained auditing) and stored in special tables or views so DBAs can review
database activity.
What is an audit trail?
• Definition:
An audit trail is a chronological record of database actions (such as logins, privilege use,
or SQL operations) captured for accountability, compliance, and forensic analysis.
• Where it’s stored:
o In data dictionary views (e.g., DBA_AUDIT_TRAIL,
DBA_AUDIT_SESSION) if database auditing is enabled.
o In OS files or SYS.AUD$ table if audit records are written to the operating
system or database tables.
o In unified audit trail (UNIFIED_AUDIT_TRAIL) in newer Oracle versions
(12c+).
Why is it important?
1. Accountability
o Identifies who performed which operation (e.g., SELECT, INSERT, UPDATE).
o Tracks DBA and privileged user actions to prevent abuse.
2. Security Monitoring
o Detects suspicious or unauthorized activities such as repeated failed logins or
privilege escalations.
o Helps respond to breaches in real time.
3. Compliance and Regulations
o Required by standards like GDPR, HIPAA, SOX, PCI-DSS to demonstrate data
access control.
o Provides legal evidence for audits or investigations.
4. Forensic Analysis
o Useful for post-incident investigations to trace exactly what was changed and
by whom.
5. Policy Enforcement
o Confirms that security policies (least privilege, restricted access) are being
followed.
Example: How to View an Audit Trail
-- Enable auditing (example)
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE BY ACCESS;
-- Query the audit trail
 SELECT USERNAME, ACTION_NAME, OBJ_NAME, TIMESTAMP
FROM DBA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;

6. Explain how Oracle supports auditing of user actions.

Answer:
Oracle supports auditing of user actions using multiple mechanisms that let a DBA track
who did what, when, and how inside the database. These auditing features record activity in
special views or log files for later review. Here’s a structured explanation:
1. Types of Auditing in Oracle
(a) Standard Auditing (Legacy)
• Enabled with the AUDIT command (pre-12c systems).
• Tracks SQL operations such as logins, privilege use, DML, DDL statements.
• Records are stored in:
o DBA_AUDIT_TRAIL (if audit records go into the database)
o Operating system files (if audit records go to OS-level logs)
Example:
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE BY ACCESS;
SELECT USERNAME, ACTION_NAME, OBJ_NAME, TIMESTAMP FROM
DBA_AUDIT_TRAIL;
(b) Unified Auditing (Modern Default in 12c+)
• Combines all audit records (standard auditing, fine-grained auditing, sys operations)
into one unified trail (UNIFIED_AUDIT_TRAIL).
• Faster and centralized with less performance overhead.
• Policies define what to audit (actions, users, privileges, roles).
• Supports conditional auditing (only log events that meet certain criteria).
Example:
CREATE AUDIT POLICY login_audit_policy ACTIONS LOGON;
AUDIT POLICY login_audit_policy;
SELECT USERNAME, ACTION_NAME, EVENT_TIMESTAMP FROM
UNIFIED_AUDIT_TRAIL;
(c) Fine-Grained Auditing (FGA)
• Audits specific columns or rows in a table using DBMS_FGA.
• Allows conditional triggers: only logs access if certain conditions are met.
• Useful for sensitive data auditing (e.g., salary > 100000).
Example:
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'salary_audit',
audit_condition => 'SALARY > 100000',
audit_column => 'SALARY'
);
END;
/
SELECT DB_USER, OBJECT_NAME, SQL_TEXT FROM
DBA_FGA_AUDIT_TRAIL;
2. What Can Be Audited?
• User logins and logouts (via DBA_AUDIT_SESSION)
• Use of privileges (who used CREATE TABLE, DROP USER, etc.)
• Object access (SELECT, INSERT, UPDATE, DELETE on sensitive tables)
• DDL statements (ALTER TABLE, CREATE USER, DROP INDEX, etc.)
• Role activations and policy violations
3. Why Is Auditing Important?
 • Accountability: Tracks individual user actions.
• Compliance: Meets requirements for GDPR, HIPAA, SOX, PCI-DSS.
• Security: Detects suspicious or unauthorized activity in real time.
• Forensics: Supports investigations after data breaches.

7. What types of activities can be captured in an audit trail?

Answer:
An audit trail in Oracle records many different types of database activities, depending on
how auditing is configured. These activities can cover user sessions, SQL statements, object
access, privilege usage, and security policy violations. Here’s a structured list:
1. User Session Activities
• Logons and logoffs
o Successful and failed login attempts
o Time and duration of sessions
• Session details
o Which user connected
o From which host or IP address
Example view: DBA_AUDIT_SESSION
2. SQL Statement Execution
• DDL (Data Definition Language)
o CREATE, ALTER, DROP (tables, users, indexes, procedures, roles)
• DML (Data Manipulation Language)
o SELECT, INSERT, UPDATE, DELETE operations on sensitive tables
• System changes
o Granting/revoking privileges or roles
o Use of administrative commands (e.g., ALTER SYSTEM)
Example view: DBA_AUDIT_STATEMENT, DBA_AUDIT_OBJECT
3. Privilege Usage
• System privileges
o Who used CREATE USER, DROP USER, ALTER DATABASE, etc.
• Object privileges
o Who accessed tables or views they were granted privileges for
• Role activations
o Which roles were enabled in a session
Example view: DBA_AUDIT_TRAIL, DBA_ROLE_PRIVS
4. Object Access and Changes
• Read and write operations
o SELECT on sensitive data (e.g., salaries, personal information)
o INSERT/UPDATE/DELETE of important business records
• Structural changes
o Adding or removing columns
o Modifying constraints or triggers
Example view: DBA_AUDIT_OBJECT
5. Policy Violations and Conditional Auditing
• Fine-Grained Auditing (FGA) can capture:
o Access to specific columns or rows meeting certain conditions (e.g., SALARY >
100000)
o SQL text that triggered the audit
o Bind variable values used in queries
Example view: DBA_FGA_AUDIT_TRAIL
6. Security-Related Events
• Failed logins (authentication issues)
• Password changes or account lockouts
• Use of powerful privileges (SYSDBA, SYSOPER)
• Database startup or shutdown events
 Example view: DBA_AUDIT_SESSION, UNIFIED_AUDIT_TRAIL
9. Why is data encryption essential in modern databases?
Answer:
Data encryption is crucial in modern databases to safeguard sensitive information from
unauthorized access and misuse. By converting readable data into an unreadable format,
encryption ensures that even if a database is compromised, the data remains protected. This is
particularly important for complying with data privacy regulations
like GDPR and HIPAA, and for maintaining customer trust.
Here's why data encryption is essential:
• Protection of sensitive data:
Encryption protects personally identifiable information (PII), financial details, medical
records, and other sensitive data stored in databases.
• Compliance with regulations:
Many data privacy regulations mandate encryption to protect sensitive data. For example,
HIPAA requires encryption for protected health information.
• Prevention of data breaches:
Encryption acts as a crucial layer of defense against data breaches. If a database is
breached, encrypted data remains unreadable without the decryption key.
• Maintaining data integrity:
Encryption can also help ensure data integrity by making it difficult to modify or corrupt
data without detection.
• Building trust:
Demonstrating a commitment to encryption can build trust with customers and partners,
as it shows that a business takes data security seriously, according to Salesforce.
• Safeguarding data in transit:
Encryption protects data while it is being transmitted between systems (data in transit),
preventing eavesdropping and interception during transmission.
10. Explain Transparent Data Encryption (TDE) in Oracle with examples.
Answer:
Transparent Data Encryption (TDE) in Oracle Database is a security feature that encrypts
sensitive data at rest, meaning data stored on physical storage media like hard drives, tapes, or
backups. TDE ensures that even if the underlying data files are accessed directly (e.g.,
through operating system tools or theft of storage media), the sensitive data remains protected
and unreadable without the proper decryption keys.
How TDE Works:
TDE operates transparently to applications and users. When data is written to an encrypted
column or tablespace, TDE encrypts it before writing to disk. Conversely, when authorized
users or applications access the data, TDE automatically decrypts it as it is read into memory,
presenting it in clear text. This transparency means no application code changes are required
to utilize TDE.
Key Components:
• TDE Master Key:
This is the highest-level key in the TDE hierarchy. It is stored securely outside the
database in an Oracle Wallet (a file-based keystore), a Hardware Security Module (HSM),
or Oracle Key Vault (OKV). The master key is used to encrypt the table and tablespace
encryption keys.
• Table/Tablespace Encryption Keys:
These keys are used to encrypt the actual data within tables or tablespaces. They are
themselves encrypted by the master key and stored within the database dictionary.
TDE Modes:
• TDE Column Encryption: Encrypts specific sensitive columns within a table. This is
suitable when only a subset of data needs protection.
o Example: Encrypting a credit card number column in a CUSTOMERS table.
Code
 CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100),
credit_card_number VARCHAR2(16) ENCRYPT USING 'AES256'
);
• TDE Tablespace Encryption: Encrypts an entire tablespace, meaning all data and objects
within that tablespace are encrypted. This is useful for encrypting entire applications or
datasets.
o Example: Creating an encrypted tablespace and moving a table into it.
Code
CREATE TABLESPACE encrypted_ts DATAFILE 'encrypted_ts.dbf' SIZE
100M ENCRYPTION USING 'AES256';
ALTER TABLE customers MOVE TABLESPACE encrypted_ts;
Benefits of TDE:
• Data at Rest Protection:
Safeguards sensitive data even if the physical storage media is compromised.
• Regulatory Compliance:
Helps meet data protection regulations and industry standards (e.g., GDPR, HIPAA, PCI
DSS).
• Transparency:
No application changes are required, simplifying implementation.
• Separation of Duties:
Allows for separation of responsibilities between database administrators and security
administrators, enhancing overall security.

11. Compare encryption at rest vs encryption in transit.

Answer:
Encryption at rest and encryption in transit are two distinct methods of securing data, each
addressing different stages of data lifecycle. Encryption at rest protects data stored on
physical media like hard drives, SSDs, or cloud storage, while encryption in transit safeguards
data as it moves across networks.
Encryption at Rest:
• Purpose: Protects stored data from unauthorized access if a storage device is
compromised.
• How it works: Data is encrypted before being written to storage and remains encrypted
until accessed.
• Examples: Full disk encryption, database encryption, file system encryption.
• Benefits: Prevents data breaches if storage devices are stolen, lost, or improperly
disposed of.
• Key implementations: AES 256, RSA, Blowfish are common encryption algorithms.
Encryption in Transit:
• Purpose: Protects data during transmission between systems, preventing interception
during network communication.
• How it works: Data is encrypted before leaving the sending system and decrypted by the
receiving system.
• Examples: TLS/SSL, SFTP, HTTPS.
• Benefits: Prevents eavesdropping and data breaches during network transfers, such as
when accessing websites or transferring files.
• Key implementations: TLS/SSL, SFTP, SSH, and HTTPS are common protocols.
Key Differences Summarized:
Feature Encryption at Rest Encryption in Transit

Scope Data at rest (stored) Data in transit (moving)

 Purpose Protect stored data from physical Protect data during transmission
access

Protection Against unauthorized access to Against interception during network

storage devices communication

Examples Hard drive encryption, database TLS/SSL, HTTPS

encryption

12. How can Oracle secure sensitive information like passwords or account details?
Answer:
Oracle secures sensitive data like passwords and account details through a combination of
encryption, secure storage, and access control measures. This includes encrypting passwords
before storage, using strong hashing algorithms, implementing account lockout features, and
employing network encryption.
Here's a breakdown of Oracle's security measures:
1. Encryption:
• Password Encryption:
Oracle automatically encrypts passwords using AES (Advanced Encryption Standard)
before transmitting them across the network during authentication.
• Transparent Data Encryption (TDE):
TDE encrypts data at rest within the database, protecting it from unauthorized access even
if the storage media is compromised.
• Data Redaction:
Oracle Data Redaction can be used to mask sensitive data like credit card numbers or
social security numbers, either partially or completely, when displaying or accessing the
data.
2. Secure Storage:
• Hashing:
When a user creates or modifies a password, Oracle generates a cryptographic hash (or
digest) of the password and stores this hash instead of the plain text password.
• Secure External Password Store:
Using an Oracle wallet or a secure external password store to encrypt passwords before
storing them is recommended.
• Oracle Wallet:
This is a secure software container that stores authentication and signing credentials,
including passwords.
3. Access Control:
• Account Lockout:
Oracle provides an account lockout feature that locks a user's credentials after a certain
number of failed login attempts, preventing brute-force attacks.
• Network Encryption:
Oracle Advanced Security uses network encryption and SSL to protect data during
transmission.
• Strong Authentication:
Oracle recommends using strong authentication methods like Kerberos, token cards,
smart cards, or X.509 certificates for user authentication.
• User Profiles:
Database administrators can control password management policies through user profiles,
enabling control over password length, complexity, and other security parameters.
4. Other Security Practices:
• Patching:
 Keeping the Oracle database and related software up-to-date with the latest security
patches is crucial.
• Monitoring:
Enable auditing and monitor for suspicious activities, such as unauthorized access
attempts, role changes, or password resets.
• Regular Rotation:
Regularly rotate API keys, authentication tokens, and service account passwords to
minimize the risk of unauthorized access.

13. What are the drawbacks of relying only on encryption for data security?
Answer:
Encryption is a powerful safeguard, but it’s not a complete solution on its own. If
organizations rely only on encryption for data security, several weaknesses can remain.
Here’s a detailed breakdown:
1. Key Management Challenges
• Single point of failure: If encryption keys are lost, data becomes permanently
inaccessible.
• Key compromise risk: If keys are stolen or mismanaged, encrypted data can be
decrypted instantly.
• Operational complexity: Rotating keys and securely distributing them is difficult at
scale.
2. No Protection Against Authorized Misuse
• Encryption doesn’t stop insiders with valid access: A DBA or application user with
decryption privileges can still read or copy sensitive data.
• Application-layer attacks still succeed: SQL injection or compromised credentials
bypass encryption because the database decrypts data for “legitimate” queries.
3. Data in Use Is Still Vulnerable
• Encryption protects data at rest or in transit — but once data is decrypted in memory
for processing, it can be stolen.
• RAM scraping or memory attacks can capture data after decryption.
4. Performance Overhead
• Higher CPU usage: Encrypting/decrypting large volumes of data can slow down
performance.
• Latency for critical transactions: Real-time encryption can impact response times,
especially without hardware acceleration.
5. Limited Coverage
• Metadata often remains unencrypted: Table names, schemas, and access patterns may
still reveal sensitive information.
• Not a substitute for access control: Encryption doesn’t decide who should have access
— it just obscures the data until it’s unlocked.
6. False Sense of Security
• Organizations may skip other protections: Thinking “we’re encrypted” can lead to
weaker authentication, poor patching, or no auditing.
• Regulatory compliance is broader: Encryption alone doesn’t meet full requirements for
GDPR, HIPAA, etc., which also demand logging, monitoring, and user accountability.

14. Define data integrity. What are its types?

Answer:
Data integrity refers to the accuracy, consistency, and reliability of data throughout its
lifecycle. It ensures that data remains unaltered and uncorrupted from its original state, during
storage, processing, retrieval, and transmission. The two main types of data integrity
are physical integrity and logical integrity. Logical integrity is further broken down into four
sub-types: entity integrity, referential integrity, domain integrity, and user-defined integrity.
Here's a more detailed explanation:
1. Physical Data Integrity:
 • This type focuses on protecting the physical systems that store and process data.
• It involves measures like backups, fault-tolerant systems, and disaster recovery plans to
ensure data remains intact despite hardware failures or other technical issues.
2. Logical Data Integrity:
• This type ensures data is accurate, consistent, and valid within a database or system.
• It encompasses several aspects:
o Entity Integrity: Ensures that each record in a table is uniquely identifiable and
that no duplicate records or null values exist. This is often enforced using primary
keys.
o Referential Integrity: Ensures that relationships between tables are consistent,
preventing orphaned records (records referencing non-existent records). It uses
foreign keys to link tables and maintain relationships.
o Domain Integrity: Ensures that data values in a column fall within a defined
range or set of acceptable values. This prevents the storage of invalid or incorrect
data in specific fields.
o User-defined Integrity: Allows users to define specific constraints or rules to
further refine data validation and ensure it meets specific business requirements.

15. Explain entity integrity and referential integrity with examples.

Answer:
Entity integrity and referential integrity are crucial for maintaining data consistency and
accuracy in relational databases. Entity integrity ensures each record in a table is uniquely
identifiable through a primary key, which cannot contain null values. Referential integrity
ensures that relationships between tables are valid by requiring foreign keys to either match a
primary key value in another table or be null.
Entity Integrity:
• Definition:
Entity integrity is enforced through primary keys, which uniquely identify each record
(row) in a table.
• Requirements:
• Each table must have a primary key.
• The primary key cannot contain null values.
• Each value in the primary key must be unique.
• Example:
Consider an EMPLOYEE table with columns EmpID, Name,
and DepartmentID. EmpID is the primary key. Entity integrity ensures:
• Each employee has a unique EmpID (no duplicates).
• No employee is without an EmpID (no null EmpID values).
Referential Integrity:
• Definition:
Referential integrity ensures that relationships between tables are valid by maintaining the
correspondence between rows in related tables.
• Requirements:
A foreign key in one table must reference a valid primary key in another table or be
null.
• Example:
Consider two tables: ORDERS and CUSTOMERS. ORDERS has a CustomerID as a
foreign key, referencing the CustomerID (primary key) in the CUSTOMERS table.
• Referential integrity ensures that every CustomerID in the ORDERS table either
corresponds to an existing CustomerID in the CUSTOMERS table or is null.
• For instance, if an order has CustomerID = 123, there must be a customer record
with CustomerID = 123 in the CUSTOMERS table.
• If CustomerID in ORDERS is NULL, it means the order is not yet associated
with a specific customer.
• Violation Example:
 If you try to insert an order with CustomerID = 999 into the ORDERS table, and there's
no customer with CustomerID = 999 in the CUSTOMERS table, it violates referential
integrity.

16. How are constraints used to enforce data integrity in Oracle SQL?
Answer:
Constraints in Oracle SQL are rules defined on table columns to enforce data integrity,
ensuring the accuracy, consistency, and validity of data within the database. They prevent the
entry of invalid data and maintain relationships between tables.
Here's how different types of constraints achieve this:
• NOT NULL Constraint:
Ensures that a column cannot contain a NULL value, guaranteeing that essential data is
always present.
• UNIQUE Constraint:
Guarantees that all values in a column or a set of columns are unique, preventing
duplicate entries. Oracle automatically creates a unique index to enforce this.
• PRIMARY KEY Constraint:
A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table
and serves as the primary identifier for records. Oracle automatically creates a unique
index for primary keys.
• FOREIGN KEY Constraint:
Enforces referential integrity by establishing a link between two tables. It ensures that
values in a foreign key column in the child table must match existing values in the
primary or unique key of the parent table, preventing orphaned records.
• CHECK Constraint:
Defines a condition that must be true for every row in a table. This allows for custom
business rules to be enforced, such as ensuring a numeric value falls within a specific
range or a string adheres to a particular format.
Enforcement Mechanisms:
• Automatic Validation:
When a DML (Data Manipulation Language) statement like INSERT, UPDATE,
or DELETE is executed, Oracle automatically checks if the operation violates any enabled
constraints.
• Transaction Rollback:
If a DML operation attempts to violate an enabled constraint, Oracle rolls back the entire
statement and returns an error, preventing the invalid data from being committed to the
database.
• Constraint States:
Constraints can be ENABLED or DISABLED. When ENABLED, they actively enforce
data rules. When DISABLED, they are not enforced, allowing data that might violate the
constraint to be entered (though this is generally discouraged for live
data). The VALIDATE and NOVALIDATE options control whether existing data is
checked when a constraint is enabled.

17. Write SQL commands to implement NOT NULL, UNIQUE, and CHECK constraints.
Answer:
SQL commands can be used to implement NOT NULL, UNIQUE, and CHECK constraints
during table creation or by altering an existing table.
1. NOT NULL Constraint:
This constraint ensures that a column cannot contain NULL values. During table creation.
Code
CREATE TABLE Employees (
EmployeeID INT NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
 Age INT
);
Adding to an existing column.
Code
ALTER TABLE Employees
ALTER COLUMN Age INT NOT NULL;
2. UNIQUE Constraint:
This constraint ensures that all values in a column (or a set of columns) are distinct. During
table creation.
Code
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) UNIQUE,
SKU VARCHAR(20) UNIQUE
);
Adding to an existing column.
Code
ALTER TABLE Products
ADD CONSTRAINT UQ_ProductName UNIQUE (ProductName);
3. CHECK Constraint:
This constraint limits the value range that can be placed in a column. During table creation
(column-level).
Code
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Quantity INT CHECK (Quantity > 0)
);
During table creation (table-level).
Code
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Age INT,
CONSTRAINT CHK_Age CHECK (Age >= 18 AND Age <= 60)
);
Adding to an existing table.
Code
ALTER TABLE Orders
ADD CONSTRAINT CHK_Quantity CHECK (Quantity BETWEEN 1 AND 100);
18. Describe how foreign keys help maintain referential integrity.
Answer:
Foreign keys enforce referential integrity by ensuring that a foreign key value in one table
must exist as a primary key value in the related table. This prevents orphaned records (records
in the child table that refer to non-existent records in the parent table) and maintains
consistent relationships between tables.
Here's a more detailed explanation:
1. What are foreign keys?
A foreign key is a column or set of columns in one table that refers to the primary key (or a
unique key) of another table. This creates a link or relationship between the two tables.
2. What is referential integrity?
Referential integrity is a database concept that ensures the relationships between tables are
valid and consistent. It means that any value in a foreign key column must correspond to an
existing, valid record in the parent table.
3. How do foreign keys maintain referential integrity?
• Preventing orphaned records:
 When a foreign key is defined, the database system enforces a rule that prevents the
deletion of a record in the parent table if there are related records (with matching foreign
key values) in the child table.
• Ensuring valid references:
Foreign keys also prevent the insertion or updating of foreign key values in the child table
if those values don't exist as primary key values in the parent table. This ensures that
every reference in the child table is to a valid record in the parent table.
4. Example:
Imagine a database with two tables: Customers and Orders. The Orders table has
a customer_id column that is a foreign key referencing the customer_id column in
the Customers table.
• Referential integrity in action:
If a customer record is deleted from the Customers table, the database will either:
• Prevent the deletion if there are any orders associated with that customer (using
RESTRICT).
• Delete the associated orders from the Orders table (using CASCADE).
• Set the customer_id in the Orders table to NULL for those orders (using SET
NULL).
• Enforcing valid references:
When adding a new order, the database will ensure that the customer_id in
the Orders table matches an existing customer_id in the Customers table.
5. Benefits of referential integrity:
• Data accuracy:
Prevents inconsistencies and errors that can arise from orphaned records or invalid
references.
• Data reliability:
Ensures that data across different tables is consistent and trustworthy.
• Simplified data management:
Makes it easier to query and analyze data because relationships are clearly defined and
enforced.
• Improved data integrity:
Prevents the database from becoming corrupted due to invalid relationships.
19. Explain the difference between system privileges and object privileges.
Answer:
System Privileges
• Allow users to perform actions on the database (not specific objects).
• Examples:
o CREATE SESSION – Connect to the database.
o CREATE TABLE – Create new tables.
o CREATE USER – Create new users.
o DROP TABLE – Delete tables.
Object Privileges
• Allow users to perform actions on specific objects (tables, views, sequences,
procedures).
• Examples:
o SELECT – Read data from a table or view.
o INSERT, UPDATE, DELETE – Modify table data.
o EXECUTE – Run a stored procedure.
o REFERENCES – Create foreign keys referencing another table.

20. How can you grant and revoke permissions in SQL? Provide syntax and examples.
 Answer:
Granting Privileges
Use the GRANT statement to give privileges to users or roles.
Granting System Privileges
-- Granting system privileges
GRANT CREATE SESSION, CREATE TABLE TO peeyush;
-- Granting admin option (can pass the privilege to others)
GRANT CREATE USER TO admin_user WITH ADMIN OPTION;
Granting Object Privileges
-- Grant SELECT and INSERT on table to a user
GRANT SELECT, INSERT ON employees TO peeyush;
-- Allow another user to grant the same privileges further
GRANT UPDATE ON employees TO manager WITH GRANT OPTION;
Granting Privileges to Roles
Roles group privileges for easier management:
-- Create a role and grant privileges to it
CREATE ROLE hr_role;
GRANT CREATE SESSION, CREATE VIEW TO hr_role;
-- Grant the role to a user
GRANT hr_role TO peeyush;
Revoking Privileges
Use the REVOKE statement to remove privileges.
Revoking System Privileges
-- Remove CREATE TABLE privilege
REVOKE CREATE TABLE FROM peeyush;
Revoking Object Privileges
-- Remove SELECT privilege on a table
REVOKE SELECT ON employees FROM peeyush;
-- Remove a role from a user
REVOKE hr_role FROM peeyush;
21. What tools does Oracle provide to support database security and integrity?
Answer:
Oracle provides a layered set of tools and features that work together to ensure database
security (prevent unauthorized access) and data integrity (ensure accuracy and consistency
of data). These include command-line utilities, GUI tools, built-in database features, and
auditing mechanisms.
1. Administration Tools
Tool Role in Security and Integrity
Command-line tool to create users, grant/revoke privileges,
SQL*Plus
enforce constraints, and configure auditing.
Web-based GUI for centralized user management, privilege
Oracle Enterprise
assignment, security policy enforcement, and real-time
Manager (OEM)
monitoring.

2. User and Privilege Management

Feature Purpose
DBA commands
Assign or revoke system and object privileges.
(GRANT/REVOKE)
 Feature Purpose
Group privileges for easier administration (e.g.,
Roles
CONNECT, RESOURCE, DBA).
Enforce password policies, resource limits, and
Profiles
account lockout rules.
Virtual Private Database Row-level security using policies to control which
(VPD) rows users can access.

3. Data Integrity Tools

Feature Purpose
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT
Integrity constraints
NULL, CHECK constraints ensure valid data.
Enforce complex business rules automatically
Triggers
before/after data changes.
Fine-Grained Access
Restricts data visibility based on user attributes.
Control (FGAC)

4. Auditing and Monitoring

Tool / View Purpose
Tracks logins, privilege usage, and
Standard Auditing (AUDIT command)
DML/DDL operations.
Consolidates all audit records into a
Unified Auditing (newer versions)
single trail for easier management.
Audits access at column/row level
Fine-Grained Auditing (FGA)
with specific conditions.
Data Dictionary Views Allow DBAs to query and review
(DBA_AUDIT_TRAIL, DBA_USERS, user activity, privileges, and policy
DBA_ROLE_PRIVS) violations.
Oracle Enterprise Manager Security Provides dashboards and alerts for
Console suspicious activities.

5. Encryption and Data Protection

Feature Purpose
Transparent Data Encrypts data at rest automatically in tablespaces or
Encryption (TDE) columns.
Oracle Wallet / Key Vault Securely stores and manages encryption keys.
Prevents even privileged users (DBAs) from accessing
Database Vault
sensitive application data.
Adds classification labels (e.g., confidential, public) to
Label Security
rows to control access.

22. Describe the role of SQL*Plus and Oracle Enterprise Manager in maintaining integrity
and security.
Answer:
Both SQL*Plus and Oracle Enterprise Manager (OEM) are key DBA tools in Oracle
environments, but they serve different purposes and complement each other. Here’s a clear
explanation of how each contributes to maintaining integrity and security:
1. SQL*Plus – Command-line control for direct administration
 Role in Integrity and Security:
• Direct privilege management:
DBAs can use SQL*Plus to grant, revoke, or audit system and object privileges precisely
using SQL statements (e.g., GRANT CREATE SESSION TO user;).
• Data integrity enforcement:
SQL*Plus is used to define integrity constraints (PRIMARY KEY, FOREIGN KEY,
CHECK, UNIQUE) and to validate them (ALTER TABLE ... ENABLE CONSTRAINT;).
• Security configuration via scripts:
Password policies, account lockouts, and roles can be scripted and applied consistently.
• Auditing setup:
Auditing can be configured with AUDIT commands (e.g., AUDIT SELECT ON
employees BY ACCESS;) and verified through data dictionary views.
• Verification of changes:
SQL queries allow DBAs to inspect user privileges, roles, and system settings at a
granular level.
Key point: SQL*Plus provides precise, low-level, command-driven control — ideal for
scripting, automation, and direct security configuration.
2. Oracle Enterprise Manager (OEM) – GUI-based centralized administration
Role in Integrity and Security:
• Visual privilege and role management:
OEM lets DBAs assign/revoke privileges through an intuitive interface without writing
manual SQL.
• Database security policies:
Password complexity, expiration, and account lockout policies can be set graphically.
• Auditing and compliance monitoring:
OEM automatically collects and displays audit logs, user activity, and failed login
attempts in dashboards.
• Alerts and diagnostics:
Real-time alerts for suspicious activity, unauthorized changes, or policy violations help
maintain database integrity.
• User account management:
Administrators can create, lock, or drop users with a few clicks while OEM ensures
consistent enforcement of Oracle security best practices.
Key point: OEM provides centralized, visual monitoring and policy enforcement — ideal for
ongoing security oversight and compliance reporting.

23. How can a DBA use data dictionary views to monitor privileges and audit logs?
Answer:
A DBA in Oracle uses data dictionary views to check who has what privileges, verify role
assignments, and track audited actions. These views are prebuilt tables maintained by Oracle
that provide metadata about users, roles, privileges, and audit records. Here's how it’s
typically done:
1. Monitoring User and Role Privileges
Key Views for Privileges:
• DBA_USERS – Lists all database users and their default settings.
• DBA_ROLES – Shows all roles created in the database.
• DBA_SYS_PRIVS – Shows system privileges granted to users or roles (e.g.,
CREATE SESSION).
• DBA_TAB_PRIVS – Shows object-level privileges (SELECT, INSERT, UPDATE
on tables, views).
• DBA_ROLE_PRIVS – Shows which roles are granted to which users.
Example Queries:
-- Check what roles are granted to a specific user
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HR_SCHEMA';
-- Check direct system privileges for a user
 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR_SCHEMA';
-- Check object privileges (table-level)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HR_SCHEMA';
-- List all database users and their default tablespace
SELECT USERNAME, DEFAULT_TABLESPACE, ACCOUNT_STATUS FROM
DBA_USERS;
2. Monitoring Audit Logs
Oracle auditing records who did what and when. These records are available via data
dictionary views once auditing is enabled.
Key Views for Auditing:
• DBA_AUDIT_TRAIL – Shows general audit records (user actions, timestamp,
privileges used).
• DBA_AUDIT_SESSION – Shows user login/logoff activities.
• DBA_AUDIT_OBJECT – Shows actions on specific database objects (e.g., table
updates).
• DBA_AUDIT_STATEMENT – Shows DDL or other SQL statement executions.
• DBA_FGA_AUDIT_TRAIL – Shows fine-grained auditing results if FGA is configured.
Example Queries:
-- Show all audit logs (general)
SELECT USERNAME, ACTION_NAME, OBJ_NAME, TIMESTAMP
FROM DBA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;
-- Show user login/logoff activity
SELECT USERNAME, LOGON_TIME, LOGOFF_TIME
FROM DBA_AUDIT_SESSION
ORDER BY LOGON_TIME DESC;
-- Show audited actions on tables
SELECT USERNAME, ACTION_NAME, OBJ_NAME, TIMESTAMP
FROM DBA_AUDIT_OBJECT
WHERE OBJ_NAME = 'EMPLOYEES';
3. Practical DBA Workflow
Check who has powerful privileges:
SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE LIKE '%DBA%';
Verify unauthorized object access:
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER='HR_SCHEMA' AND
GRANTEE!='HR_SCHEMA';
Audit who altered sensitive tables:
SELECT USERNAME, ACTION_NAME, OBJ_NAME, TIMESTAMP
FROM DBA_AUDIT_OBJECT
WHERE OBJ_NAME='SALARY' AND ACTION_NAME='UPDATE';
Monitor failed logins (security):
SELECT USERNAME, RETURNCODE, TIMESTAMP
FROM DBA_AUDIT_SESSION
WHERE RETURNCODE != 0;

1. Privilege Monitoring Cheat Sheet

View Purpose Sample Query
Lists all database
SELECT USERNAME,
users, default
DBA_USERS ACCOUNT_STATUS FROM
tablespaces,
DBA_USERS;
status
Lists all database SELECT ROLE FROM
DBA_ROLES
roles DBA_ROLES;
View Purpose Sample Query
SELECT * FROM
Shows roles
DBA_ROLE_PRIVS DBA_ROLE_PRIVS WHERE
granted to users
GRANTEE='HR_SCHEMA';
Shows system-
SELECT * FROM
level privileges
DBA_SYS_PRIVS DBA_SYS_PRIVS WHERE
(e.g., CREATE
GRANTEE='HR_SCHEMA';
SESSION)
Shows object-
SELECT * FROM
level privileges
DBA_TAB_PRIVS DBA_TAB_PRIVS WHERE
(SELECT,
GRANTEE='HR_SCHEMA';
INSERT, etc.)

2. Audit Log Monitoring Cheat Sheet

View Purpose Sample Query
SELECT USERNAME,
General ACTION_NAME,
audit trail OBJ_NAME, TIMESTAMP
DBA_AUDIT_TRAIL
for all user FROM DBA_AUDIT_TRAIL
activities ORDER BY TIMESTAMP
DESC;
SELECT USERNAME,
LOGON_TIME,
Login/logoff LOGOFF_TIME FROM
DBA_AUDIT_SESSION
activities DBA_AUDIT_SESSION
ORDER BY LOGON_TIME
DESC;
SELECT USERNAME,
ACTION_NAME,
DML/DDL
OBJ_NAME, TIMESTAMP
activity on
DBA_AUDIT_OBJECT FROM
specific
DBA_AUDIT_OBJECT
objects
WHERE
OBJ_NAME='EMPLOYEES';
SELECT USERNAME,
Audit SQL
ACTION_NAME,
DBA_AUDIT_STATEMENT statements
TIMESTAMP FROM
run by users
DBA_AUDIT_STATEMENT;
Fine-
Grained SELECT POLICY_NAME,
Audit OBJECT_NAME, SQL_TEXT
DBA_FGA_AUDIT_TRAIL
(column- FROM
level DBA_FGA_AUDIT_TRAIL;
conditions)

3. Common DBA Security Checks

Goal Query
Find who has DBA SELECT * FROM DBA_SYS_PRIVS WHERE
privileges PRIVILEGE='DBA';
 Goal Query
Verify
unauthorized SELECT * FROM DBA_TAB_PRIVS WHERE
access to HR OWNER='HR_SCHEMA' AND GRANTEE!='HR_SCHEMA';
tables
SELECT USERNAME, ACTION_NAME, OBJ_NAME,
See who updated
TIMESTAMP FROM DBA_AUDIT_OBJECT WHERE
sensitive tables
OBJ_NAME='SALARY' AND ACTION_NAME='UPDATE';
Monitor failed SELECT USERNAME, RETURNCODE, TIMESTAMP FROM
login attempts DBA_AUDIT_SESSION WHERE RETURNCODE!=0;

24. Discuss the importance of role-based access control and how it's implemented in SQL.
Answer:
Role-Based Access Control Definition
Role-based access control (RBAC) is a method of protecting sensitive data from improper
access, modification, addition, or deletion. It allows employees to have access to the
information required to fulfill their responsibilities. Access rights and permissions are given to
employees based on their job roles and designations. This helps protect business-critical data
against misuse.
Basics of role-based access control
Three common principles of role-based access control include:
1. User role assignment: The permission or access rights are granted only if the individual
is assigned a role or a task.
2. User role authorization: The active role of the user in the task must be authorized.
3. User role permission and access rights: The individual can utilize their permission
rights only if they’re given the authorization to perform their active role.
Effective RBAC can help with granting systematic permissions and access to business-critical
information to improve cybersecurity and with maintaining compliance with regulations such
as HIPAA, GDPR, and more. Using an automated RBAC software solution can also provide
structured templates to monitor user access levels and simplify the audit process.
How does RBAC work?
An access rights system leveraging RBAC works on the principle of least privilege to help
ensure sensitive data security. Using RBAC can be extremely helpful for larger enterprises
with a great number of employees where administrators are facing difficulty in assigning
unique user credentials to each employee. With automated role-based access control,
administrators can create user groups with similar permissions and rights, assign roles and
responsibilities, and allow access to a defined set of resources.
Benefits of RBAC
Limiting unnecessary employee access to business-critical information can help ensure
security and compliance by:
o Improving operational efficiency: Role-based access control can help reduce manual
tasks and paperwork by streamlining the automation of access rights. With an RBAC
software solution, enterprises can more easily assign, modify, add, and delete roles and
responsibilities to enhance operational efficiency.
o Demonstrating compliance: Implementing RBAC will help organizations demonstrate
compliance with local, federal, and state regulations. This enables IT teams and
administrators to manage access to confidential data more effectively. Financial and
healthcare institutions can use RBAC to manage access to critical data, such as PCI and
PHI.
Best practices for implementing RBAC
By following a few best practices, implementing role-based access control can be a simple
process. Some best practices to help support RBAC include:
 o Note the current user permissions assigned to resources. It’s important to have detailed
information and be able to easily view user access to applications and resources, such as
software and hardware.
o Use role-specific templates to assign access rights to only users who need them based
on job responsibilities and standardize user credentials.
o Track any modifications or changes made in user roles, access rights, and
permissions to identify and investigate privilege abuses, suspicious account activity, and
other vulnerabilities.
Role-based access control in Azure AD
Azure Active Directory provides two types of role-based access controls:
1. Built-in roles: Azure AD supports many built-in roles. However, each role includes a
fixed set of permissions that cannot be modified.
2. Custom roles: Azure AD also supports custom roles, including a collection of
permissions that can be modified depending on the role. Granting permissions using
custom roles is a two-step process. It involves creating a custom Azure AD role and
assigning the permissions from a preset list. A custom role can be assigned at an
organization level or object scope level. The member with custom permission rights can
have access to all the organization’s resources, while object-scope permissions are limited
to a single application.
RBAC vs. ABAC
Role-based access control and attribute-based access control (ABAC) are two different
methods of access control. The major difference between the two is RBAC grants access
based on the user roles and responsibilities while ABAC provides access based on
environmental attributes, resource attributes, user attributes, and other attributes.
25. What is Curser? Explain indetail and its types.
Answer:
In Oracle PL/SQL, a cursor is a pointer or handle used to fetch and process rows returned by a
SQL query, one at a time. Because SQL statements can return multiple rows but PL/SQL
variables can hold only one value at a time, cursors act as a bridge between multi-row query
results and PL/SQL’s row-by-row processing.
Why Use Cursors?
To process query results row by row.
To perform complex business logic on each row.
To improve clarity and control compared to implicit fetching.
Types of Cursors in PL/SQL
Implicit Cursor
Automatically created by Oracle when you execute a single SQL statement (e.g., INSERT,
UPDATE, DELETE, SELECT INTO) inside PL/SQL.
Oracle manages the opening, fetching, and closing.
The status of the last implicit cursor can be checked using the SQL Cursor Attributes:
SQL%FOUND – TRUE if rows were affected.
SQL%NOTFOUND – TRUE if no rows were affected.
SQL%ROWCOUNT – Number of rows affected.
SQL%ISOPEN – Always FALSE for implicit cursors (they close automatically).
Example:
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows updated.');
END IF;
END;
Explicit Cursor
Declared and managed manually by the programmer.
Useful when:
The query returns multiple rows.
You need step-by-step control over fetching and processing.
Steps to Use Explicit Cursor
Declare the cursor.
Open the cursor.
Fetch rows into variables.
Close the cursor.
Example:
DECLARE
CURSOR emp_cursor IS
SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = 20;
v_id employees.emp_id%TYPE;
v_name employees.emp_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor; -- Step 2: Open
LOOP
FETCH emp_cursor INTO v_id, v_name, v_salary; -- Step 3: Fetch
EXIT WHEN emp_cursor%NOTFOUND; -- Exit when no more rows
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name || ' - ' || v_salary);
END LOOP;
CLOSE emp_cursor; -- Step 4: Close
END;
Cursor FOR Loop
Simplifies explicit cursor handling by automatically opening, fetching, and closing the cursor.
Requires fewer lines of code.
Example:
BEGIN
FOR emp_rec IN (SELECT emp_id, emp_name, salary FROM employees WHERE dept_id
= 20) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_id || ' - ' || emp_rec.emp_name || ' - ' ||
emp_rec.salary);
END LOOP;
END;
Parameterized Cursor
Accepts parameters at runtime, making cursors more flexible.
Example:
DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT emp_id, emp_name FROM employees WHERE dept_id = p_dept_id;
BEGIN
FOR emp_rec IN emp_cursor(30) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_id || ' - ' || emp_rec.emp_name);
END LOOP;
END;
Cursor Attributes
Attribute Description
%FOUND TRUE if the last fetch returned a row.
%NOTFOUND TRUE if the last fetch did NOT return a row.
%ROWCOUNT Number of rows fetched so far.
 Attribute Description
%ISOPEN TRUE if the cursor is open.

Ref Cursors (Cursor Variables)

A REF CURSOR is a pointer to a cursor, useful for passing query results between PL/SQL
blocks or procedures.
Example:
DECLARE
TYPE emp_ref_cursor IS REF CURSOR;
c_emp emp_ref_cursor;
v_id employees.emp_id%TYPE;
v_name employees.emp_name%TYPE;
BEGIN
OPEN c_emp FOR SELECT emp_id, emp_name FROM employees WHERE dept_id = 10;
LOOP
FETCH c_emp INTO v_id, v_name;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE c_emp;
END;
Best Practices
Always close explicit cursors after use to free resources.
Prefer cursor FOR loops for simplicity unless you need fine-grained control.
Use parameterized cursors or REF CURSORS for reusable and flexible logic.
Monitor %ROWCOUNT for performance checks and debugging.