1. Can you please
tell me about yourself and your experience and all?
I have been working
as a PL/SQL Developer and ODI Specialist for the past 4 years. My experience
includes developing and maintaining complex PL/SQL procedures, functions, and
packages, as well as designing and implementing ETL processes using Oracle Data
Integrator (ODI). I've worked on various projects that involve data migration,
integration, and transformation, where I have utilized my skills in SQL,
PL/SQL, and ODI to ensure data accuracy and efficiency. Additionally, I have
been involved in performance tuning and optimization to enhance the speed and
efficiency of data processing.
2. In which project
are you working? Is it based on ETL, ODI, or PL/SQL?
Currently, I am
working on a project focused on data integration for a large retail client. The
project involves ETL processes using ODI to integrate data from various sources
into a central data warehouse. My role includes developing ODI mappings,
creating PL/SQL procedures for data transformation, and ensuring that the data
integration processes are robust and efficient.
3. Can you please
explain the difference between ETL and ELT with an example?
ETL (Extract,
Transform, Load) and ELT (Extract, Load, Transform) are two data processing
approaches:
- ETL: Data is
extracted from the source, transformed in a staging area, and then loaded into
the target data warehouse. For example, if we extract sales data from different
systems, transform it (e.g., converting date formats, aggregating data), and
then load it into a data warehouse, that's ETL.
- ELT: Data is
extracted and loaded into the target data warehouse first, and then transformed
within the data warehouse. For instance, if we load raw sales data directly
into a data warehouse and perform transformations such as cleaning and aggregation
using SQL queries or data processing tools in the warehouse, that's ELT.
4. Can you rate
yourself in SQL and PL/SQL out of 10?
I would rate myself
8/10 in SQL and PL/SQL. I have extensive experience in writing complex queries,
optimizing performance, and developing PL/SQL procedures, functions, and
packages. I continuously seek to improve my skills and stay updated with best
practices.
5. Do you have any
experience in DBA-related tools?
Yes, I have
experience working with Oracle Enterprise Manager for database monitoring and
management. I am familiar with tasks such as database backup and recovery,
performance tuning, and user management. Additionally, I have used tools like
SQL*Plus and Toad for database development and administration.
6. Can you please
explain Oracle Architecture?
Oracle Database
architecture consists of the following main components:
- Instance:
Consists of the Oracle background processes and memory structures (SGA - System
Global Area and PGA - Program Global Area) that manage database operations.
- Database: The
physical files that store data, including datafiles, redo log files, and
control files.
- Tablespaces:
Logical storage units that group datafiles and manage data storage.
- Datafiles:
Physical files on disk that store database data.
- Redo Log Files:
Files that record all changes made to the database to ensure data recovery.
- Control Files:
Files that store metadata about the database structure and state.
7. How does the
UPDATE command work internally?
The UPDATE command
modifies existing records in a table. Internally, Oracle performs the following
steps:
- Row
Identification: Locates the rows to be updated based on the WHERE clause.
- Locking: Places a
lock on the identified rows to prevent concurrent modifications.
- Modification:
Updates the row data with the new values specified.
- Logging: Records
the changes in redo log files to ensure recoverability.
- Commit: If the
transaction is committed, changes are made permanent; otherwise, they are
rolled back.
8. Do you have
procedure tuning experience?
Yes, I have
experience in tuning PL/SQL procedures. This involves analyzing execution
plans, optimizing SQL queries within procedures, using appropriate indexes, and
minimizing context switches between SQL and PL/SQL. I also use tools like
Oracle’s SQL Trace and TKPROF to identify and resolve performance bottlenecks.
9. What is the main
use of Synonyms? Have you created Synonyms in your previous projects?
Synonyms provide a
way to create an alias for database objects, making it easier to reference
objects without specifying the schema name. They are useful for simplifying SQL
statements and managing object references across different schemas. Yes, I have
created synonyms in previous projects to simplify access to frequently used
tables and views and to support cross-schema access.
10. We have two
tables with a parent and child relationship. Is it possible to remove any
records from the parent table if we have records in the child table for the
parent key?
No, it is not
possible to delete records from the parent table if there are dependent records
in the child table unless you have defined a cascading delete rule on the
foreign key constraint. Without cascading delete, Oracle will enforce
referential integrity and prevent the deletion to avoid orphaned records.
11. What is the error
if we delete data from the parent table?
If you attempt to
delete a record from the parent table while there are related records in the
child table, you will encounter a "ORA-02292: integrity constraint
(constraint_name) violated - child record found" error. This error
indicates that the delete operation is being blocked due to the presence of
dependent child records.
12. Have you worked
on SQL Loader and XML table `ult_file` package?
Yes, I have
experience with SQL Loader for loading data from flat files into Oracle tables.
SQL Loader allows for efficient bulk data loading with various options for data
transformation and error handling. I have also worked with XML data using the
`ult_file` package to handle XML files and integrate them into the database.
13. Let’s consider a
scenario. If there are 6 interfaces running sequentially and one of the interfaces
failed (2nd or 3rd) run, how will you rectify this error?
To rectify the
error, I would:
- Review Logs:
Check the logs of the failed interface to identify the root cause of the
failure.
- Fix Issue:
Address the specific issue (e.g., data errors, connection issues, etc.) that
caused the failure.
- Re-run Failed
Interface: Manually or automatically re-run the failed interface after
resolving the issue.
- Validate: Ensure
that the interface completed successfully and validate the data.
- Monitor: Monitor
subsequent interfaces to ensure that they run without issues.
14. What are the
different types of DB objects you have created in your project?
In my projects, I
have created various database objects including:
- Tables: For
storing structured data.
- Views: For
simplifying complex queries and restricting access.
- Indexes: For
improving query performance.
- Sequences: For
generating unique identifiers.
- Synonyms: For
aliasing database objects.
- Procedures: For
encapsulating business logic.
- Functions: For
reusable code that returns a value.
- Packages: For
grouping related procedures and functions.
- Triggers: For
automatically performing actions in response to changes in the database.
15. What is the
difference between a procedure, function, and package?
- Procedure: A
stored PL/SQL block that performs an action but does not return a value. It can
modify database state and accept parameters.
- Function: A
stored PL/SQL block that returns a value. It is typically used for computations
and can be used in SQL statements.
- Package: A
collection of related procedures, functions, variables, and cursors grouped
together. Packages help organize code and provide encapsulation and modularity.
16. Is it possible to
use SYSDATE in CHECK constraints?
No, it is not
possible to use SYSDATE in CHECK constraints. CHECK constraints evaluate
conditions on row values, but SYSDATE is a dynamic value that changes with each
query execution, which makes it unsuitable for static constraints.
17. Updating the same
record by two users, what will happen?
When two users
attempt to update the same record simultaneously, Oracle handles this with
row-level locking. One user will acquire a lock on the record and the other
will have to wait until the first transaction is committed or rolled back. If
the first user commits their changes, the second user’s update will be applied.
If the first user rolls back, the second user's update will proceed without
conflicts.
18. Did you work on
the creation of jobs?
Yes, I have worked
on creating and managing jobs using Oracle's DBMS_SCHEDULER package and
DBMS_JOB package. I have scheduled various tasks such as data loads, backups,
and maintenance procedures to run at specific intervals or based on specific
conditions.
19. What is
polymorphism in Oracle PL/SQL?
Polymorphism in
PL/SQL refers to the ability to define multiple procedures or functions with
the same name but different parameter lists (overloading). It allows for more
flexible and modular code. For example, you can have a procedure `calculate`
that handles different types of calculations based on the number and type of
parameters passed.
Here are the answers to the additional interview questions:
20. What are the
different types of collection methods you have used in your previous projects?
In my previous
projects, I have used the following types of PL/SQL collections:
- Associative
Arrays (Index-by Tables): These are useful when you need to use a unique key
(like a string) to access elements. They are often used for lookups or to hold
data where the key is not a sequential number.
- Nested Tables:
These are useful when you need to store a set of elements of the same type and
you want to be able to dynamically resize the collection. They are often used
in situations where the number of elements is variable.
- VARRAYs
(Variable-size Arrays): These are used when you need a fixed-size collection
that can grow up to a specified limit. They are less flexible compared to
nested tables but useful when you know the maximum number of elements
beforehand.
21. What is the
difference between static cursor and dynamic cursor?
- Static Cursor: A
static cursor is associated with a result set that is fixed when the cursor is
opened. It does not reflect changes made to the underlying data after the
cursor is opened. Static cursors are generally used when you want a consistent
view of the data, regardless of subsequent changes. In PL/SQL, a static cursor
is defined with a `REF CURSOR` that is not intended to be updated dynamically.
- Dynamic Cursor:
A dynamic cursor reflects changes made to the data in the database while the
cursor is open. If the underlying data changes (e.g., new rows are inserted, or
existing rows are updated or deleted), the dynamic cursor will see these
changes. This type of cursor is useful for queries where you need to see
real-time updates to the data.
22. We have two
databases DEV and SYS, and the same table exists in both databases. We want to
load the data dynamically, so whenever you pass the table name it should load
to the DEV table.
You can achieve
this using a combination of dynamic SQL and database links. Here’s an example
approach:
- Create a
Database Link: Set up a database link in DEV to connect to the SYS database.
```sql
CREATE DATABASE
LINK sys_link
CONNECT TO sys
IDENTIFIED BY password
USING 'sys_db';
```
- Use Dynamic SQL:
Write a PL/SQL procedure that dynamically constructs and executes an SQL
statement to load data from SYS to DEV.
```sql
CREATE OR
REPLACE PROCEDURE LoadData(p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'INSERT
INTO dev.' || p_table_name ||
' SELECT
* FROM ' || p_table_name || '@sys_link';
END;
```
In this
procedure, `p_table_name` is the name of the table you want to load, and
`@sys_link` specifies the database link to SYS.
23. If we have Procedure B in Procedure A, and Procedure B
is calling Procedure C. If we get an error, how to find out that error? Is
there any methodology we have?
To find out the
error when Procedure B calls Procedure C within Procedure A, follow these
steps:
- Exception
Handling: Implement exception handling in Procedure A, Procedure B, and
Procedure C to capture and log errors.
```sql
BEGIN
-- Call
Procedure B
Procedure_B;
EXCEPTION
WHEN OTHERS
THEN
-- Log
the error
DBMS_OUTPUT.PUT_LINE('Error in Procedure A: ' || SQLERRM);
RAISE;
-- Optionally re-raise the error
END;
```
- Error Logging:
Use error logging tables or packages to capture error details. For example,
create a table to store error information and insert error details into this
table from the exception handling sections.
- Stack Trace: In
PL/SQL, you can use `DBMS_UTILITY.FORMAT_ERROR_BACKTRACE` to get a stack trace
of where the error occurred.
```sql
BEGIN
-- Call
Procedure B
Procedure_B;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('Backtrace: ' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
```
24. Do you have
exposure to Materialized Views?
Yes, I have worked
with Materialized Views. Materialized Views are used to improve query
performance by precomputing and storing the results of a query. They are
particularly useful for reporting and data warehousing scenarios. I have
created and managed Materialized Views to handle large volumes of data and
refresh them periodically or on-demand to ensure data accuracy.
25. We have one
procedure where we have called another procedure. If we change the name of the
calling procedure, will the main procedure still work?
No, if you change
the name of the calling procedure, the main procedure will not work unless you
also update the references to the calling procedure. Procedure names must be
consistent across all references, so you need to update all invocations of the
procedure to reflect the new name.
26. We have one
procedure where after BEGIN we have a DELETE command, then an UPDATE command,
then we called another procedure and then a ROLLBACK command. Will it work or
not?
Yes, the ROLLBACK
command will undo all changes made in the transaction, including the DELETE and
UPDATE operations. When you issue a ROLLBACK, any changes made to the database
during the transaction (including calls to other procedures that perform data
modifications) are reverted. The state of the database will be as it was before
the BEGIN statement.
27. What is the use
of PRAGMA SERIALLY_REUSABLE?
`PRAGMA
SERIALLY_REUSABLE` is a compiler directive in PL/SQL used to mark a package as
"serially reusable." This means the package is designed to be
instantiated once per session and then reused multiple times, which can help
improve performance. It signals that the package does not maintain
session-specific state and is optimized for cases where the package is
instantiated and used multiple times within a single session.
28. Suppose I want to
stop the transaction during the month-end. How to do that?
To stop a
transaction during month-end or any specific period, you can:
- Use Application
Logic: Implement application-level logic to check for the month-end condition
and halt transactions or certain operations if the condition is met.
- Database
Triggers: Implement database triggers to prevent data modifications during
specific periods, such as the end of the month. For example, a BEFORE INSERT or
BEFORE UPDATE trigger can be used to check the current date and raise an
exception if it falls within the restricted period.
- Scheduled Jobs:
Schedule database jobs to check for transactions or halt certain operations
during the month-end. For example, you can use Oracle Scheduler to manage tasks
that should be stopped or modified at month-end.
- Manual
Intervention: If automatic methods are not feasible, manual intervention by
database administrators or application support teams can also be used to stop
transactions during critical periods.
