Oracle apps PLSQL interview questions

https://mindmajix.com/oracle-pl-sql-interview-questions
If you're looking for Oracle PL SQL Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research Oracle PL SQL has a market share of about 2.2%. So, You still have opportunity to move ahead in your career in Oracle PL SQL Development. Mindmajix offers Advanced Oracle PL SQL Interview Questions 2019 that helps you in cracking your interview & acquire dream career as Oracle PL SQL Developer.

Oracle PL SQL Interview Questions

PL Sql vs Sql
ComparisonSQLPL/SQL
ExecutionSingle command at a timeBlock of code
ApplicationSource of data to be displayedApplication created by data aquired by SQL
Structures includeDDL and DML based queries and commandsIncludes procedures, functions, etc
Recommended whilePerforming CRUD operations on dataCreating applications to display data obtained using sql
Compatibility with each otherSQL can be embedded into PL/SQLPL/SQL cant be embedded in SQL
Q. What is SQL and also describe types of SQL statements?
SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.
There are 5 different types of SQL statements.
  1. Data Retrieval: SELECT
  2. Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE
  3. Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.
  4. Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT
  5. Data Control Language (DCL): GRANT, REVOKE
Q. What is an alias in SQL statements?
Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.
For ex: Select empname AS name from employee; (Here AS is a keyword and “name” is an alias).
Q. What is a Literal? Give an example where it can be used?
A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.
For ex: Select last_name||’is a’||job_id As “emp details” from employee; (Here “is a” is a literal).
Learn Oracle PL SQL Training: Become experts in components of Oracle PL/SQL, Oracle database environment | Take Up Now Course at Mindmajix. Enroll & Become Certified
Q. What is a difference between SQL and iSQL*Plus?
SQL Vs iSQL*Plus
SQL
iSQL*Plus
Is a Language
Is an Environment
Character and date columns heading are left-justified and number column headings are right-justified.
Default heading justification is in Centre.
Cannot be Abbreviated (short forms)
Can be Abbreviated
Does not have a continuation character
Has a dash (-) as a continuation character if the command is longer than one line
Use Functions to perform some formatting
Use commands to format data
Q. Define the order of Precedence used in executing SQL statements.
Order of Precedence used in executing SQL statements
Order Evaluated
Operator
1
Arithmetic operators (*, /, +, -)
2
Concatenation operators (||)
3
Comparison conditions
4
Is[NOT] NULL, LIKE, [NOT] IN
5
[NOT] BETWEEN
6
NOT Logical condition
7
AND logical condition
8
OR logical condition
Q. What are SQL functions? Describe in brief different types of SQL functions?
SQL Functions are very powerful feature of SQL. SQL functions can take arguments but always return some value.
There are two distinct types of SQL functions:
1) Single-Row functions: These functions operate on a single row to give one result per row.
Types of Single-Row functions:
  1. Character
  2. Number
  3. Date
  4. Conversion
  5. General
2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.
Types of Multiple-Row functions:
  1. AVG
  2. COUNT
  3. MAX
  4. MIN
  5. SUM
  6. STDDEV
  7. VARIANCE
                                         Frequently Asked SQL Interview Questions & Answers
Q. Explain character, number and date function in detail?
Character functions: accept character input and return both character and number values. Types of character function are:
a) Case-Manipulation Functions: LOWER, UPPER, INITCAP
b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE
Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD
Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC. 
Q. What is a Dual Table?
Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.
Q. Explain Conversion function in detail?
Conversion Functions converts a value from one data type to another. Conversion functions are of two types:
Implicit Data type conversion:
  1. VARCHAR2 or CHAR To NUMBER, DATE
  2. NUMBER To VARCHAR2
  3. DATE To VARCHAR2
Explicit data type conversion:
  1. TO_NUMBER
  2. TO_CHAR
  3. TO_DATE
TO_NUMBER function is used to convert Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of TO_NUMBER function.
TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function use fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date, ‘format_model’).Format model must be enclosed in single quotation marks and is case sensitive.
For ex: Select TO_CHAR (hiredate, ‘MM/YY’) from employee.
TO_DATE function is used to convert Character string to date format. TO_DATE function use fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE function. TO_DATE function format: TO_DATE ( char[, ‘ format_model’] ).
For ex: Select TO_DATE (‘may 24 2007’,’mon dd rr’) from dual;
Q. Describe different types of General Function used in SQL?
General functions are of following types:
  1. NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
  2. NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
  3. NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
  4. COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
  5. Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function. 
Q. What is difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)
COUNT (*): Returns number of rows in a table including duplicates rows and rows containing null values in any of the columns.
COUNT (EXP): Returns the number of non-null values in the column identified by expression.
COUNT (DISTINCT EXP): Returns the number of unique, non-null values in the column identified by expression.
Q. What is a Sub Query? Describe its Types?
A sub query is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.
Guidelines for using sub queries:
  1. Enclose sub queries within parenthesis
  2. Place sub queries on the right side of the comparison condition.
  3. Use Single-row operators with single-row sub queries and Multiple-row operators with multiple-row sub queries.
Types of sub queries:
  1. Single-Row Sub query: Queries that return only one row from the Inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
  2. Multiple-Row Sub query: Queries that return more than one row from the inner Select statement. There are also multiple-column sub queries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.
Q. What is difference between ANY and ALL operators?
ANY Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.
> ANY means more than the minimum.
< ANY means less than the maximum
= ANY is equivalent to IN operator.
ALL Operator compares value to every value returned by the subquery.
> ALL means more than the maximum
< ALL means less than the minimum
<> ALL is equivalent to NOT IN condition. 
Q. What is a MERGE statement?
The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications. 
Q. What is a difference between “VERIFY” and “FEEDBACK” command?
VERIFY Command: Use VERIFY Command to confirm the changes in the SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.
Feedback Command: Displays the number of records returned by a query.
Q. What is the use of Double Ampersand (&&) in SQL Queries? Give example?
Use “&&” if you want to reuse the variable value without prompting the user each time.
For ex: Select empno, ename, &&column_name from employee order by &column_name;
Q. What are Joins and how many types of Joins are there?
Joins are used to retrieve data from more than one table.
There are 5 different types of joins.
types of Joins
Oracle 8i and Prior
SQL: 1999 (9i)
Equi Join
Natural/Inner Join
Outer Join
Left Outer/ Right Outer/ Full Outer Join
Self Join 
Join ON
Non-Equi Join
Join USING
Cartesian Product
Cross Join
Q. Explain all Joins used in Oracle 8i?
?Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. For ex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.
Non-Equi Joins: A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=).The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.
Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.
Self Join: Joining a table to itself.
Q. Explain all Joins used in Oracle 9i and later release?
Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.
Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, than the Natural Join syntax causes an error.
Join with the USING clause: If several columns have the same names but the data types do not match, than the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.
For ex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;
Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.
Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause.
Q. What is a difference between Entity, Attribute and Tuple?
Entity: A significant thing about which some information is required. For ex: EMPLOYEE (table). Attribute: Something that describes the entity. For ex: empno, empname, empaddress (columns). Tuple: A row in a relation is called Tuple.
Q. What is a Transaction? Describe common errors can occur while executing any Transaction?
Transaction consists of a collection of DML statements that forms a logical unit of work.
The common errors that can occur while executing any transaction are:
The violation of constraints.
  1. Data type mismatch.
  2. Value too wide to fit in column.
  3. The system crashes or Server gets down.
  4. The session Killed.
  5. Locking take place. Etc.
Q. What is locking in SQL? Describe its types?
Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are: 
Implicit Locking: Occurs for all SQL statements except SELECT.
Explicit Locking: Can be done by user manually.
Further there are two locking methods:
  1. Exclusive: Locks out other users
  2. Share: Allows other users to access
Q. What is a difference between Commit, Rollback and Savepoint?
  • COMMIT: Ends the current transaction by making all pending data changes permanent.
  • ROLLBACK: Ends the current transaction by discarding all pending data changes.
  • SAVEPOINT: Divides a transaction into smaller parts. You can rollback the transaction till a particular named savepoint.
Q. What are the advantages of COMMIT and ROLLBACK statements?
Advantages of COMMIT and ROLLBACK statements are:
  • Ensure data consistency
  • Can preview data changes before making changes permanent.
  • Group logically related operations.
Q. Describe naming rules for creating a Table?
Naming rules to be consider for creating a table are:
  1. Table name must begin with a letter,
  2. Table name can be 1-30 characters long,
  3. Table name can contain only A-Z, a-z, 0-9,_, $, #.
  4. Table name cannot duplicate the name of another object owned by the same user.
  5. Table name cannot be an oracle server reserved word.
Q. What is a DEFAULT option in a table?
A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.
Q. What is a difference between USER TABLES and DATA DICTIONARY?
USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.
Q. Describe few Data Types used in SQL?
Data Types is a specific storage format used to store column values. Few data types used in SQL are:
  1. VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’
  2. CHAR(size): Minimum size is ‘1’and Maximum size is ‘2000’
  3. NUMBER(P,S): " Precision" can range from 1 to 38 and the “Scale” can range from -84 to 127.
  4. DATE
  5. LONG: 2GB
  6. CLOB: 4GB
  7. RAW (size): Maximum size is 2000
  8. LONG RAW: 2GB
  9. BLOB: 4GB
  10. BFILE: 4GB
  11. ROWID: A 64 base number system representing the unique address of a row in the table.
Q. In what scenario you can modify a column in a table?
During modifying a column:
  1. You can increase the width or precision of a numeric column.
  2. You can increase the width of numeric or character columns.
  3. You can decrease the width of a column only if the column contains null values or if the table has no rows.
  4. You can change the data type only if the column contains null values.
  5. You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size. 
Q. Describe few restrictions on using “LONG” data type?
A LONG column is not copied when a table is created using a sub query. A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table. No constraint can be defined on a LONG column.
Q. What is a SET UNUSED option?
SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.
Q. What is a difference between Truncate and Delete?
The main difference between Truncate and Delete is as below:
SQL Truncate Vs SQL Delete
TRUNCATE
DELETE
Removes all rows from a table and releases storage space used by that table.
Removes all rows from a table but does not release storage space used by that table.
TRUNCATE Command is faster. 
DELETE command is slower.
Is a DDL statement and cannot be Rollback.
Is a DDL statement and can be Rollback.
Database Triggers do not fire on TRUNCATE.
Database Triggers fire on DELETE.
Q. What is a main difference between CHAR and VARCHAR2?
CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.
Q. What are Constraints? How many types of constraints are there?
Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at column level. There are 5 types of constraints:
  1. Not Null Constraint
  2. Unique Key Constraint
  3. Primary Key Constraint
  4. Foreign Key Constraint
  5. Check Key Constraint.
Q. Describe types of Constraints in brief?
NOT NULL: NOT NULL Constraint ensures that the column contains no null values.
UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates index on the column it is created.
PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.
FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.
CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:
1) References to CURRVAL, NEXTVAL, LEVEL and ROWNUM Pseudo columns.
2) Calls to SYSDATE, UID, USER and USERENV Functions
Q. What is the main difference between Unique Key and Primary Key?
The main difference between Unique Key and Primary Key are:
Unique Vs Primary Key
Unique Key
Primary Key
A table can have more than one Unique Key.
 A table can have only one Primary Key.
Unique key column can store NULL values.
Primary key column cannot store NULL values.
Uniquely identify each value in a column.
Uniquely identify each row in a table.
Q. What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?
ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.
Explore Oracle PL SQL Sample Resumes! Download & Edit for Free!Download Now!
Q. What is a Candidate Key?
The columns in a table that can act as a Primary Key are called Candidate Key.
Q.  What are Views and why they are used?
A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
  • To restrict data access
  • To make complex queries easy
  • To provide data Independence
  • Views provide groups of user to access data according to their requirement.
Q. What is a difference between Simple and Complex Views?
The main differences between two views are:
Simple Views Vs Complex Views
Simple View
Complex View
Derives data from only one table.
Derives data from many tables.
Contains no functions or group of data
 Contain functions or groups of data.
Can perform DML operations through the view.
Does not always allow DML operations through the view.
Q. What are the restrictions of DML operations on Views?
Few restrictions of DML operations on Views are:
You cannot DELETE a row if the View contains the following:
  1. Group Functions
  2. A Group By clause
  3. The Distinct Keyword
  4. The Pseudo column ROWNUM Keyword.
You cannot MODIFY data in a View if it contains the following:
  1. Group Functions
  2. A Group By clause
  3. The Distinct Keyword
  4. The Pseudo column ROWNUM Keyword.
  5. Columns defined by expressions (Ex; Salary * 12)
You cannot INSERT data through a view if it contains the following:
Q.  What is PL/SQL?
  1. PL/SQL is a procedural language extension with SQL Language.
  2. Oracle 6.0 introduced PL/SQL
  3. It is a combination of SQL and Procedural Statements and used for creating applications.
  4. Basically PL/SQL is a block structure programming language whenever we are submitting PL/SQL
  5. Blocks then all SQL statements are executing separately by using sql engine and also all procedure statements are executed separately.
Q. What are the different functionalities of a Trigger ?
Trigger is also same as stored procedure & also it will automatically invoked whenever DML operation performed against table or view.
There are two types of triggers supported by PL/SQL
  1. Statement Level Trigger.
  2. Row Level Trigger
Statement Level Trigger: In statement level trigger, trigger body is executed only once for DML statement.
Row Level Trigger: In row level trigger, trigger body is executed for each row DML statements. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers :old, :new, are also called as records type variables.
These qualifiers are used in trigger specification & trigger body.
Synatx:
             :old.column_name
Synatx:
            :new column_name
When we are use this qualifiers in trigger specification then we are not allowed to use “:” in forms of the qualifiers names.
Q. Write a PL/SQL Program which raise a user defined exception on thursday?
declare
a exception
begin
If to_char(sysdate, ‘DY)=’THU’
then
raise a;
end if;
exception
when a then
dbms_output.put_line(‘my exception raised on thursday’);
end;
Output: my exception raised on thursday
Q.Write a PL/SQL program to retrieve emp table and then display the salary?
declare
v_sal number(10);
begin
select max(sal)intr v_sal;
from emp;
dbms_output.put_line(v.sal);
end;
/
(or)
declare
A number(10);
B number(10);
C number(10);
begin
a:=70;
b:=30;
c:=greatest+(a,b);
dbms_output.put_line(c);
end;
/
Output:70
Q. Write a PL/SQL cursor program which is used to calculate total salary from emp table without using sum() function?
Declare
cursor c1 is select sal from emp;
v_sal number(10);
n.number(10):=0;
begin
open c1;
loop
fetch c1 into v_sal;
exit when c1%not found;
n:=n+v_sal;
end loop;
dbms_output.put_line(‘tool salary is’||’ ‘ ||n);
close c1;
end;
/
Output: total salary is: 36975
Q. Write a PL/SQL cursor program to display all employee names and their salary from emp table by using % not found attributes?
Declare
Cursor c1 is select ename, sal from emp;
v_ename varchar2(10);
v_sal number(10);
begin
open c1;
loop
fetch c1 into v_ename, v_sal;
exist when c1 % notfound;
dbms_output.put_line(v_name ||’ ‘||v_sal);
end loop;
close c1;
end;
/
Q. What is Mutating Trigger?
  • Into a row level trigger based on a table trigger body cannot read data from same table and also we cannot perform DML operation on same table.
  • If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
  • This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.
  • Mutating errors are not occured in statement level trigger because through these statement level trigger when we are performing DML operations automatically data committed into the database, whereas in row level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occured.
Q. What is Triggering Events (or) Trigger Predicate Clauses?
If we want to perform multiple operations in different tables then we must use triggering events within trigger body. These are inserting, updating, deleting clauses. These clauses are used in statement, row-level trigger. These triggers are also called as trigger predicate clauses.
Syntax:
If inserting then
stmts;
else if updating then
stmts;
else if deleting then
stmts;
end if;
Q. What is Discard File?
  • This file extension is .dsc
  • Discard file we must specify within control file by using discard file clause.
  • Discard file also stores reflected record based on when clause condition within control file. This condition must be satisfied into table tablename clause.
Q. What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR ?
Oracle 7.2 introduced ref cursor, This is an user defined type which is used to process multiple records and also this is a record by record process.
In static cursor database servers executes only one select statement at a time for a single active set area where in ref cursor database servers executes number of select statement dynamically for a single active set area that's why those cursor are also called as dynamically cursor.
Generally we are not allowed to pass static cursor as parameters to use subprograms where as we can also pass ref cursor as parameter to the subprograms because basically refcursor is an user defined type in oracle we  can also pass all user defined type as parameter to the subprograms.
Generally static cursor does not return multiple record into client application where as ref cursor are allowed to return multiple records into client application (Java, .Net, php, VB, C++).
This is an user defined type so we are creating it in 2 steps process i.e first we are creating type then only we are creating variable from that type that’s why this is also called as cursor variable.
Q. What are The Types of Ref Cursors?
In all databases having 2 ref cursors.
  1. Strong ref cursor
  2. Weak ref cursor
Strong ref cursor is a ref cursor which have return type, whereas weak ref cursor has no return type.
Syntax:
Type typename is ref cursor return record type data type;
Variable Name typename
Syntax
Type typename is ref cursor
Variable Name typename;
In Weak ref cursor we must specify select statement by using open for clause this clause is used in executable section of the PL/SQL block.
Syntax:
Open ref cursor varname for SELECT * FROM tablename condition;
Q. What is Difference Between trim, delete collection method?
 SQL> declare
           type t1 is table of number(10);
           v_t t1;=t1(10,20,30,40,50,60);
           beign
           v_t.trim(2);
           dbms_output.put_line(‘after deleting last two elements’);
            for i in v_t.first.. V_t.last
            loop
            dbms_output.put_line(v_t(i));
            End loop;
            vt.delete(2);
            dbms_output.put_line(‘after deleting second element;);
            for i in v_t.first..v_t.last
            loop
            If v_t.exists(i) then
            dbms_output.put_line(v_t(i));
            end if;
            end loop;
            end;
             /
Q. What is Overloading Procedures?
Overload is refers to same name can be used for different purpose, in oracle we can also implement overloading procedure through package. Overloading procedure having same name with different type or different number of parameters.
Q. What is Global Variables?
In oracle we are declaring global variables in Package Specification only.
Q. What is Forward Declaration?
In oracle declaring procedures within package body is called forward declaring generally before we are calling private procedures into public procedure first we must implements private into public procedure first we must implements private procedure within body otherwise use a forward declaration within package body.
Q. What is Invalid_number, Value_Error?
In oracle when we try to convert “string type to number type” or” data string into data type” then oracle server returns two types of errors.
1. Invalid.number
2. Value_error (or) numeric_error
a) Invalid_number:
When PL/SQL block have a SQL statements and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number
For handling this error oracle provides number exception Invalid_number exceptionname.
Example:
begin
Insert into
emp(empno, ename, sal) values(1,’gokul’, ‘abc’)
exception
when invalid_number then
dbms_output.put_line(‘insert proper data only’);
end;
/
b)value_error
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers returns an error: ora-6502:numeric or value error: character to number conversion error
For handling this error oracle provided exception value_error exception name
Example:
begin
declare
z number(10);
begin
z:= ‘&x’ + ‘&y’;
dbms_output.put_line(z);
exception
when value_error then
dbms_output.put_line(‘enter numeric data value for x & y only’);
end;
/
Output:
Enter value for x:3
Enter value for y:2
z:=5
Enter value for x:a
Enter value for y:b
Error:enter numeric data value for x & y only.
Q. What is Flashback Query?
  • Flashback query are handle by Database Administrator only flashback queries along allows content of the table to be retrieved with reference to specific point of time by using as of clause that is flashback queries retrieves clause that is flashback queries retrieves accidental data after committing the transaction also.
  • Flashback queries generally uses undo file that is flashback queries retrieve old data before committing the transaction oracle provide two method for flashback queries
Method1: using timestamp
Method2: using scn number
References: Stackoverflow | Scribd | Slideshare

1) What is PL SQL ?
PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.
2) Differentiate between % ROWTYPE and TYPE RECORD.
% ROWTYPE is used when a query returns an entire row of a table or view.
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
3) Explain uses of cursor.
Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.
4) Show code of a cursor for loop.
Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.
Eg. FOR smp_rec IN C1 LOOP
totalsal=totalsal+smp_recsal;
ENDLOOP;
5) Explain the uses of database trigger.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
1) Audit data modifications.
2) Log events transparently.
3) Enforce complex business rules.
4) Maintain replica tables
5) Derive column values
6) Implement Complex security authorizations
6) What are the two types of exceptions.
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
7) Show some predefined exceptions.
DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
etc.
8) Explain Raise_application_error.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
9) Show how functions and procedures are called in a PL SQL block.
Function is called as a part of an expression.
total:=calculate_sal('b644')
Procedure is called as a statement in PL/SQL.
calculate_bonus('b644');
10) Explain two virtual tables available at the time of database trigger execution.
Table columns are referred as THEN.column_name and NOW.column_name.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
11) What are the rules to be applied to NULLs whilst doing comparisons?
1) NULL is never TRUE or FALSE
2) NULL cannot be equal or unequal to other values
3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
12) How is a process of PL SQL compiled?
Compilation process includes syntax check, bind and p-code generation processes.
Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
13) Differentiate between Syntax and runtime errors.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
14) Explain Commit, Rollback and Savepoint.
For a COMMIT statement, the following is true:
  • Other users can see the data changes made by the transaction.
  • The locks acquired by the transaction are released.
  • The work done by the transaction becomes permanent.
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
  • The work done in a transition is undone as if it was never issued.
  • All locks acquired by transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
15) Define Implicit and Explicit Cursors.
A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
16) Explain mutating table error.
It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.
17) When is a declare statement required?
DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
18) How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
19) What is the importance of SQLCODE and SQLERRM?
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
20) If a cursor is open, how can we find in a PL SQL Block?
the %ISOPEN cursor status variable can be used.
21) Show the two PL/SQL cursor exceptions.
Cursor_Already_Open
Invaid_cursor
22) What operators deal with NULL?
NVL converts NULL to another specified value.
var:=NVL(var2,'Hi');
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
23) Does SQL*Plus also have a PL/SQL Engine?
No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.
24) What packages are available to PL SQL developers?
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
25) Explain 3 basic parts of a trigger.
  • A triggering statement or event.
  • A restriction
  • An action
26) What are character functions?
INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.
27) Explain TTITLE and BTITLE.
TTITLE and BTITLE commands that control report headers and footers.
28) Show the cursor attributes of PL/SQL.
%ISOPEN : Checks if the cursor is open or not
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
29) What is an Intersect?
Intersect is the product of two tables and it lists only matching rows.
30) What are sequences?
Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.
31) How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?
Using the keyword "new.column name", the triggers can reference column values by new collection. By using the keyword "old.column name", they can reference column vaues by old collection.
32) What are the uses of SYSDATE and USER keywords?
SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.
33) How does ROWID help in running a query faster?
ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.
34) What are database links used for?
Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.
35) What does fetching a cursor do?
Fetching a cursor reads Result Set row by row.
36) What does closing a cursor do?
Closing a cursor clears the private SQL area as well as de-allocates memory
37) Explain the uses of Control File.
It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.
38) Explain Consistency
Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.
39) Differ between Anonymous blocks and sub-programs.
Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.
40) Differ between DECODE and CASE.
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(totalsal=12000,'high',10000,'medium') as decode_tesr from smp where smpno in (10,12,14,16);
This statement returns an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
41) Explain autonomous transaction.
An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.
There are several situations to use autonomous transactions like event logging and auditing.
42) Differentiate between SGA and PGA.
SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.
43) What is the location of Pre_defined_functions.
They are stored in the standard package called "Functions, Procedures and Packages"
44) Explain polymorphism in PL SQL.
Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.
45) What are the uses of MERGE?
MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command
46) Can 2 queries be executed simultaneously in a Distributed Database System?
Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.
47) Explain Raise_application_error.
It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.
48) What is out parameter used for eventhough return statement can also be used in pl/sql?
Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.
49) How would you convert date into Julian date format?
We can use the J format string :
SQL > select to_char(to_date('29-Mar-2013','dd-mon-yyyy'),'J') as julian from dual;
JULIAN
50) Explain SPOOL
Spool command can print the output of sql statements in a file.
spool/tmp/sql_outtxt
select smp_name, smp_id from smp where dept='accounts';
spool off;
51) Mention what PL/SQL package consists of?
A PL/SQL package consists of
  • PL/SQL table and record TYPE statements
  • Procedures and Functions
  • Cursors
  • Variables ( tables, scalars, records, etc.) and constants
  • Exception names and pragmas for relating an error number with an exception
  • Cursors
52) Mention what are the benefits of PL/SQL packages?
It provides several benefits like
  • Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
  • Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
  • Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
  • Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
  • Guaranteeing transaction integrity: It provides a level of transaction integrity
  • Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
53) Mention what are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
  • DBMS_APPLICATION_INFO
  • DBMS_TRACE
  • DBMS_SESSION and DBMS_MONITOR
  • trcsess and tkproof utilities
54) Mention what does the hierarchical profiler does?
The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes
  • Distinct reporting for SQL and PL/SQL time consumption
  • Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
  • Multiple interactive analytics reports in HTML format by using the command line utility
  • More effective than conventional profiler and other tracing utilities
55) Mention what does PLV msg allows you to do?
The PLV msg enables you to
  • Assign individual text message to specified row in the PL/SQL table
  • It retrieves the message text by number
  • It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
  • Batch load message numbers and text from a database table directly PLV msg PL/SQL table
56) Mention what is the PLV (PL/Vision) package offers?
  • Null substitution value
  • Set of assertion routines
  • Miscellaneous utilities
  • Set of constants used throughout PL vision
  • Pre-defined datatypes
  • 57) Mention what is the use of PLVprs and PLVprsps?
  • PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
  • PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.
58) Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?
With a single program call - "fcopy procedure", you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program "file2pstab".
59) Explain how exception handling is done in advance PL/SQL?
For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.
  • Continue processing
  • Record and then continue
  • Halt processing
  • Record and then halt processing
For those exceptions that re-occurs you can use the RAISE statement.
60) Mention what problem one might face while writing log information to a data-base table in PL/SQL?
While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.
61) Mention what is the function that is used to transfer a PL/SQL table log to a database table?
To transfer a PL/SQL table log a database log table function "PROCEDURE ps2db" is used.
62) When you have to use a default "rollback to" savepoint of PLVlog?
The default "rollback to" savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.
63) Why PLVtab is considered as the easiest way to access the PL/SQL table?
The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.
64) Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?
PLVtab enables you to do following things when you show the contents of PL/SQL tables
  • Display or suppress a header for the table
  • Display or suppress the row numbers for the table values
  • Show a prefix before each row of the table
65) Explain how can you save or place your msg in a table?
To save msg in a table, you can do it in two ways
  • Load individual messages with calls to the add_text procedure
  • Load sets of messages from a database table with the load_from_dbms procedure
66) Mention what is the use of function "module procedure" in PL/SQL?
The "module procedure" enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules
  • module_in
  • cor_in
  • Last_module_in
67) Mention what PLVcmt and PLVrb does in PL/SQL?
PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.
  • PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
  • PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL

Top PL/SQL Interview Questions:
What is PL/SQL?
PL/SQL (Procedural Language/SQL) is basically a procedural extension of Oracle – SQL. PL/SQL helps the user to develop complex database applications using control structures, procedures, function, modules, etc.
This article will discuss the top-most PL/SQL interview question and answers.

PL/SQL Interview Questions and Answers

PL/SQL Interview Questions
Let’s start!!
Question #1) Differentiate PL/SQL and SQL?
Answer: Difference between SQL and PL/SQL can be categorized as follows
SQLPL/SQL
SQL is a natural language which is very useful for interactive processing.PL/SQL is a procedural extension of Oracle - SQL.
No procedural capabilities like condition testing, looping is offered by SQL.PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc.
All SQL statements are executed by the database server one at a time, thus it is a time-consuming process.PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably.
No error handling procedures are there in SQL.PL/SQL supports customized error handling.
Question #2) Enlist the characteristics of PL/SQL?
Answer: Characteristics of PL/SQL are as follows
  • PL/SQL allows access and sharing of the same sub programs by multiple applications.
  • PL/SQL is known for portability of code as code can be executed on any operating system provided Oracle is loaded on it.
  • With PL/SQL user can write their own customized error handling routines.
  • Improved transaction performance with integration to Oracle data dictionary.
Question #3) What are the data types available in PL/SQL?
Answer: Data types define the ways to identify the type of data and their associated operations. There are 4 types of predefined data types explained as follows
  • Scalar Data Types: A scalar data type is an atomic data type that does not have any internal components.
  • For example
    • CHAR (fixed length character value between 1 and 32,767 characters)
    • VARCHAR2 (variable length character value between 1 and 32,767 characters)
    • NUMBER ( fixed-decimal, floating-decimal or integer values)
    • BOOLEAN ( logical data type for TRUE FALSE or NULL values)
    • DATE (stores date and time information)
    • LONG (character data of variable length)
  • Composite Data Types: A composite data type is made up of other data types and internal components that can be easily used and manipulated. For example RECORD, TABLE, and VARRAY.
  • Reference Data Types: A reference data types holds values, called pointers that designate to other program items or data items. For example REF CURSOR.
  • Large Object Data Types: A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.
  • For example
    • BFILE (Binary file)
    • BLOB (Binary large object)
    • CLOB ( Character large object)
    • NCLOB( NCHAR type large object)
Question #4) Explain the purpose of %TYPE and %ROWTYPE data types with the example?
Answer: PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.
For example, the variable m_empno has the same data type and size as the column empno in table emp.
m_empno emp.empno%TYPE;
%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.
For example: dept_rec dept%ROWTYPE;
This declares a record that can store an entire row for DEPT table.
Question #5) What do you understand by PL/SQL packages?
Answer: PL/SQL packages are schema objects that groups functions, stored procedures, cursors and variables at one place. Packages have 2 mandatory parts
  • Package Specifications
  • Package body
Question #6) What do you understand by PL/SQL cursors?
Answer: PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.
PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a PL/SQL program.
Question #7) Explain cursor types?
Answer: There are two types of cursors. They are explained as follows
1) Explicit Cursors: For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed
Declare the cursor
Syntax: CURSOR <cursor_name> is
SELECT statement;
Where <cursor_name> is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.
Open the cursor
Syntax: OPEN <cursor_nam>;
Where, <cursor_name> is the name of the previously defined cursor.
Fetch rows from the cursor

Syntax: FETCH <cursor_name> INTO <record_list>;
Where <cursor_name> refers to the name of the previously defined cursor from which rows are being fetched.
<record_list> represents the list of variables that will receive the data being fetched.
Closing the cursor
Syntax: CLOSE <cursor_name>;
Where <cursor_name> is the name of the cursor being closed.
2) Implicit cursors: When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors.
For following statements, PL/SQL employs implicit cursors
  • INSERT
  • UPDATE
  • DELETE
  • SELECT ( queries that return exactly one row)
Question #8) When do we use triggers?
Answer: The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when database related event is performed. Triggers are mainly required for the following purposes
  • To maintain complex integrity constraints
  • Auditing table information by recording the changes
  • Signaling other program actions when changes are made to table
  • Enforcing complex business rules
  • Preventing invalid transactions
Question #9) Explain the difference in execution of triggers and stored procedures?
Answer: A stored procedure is executed explicitly by issuing procedure call statement from another block via a procedure call with arguments.
The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.
Question #10) Explain the difference between Triggers and Constraints?
Answer: Triggers are different from constraints in the following ways
TriggersConstraints
Only affect those rows added after the trigger is enabled.Affect all rows of the table including that already exist when the constraint is enabled.
Triggers are used to implement complex business rules which cannot be implemented using integrity constraints.Constraints maintain the integrity of the database.
Question #11) What is a PL/SQL block?
Answer: In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package.
Broadly, PL/SQL blocks are two types
1) Anonymous blocks: PL/SQL blocks without header are known as anonymous blocks. These blocks do not form the body of a procedure, function or triggers.
Example:
1DECLARE
2num NUMBER(2);
3sq NUMBER(3);
4BEGIN
5num:= &Number1;
6sq := num*num;
7DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);
8END;
2) Named blocks: PL/SQL blocks having header or labels are known as Named blocks. Named blocks can either be subprograms (procedures, functions, packages) or Triggers.
Example:
1FUNCTION sqr (num IN NUMBER)
2RETURN NUMBER is sq NUMBER(2);
3BEGIN
4sq:= num*num;
5RETURN sq;
6END;
Question #12) Differentiate between syntax and runtime errors?
Answer: Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be the spelling mistake, etc.
Runtime errors are those errors in PL/SQL block for which exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.
Question #13) What are COMMIT, ROLLBACK, and SAVEPOINT?
Answer: COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL.
COMMIT statement: When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens
  • Affected rows locks are released
  • Transaction marked as complete
  • Transaction detail is stored in the data dictionary.
Syntax: COMMIT;
 ROLLBACK statement: When we want to undo or erase all the changes that have occurred in the current transaction so far, we require rolling back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.
Syntax to rollback a transaction fully
ROLLBACK;
SAVEPOINT statement: The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released.
Syntax:
SAVEPOINT <savepoint_name>;
Question #14) What is the mutating table and constraining table?
Answer: A table which is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table.
A table that might need to be read from for a referential integrity constraint is known as constraining table.
Question #15) What are actual parameters and formal parameters?
Answer: The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters.
For example: raise_sal(emp_num, merit+ amount);
Here in the above example, emp_num and amount are the two actual parameters.
The variables that are declared in the procedure header and are referenced in the procedure body are called as Formal parameters.
For example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
Here in the above example, emp_id acts as a formal parameter.
Question #16) What is the difference between ROLLBACK and ROLLBACK TO statements?
Answer: The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks.
On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT.
Question #17) Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?
Answer
1SET SERVER OUTPUT ON
2DECLARE
3BEGIN
4FOR i IN REVERSE 1..99
5LOOP
6IF Mod(i,3) = 0 THEN
7DBMS_OUTPUT.PUT_LINE(i);
8END IF;
9END LOOP;
10END;
11/
Question #18) What are the 3 modes of parameter?
Answer: 3 modes of the parameter are IN, OUT, IN OUT. These can be explained as follows
IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.
OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.
IN OUT parameters: IN OUT parameters passes initial values to a procedure and returns updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.
Question #19) Why is %ISOPEN always false for an implicit cursor?
Answer: An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of DML statement.
Question #20) When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?
Answer: The outcome of the statement is saved in 4 cursor attributes. These are
  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ROWCOUNT
  • SQL%ISOPEN
Question #21) What are the ways on commenting in a PL/SQL code?
Answer: Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. There are two ways to comment in PL/SQL
1) Single line comment: This comment starts with double –.
Example:
DECLARE
num NUMBER(2);        — it is a local variable.
BEGIN
2) Multi-line comment: This comment starts with /* and ends with */.
Example:
BEGIN
num := &p_num;         /* This is a host variable used in program body */
……….
END
Question #22) What do you understand by Exception handling in PL/SQL?
Answer: When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in EXCEPTION section.
There are 3 types of EXCEPTION:
  • Predefined Exceptions: Common errors with predefined names.
  • Undefined Exceptions: Less common errors with no predefined names.
  • User-defined Exceptions: Do not cause runtime error but violate business rules.
Question #23) Enlist some predefined exceptions?
Answer: Some of the predefined exceptions are
  • NO_DATA_FOUND: Single row SELECT statement where no data is returned.
  • TOO_MANY_ROWS: Single row SELECT statement where more than one rows are returned.
  • INVALID_CURSOR: Illegal cursor operation occurred.
  • ZERO_DIVIDE: Attempted to divide by zero.
Question #24) What are PL/SQL cursor exceptions?
Answer: The exceptions related to PL/SQL cursors are
  • CURSOR_ALREADY_OPEN
  • INVALID_CURSOR
Question #25) Explain the difference between cursor declared in procedures and cursors declared in the package specification?
Answer: The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures.
The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.
Question #26) What are INSTEAD of triggers?
Answer: The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.
Question #27) What are expressions?
Answer: Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.
  • Operands: These are an argument to the operators. Operands can be a variable, function call or constant.
  • Operators: These specify the actions to be performed on operators. E.g. ‘+’, ‘*’, etc.
Question #28) List different type of expressions with the example.
Answer: Expressions can be as mentioned below
  • Numeric or Arithmetic expressions : e.g. 20* 10+ 15
  • Boolean expressions: e.g. ‘spot’ LIKE ‘sp%t’
  • String expressions: e.g. LENGTH (‘NEW YORK’|| ‘NY’)
  • Date expressions: e.g. SYSDATE>TO_DATE(’15-NOV-16’, “dd-mm-yy”)
Question #29) Write a program that shows the usage of WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?
Answer
1DECLARE
2n NUMBER;
3avg NUMBER :=0 ;
4sum NUMBER :=0 ;
5count NUMBER :=0 ;
6BEGIN
7n := &enter_a_number;
8WHILE(n<>0)
9LOOP
10count := count+1;
11sum := sum+n;
12n := &enter_a_number;
13END LOOP;
14avg := sum/count;
15DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
16END;
Question #30) What do you understand by PL/SQL Records?
Answer: A PL/SQL records can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.
There are three types of records supported in PL/SQL
  • Table based records
  • Programmer based records
  • Cursor based records


1. Compare SQL & PL/SQL
CriteriaSQLPL/SQL
What it isSingle query or command executionFull programming language
What it comprisesData source for reports, web pagesApplication language to build, format and display report, web pages
CharacteristicDeclarative in natureProcedural in nature
Used forManipulating dataCreating applications
2. What is PL/SQL?
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Go through this tutorial to learn more about PL/SQL.
3. What is the basic structure of PL/SQL?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
Master PL/SQL, in this PL/SQL certification training.
Become PL SQL Certified in 24 hrs.

GET CERTIFIED
4. Explain the uses of database trigger?
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
  • Audit data modifications.
  • Log events transparently.
  • Enforce complex business rules.
  • Maintain replica tables
  • Derive column values
  • Implement Complex security authorizations
Any of the constant, variable or parameter has a data type depending on which the storage constraints, format and range of values and operations are determined.
5. How is a process of PL/SQL compiled?
Compilation process includes syntax check, bind and p-code generation processes.Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
Go through this tutorial to learn more about handling PL/SQL errors.
6. Mention what PL/SQL package consists of?
A PL/SQL package consists of
  • PL/SQL table and record TYPE statements
  • Procedures and Functions
  • Cursors
  • Variables ( tables, scalars, records, etc.) and constants
  • Exception names and pragmas for relating an error number with an exception
  • Cursors
Check the insightful PL/SQL tutorial to learn more about Pl/SQL Packages.
7. What are the benefits of PL/SQL packages?
It provides several benefits like
  • Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
  • Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
  • Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
  • Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
  • Guaranteeing transaction integrity: It provides a level of transaction integrity
  • Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
8. What are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
  • DBMS_APPLICATION_INFO
  • DBMS_TRACE
  • DBMS_SESSION and DBMS_MONITOR
  • trcsess and tkproof utilities
9. What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
10. What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
Learn more about PL/SQL Collections & Records.
11. What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed.A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
12. Day-to –Day activities?
  1. creating Database objects—Tables,synonyms, sequences
  2. To implement Business Rules— creat procedures, functions
  3. To impose Business Rules— create constraints, Trigers.
  4. Data manipulation———– create cursors.
13. Display records having max salary?
Select * from emp where sal= (select max(sal) from emp)
14. Display Highest salary from table?
Select max(sal) from emp;
15. Display 2nd Highest salary from table?
Select max(sal) from emp where sal not in ( select max(sal) from emp
16. What is a JOIN?
It is a Keyword, used to query data from multiple tables based on relationship between the fields of the tables. Keys plays major role in JOINs.
17. what is View?
  • It is a virtual table ,which consists of data contained in a table.
  • Views not need any memory space .
  • View can be created on multiple tables.
18. What is subquery? Types.
  • A subquery is a query within another query.
  • The outer query is known as Main query, &amp; inner query is called subquery.
  • Subquery is executed first &amp; result of subquery is passed to main query.
  • Types——— correlated &amp; non-correlated
19. what is Trigger?
It is a Database object,that automatically executes in response to some events on the tables or views. It is used to apply the Integrity constraint to the Database objects.


1.What is cursor ?
Answer :
Cursor is a buffer area which is used to process multiple records and also record by record tabs.
There are 2 types of cursors :
1.Implicit cursor
2.Explicit cursor
Implicit cursor : Implicit cursor is a buffer area which has been defined and controlled by oracle internally. Implicit cursor will process single record at a time.
example :
declare
v_Ename varchar2(100);
begin
select ename into V_Ename from Employee where empno=101;
dbms_output.put_line(V_Ename );
end;
The above cursor is implicit cursor where all the operations are defined by oracle engine internally like declaring the cursor,fetching values from the cursor and close cursor.
Explicit Cursor : Explicit cursor is a cursor which is defined by user to process set of records.For multiple records user needs to use explicit cursor.Explicit cursor operations are done by the user.
There are following 4 operations needs to be done by user :
1.Declare cursor
2.Open cursor
3.Fetch all records from the cursor
4.Close cursor.
2.What are different cursor attributes?
Answer :
There are following cursor attributes :
1.%Found
2.%Not Found
3.%Isopen
4.%Rowcount
3.What is ref cursor?Why it is used?
Answer :
As the name suggested ref cursor is a variable which will point to the address or reference of the cursor.Ref cursor is variable not cursor but that variable points to cursor.
There are 2 type of ref cursors :
1.Strong Ref cursor
2.Weak Ref cursor
4.What is  %ROWTYPE ? Explain this with example.
Answer :
%ROWTYPE is cursor attribute which is used to define the record of the field.Each field assumes it own datatype and %ROWTYPE is used to define the specific record type.
example :
CREATE OR REPLACE PROCEDURE P_Employee_Information
IS
CURSOR Emp_Cur IS SELECT Employee_name, Employee_Number FROM emp;
variable1 Emp_Cur %ROWTYPE; ---This is cursor variable name
BEGIN
OPEN Emp_Cur ;
LOOP
FETCH Emp_Cur INTO variable1;
EXIT WHEN Emp_Cur %Notfound; ---When cursor
DBMS_OUTPUT.PUT_LINE( variable1.Employee_name || ' works in department '
|| myvar.Employee_Number);
END LOOP;
CLOSE Emp_Cur ;
END;
5.How to write Cursor with for loop?

Answer:
Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.Means while using the for loop user dont need to Open the cursor and fetch the values from cursor or close cursor explicitly.
In For loop all cursor operations done implicitly..
Real Example:
FOR Sample_cursor IN C1 LOOP
Total_Salary=Total_Salary + Appraisals;
END LOOP;
6.What is Database Trigger?What is real use of trigger?
Answer :
PL SQL procedure which is used to trigger specific event on specific condition is known as database triggers. Triggers are database objects with specific conditions.
Examples of Trigger :
1)Audit data modifications.
2)Log events transparently.
3)Enforce complex business rules.
4)Maintain replica tables
5)Derive column values
6)Implement Complex security authorizations.
7.What is Raise_Application_Error?
Answer :
When user wants to insert Error message then user needs to use the Raise_Application_Error procedure. Raise_Application_Error is the system defined procedure of package named DBMS_STANDARD.
Syntax :
Raise_Application_Error(Error_Code,Error_Message);
Example :
Raise_Application_Error (-20343, ‘The balance is too low.’);
8.What is commit?RollBack?Savepoint?
Answer :
Commit :
When user commits the data after transaction that changes are permanent changes.
1.Other users can see the data changes made by the transaction.
2.The locks acquired by the transaction are released.
3.The work done by the transaction becomes permanent.
Rollback :
When transaction become wrong user can rollback the data.
1.The work done in a transition is undone as if it was never issued.
2.All locks acquired by transaction are released.
Savepoint :
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
9.What is mutating error?(90% asked PL SQL Interview Questions)
Answer :
It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.
10.What is mean by Unique Indexes?(90% asked PL SQL Interview Questions)
Answer:
1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.
2.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.
3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.
4.The unique indexes are also called as clustered indexes when primary key is defined on the column.
Example:
Create Unique index  Index_name on Table_name(Unique column name);
Example:
CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

11.How many triggers can be applied on one table?
Answer :
There are maximum 12 triggers can be applied on one table.
12.What is Bit-map index?Explain with Example.(80 % Asked in PL SQL Interview Questions)
Answer:
1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.
2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.
3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.
4.Means If in  1 million  records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.
Syntax:
Create bitmap index Index_name on Table_name(Columns which have distinct values);
Example:
CREATE BITMAP index  BM_DEPT_NAME on DEPT(Department_name);
12. What are different cursor attributes?
Answer :
%ISOPEN :
Checks if the cursor is open or not
%ROWCOUNT :
The number of rows that are updated, deleted or fetched.
%FOUND :
Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND :
Checks if the cursor has fetched any row. It is True if rows are not fetched.
13.What is sequences in PL SQL?
Answer :
Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.
14.What is database link used for?
Answer :
Database links are used to communicate between 2 databases.Database links are created in order to form communication between various databases, or different environments like test, development and production.When user wants to insert or update data from one environment to another environment then database links are used.
15.What is difference between Anonymous  block and subprogram?
Answer : 
Anonymous block :
Anonymous blocks are programs or unnamed block which is used to check some functionality and which are not stored in database.
Subprograms :
Subprograms are stored blocks which are stored in to database. Subprograms are compiled at runtime.
16.What are types of exceptions in PL SQL?
Answer :
There are 2 types of exceptions in PL SQL :
1.Predefined Exceptions :
These exceptions are system defined exceptions.
2.User defined Exceptions:
User defined exceptions are exceptions which are defined by the user in the program.
17.Give us example of Predefined Exceptions?
Answer :
DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
18.What operators deals with null?
Answer :
NVL converts NULL to another specified value.
var:=NVL(var2,’Hi’);
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
19.What is cost based optimizer?
Answer:
Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself  decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on  the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.
“Cost based optimizer considers the statistical information of the table for query execution”
20.What is difference between Decode and Case?
Answer :
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(Total_salary=50000,’high’,40000,’medium’) as “Decode Test” from Employee where Employee_number in (1,2,3,4,5,6);
This statement will return an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
These are some important PL SQL interview questions,hope you like this article.




Difference between Syntax and Runtime errors in PL/SQL??

Syntax errors occur when the interpreter find something not compelling with PL/SQL syntax.
Runtime errors occur because of unhandled logic while executing the code. Exception handle should be implemented to overcome Runtime errors.

Explain what is COMMIT, ROLLBACK and SAVEPOINT??

COMMIT      :Make changes done in  transaction permanent.
ROLLBACK  :Rollbacks the state of database to the last commit point.
SAVEPOINT :Use to specify a point in transaction to which later you can rollback.

How many types of Triggers are there in PL/SQL and what are they?


Majorly there are 5 different types of triggers

  1. BEFORE Trigger : BEFORE trigger execute before the triggering DML statement (INSERT, UPDATE, DELETE) execute. Triggering SQL statement is may or may not execute, depending on the BEFORE trigger conditions block.
  2. AFTER Trigger : AFTER trigger execute after the triggering DML statement (INSERT, UPDATE, DELETE) executed. Triggering SQL statement is execute as soon as followed by the code of trigger before performing Database operation.
  3. ROW Trigger : ROW trigger fire for each and every record which are performing INSERT, UPDATE, DELETE from the database table. If row deleting is define as trigger event, when trigger file, deletes the five rows each times from the table.
  4. Statement Trigger : Statement trigger fire only once for each statement. If row deleting is define as trigger event, when trigger file, deletes the five rows at once from the table.
  5. Combination Trigger : Combination trigger are combination of two trigger type,
    1. Before Statement Trigger : Trigger fire only once for each statement before the triggering DML statement.
    2. Before Row Trigger : Trigger fire for each and every record before the triggering DML statement.
    3. After Statement Trigger : Trigger fire only once for each statement after the triggering DML statement executing.
    4. After Row Trigger : Trigger fire for each and every record after the triggering DML statement executing.

How to generate Debugging Information in PL/SQL?

There are several ways to generate debugging information, I have listed few of them below:

  1. You can use the DBMS_OUTPUT package
  2. Another possible method is to just use the SHOW ERROR command, but this only shows errors.
  3. You can use UTL_FILE package
  4. We can just insert debug information into some table


In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented



OPEN then FETCH then LOOP followed by the EXIT WHEN.

Name any 5 PL/SQL exception types

  1. NO_DATA_FOUND       – ORA-01403
  2. INVALID_CURSOR       – ORA-01001
  3. ROWTYPE_MISMATCH – ORA-06504
  4. TOO_MANY_ROWS      – ORA-01422
  5. ZERO_DIVIDE             – ORA-01476


What is Public and Private Procedures in PL/SQL?

Public Procedures:
Any procedures declared inside the package specification are visible outside the package and can be accessed in any objects.

Private Procedures:
The procedures that are declared inside the package body, you are restricted to use within that package.
Therefore, PL/SQL code outside the package cannot reference any of the procedures that were privately declared within the package.

What is Referential Integrity?

Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.

What is SQLCODE and SQLERRM in PL/SQL?

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.

The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.

What is the difference between CASE and DECODE?

Below are the major differences between CASE and DECODE:

  1. DECODE is Oracle standard and CASE is ANSI standard.
  2. DECODE is a function where CASE is a Expression.
  3. CASE is Faster when compared to DECODE since DECODE is a function which takes time to load and run but the cost difference of DECODE and CASE is very very minimal.
  4. Both CASE and DECODE can be used in WHERE clause.
  5. CASE requires all return expressions to be of same base type. DECODE doesn’t. DECODE result type is first decoded expression type, all others are implicitly converted (if needed). DECODE considers two nulls to be equivalent while CASE doesn’t.
  6. Relational operators can’t be used in DECODE. like  decode(  sal >1000,’high’,10000,’good’,’ok’).
  7. CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL statements only.
  8. CASE can work with predicates and searchable sub queries

What is the difference between VARCHAR and VARCHAR2 in PL/SQL?

  1. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
  2. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
  3. VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.
  4. VARCHAR2 does not distinguish between a NULL and empty string, and never will.
  5. If you rely on empty string and NULL being the same thing, you should use VARCHAR2.

What is %TYPE and %ROWTYPE in PL/SQL??

%TYPE provides the data type of a variable or a database column to that variable.

Example:
l_item_id MTL_SYSTEM_ITEM_B.INVENTORY_ITEM_ID%TYPE;


%ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

Example:
DECLARE
  l_rec employee%rowtype;
  CURSOR c1
  IS
    SELECT * FROM employee WHERE empno = 100;
BEGIN
  OPEN c1;
  FETCH c1 INTO l_rec;
  CLOSE c1;
END;

What is the disadvantage of using %ROWTYPE in PL/SQL?

The disadvantage of declaring the record as a ROWTYPE is:
When u create a record as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields. So use ROWTYPE only when you are using all the columns of the table in the program.

What are the advantages of using %TYPE and $ROWTYPE in PL/SQL?

The advantages of declaring the record as a ROWTYPE are:
  1. You do not need to explicitly declare variables for all the columns in a table.
  2. If you alter the column specification in the database table, you do not need to update the code.

The advantages of using %TYPE are:
  1. Need not know about variable’s data type
  2. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

List all different types of Data Types in PL/SQL?

Here are the complete list of Data Types supported by Oracle
Number Data Types
  1. BINARY_INTEGER
  2. NUMBER(p,s)    
  3. NATURAL
  4. NATURALN
  5. PLS_INTERGER
  6. POSITIVE
  7. POSITIVEN
  8. SIGNTYPE
  9. NUMERIC(p,s)    
  10. FLOAT
  11. DEC(p,s)    
  12. DECIMAL(p,s)    
  13. INTEGER               
  14. INT                       
  15. SMALLINT                       
  16. REAL                       
  17. DOUBLE PRECISION

Character Data Types
  1. CHAR(size)    
  2. CHARACTER(size)
  3. ROWID
  4. STRING
  5. UROWID   
  6. NCHAR(size)   
  7. NVARCHAR2(size)
  8. VARCHAR2(size)    
  9. LONG    
  10. RAW    
  11. LONG RAW

Date Type Data Types
  1. DATE
  2. INTERVAL DAY TO SECOND
  3. INTERVAL YEAR TO MONTH
  4. TIMESTAMP
  5. TIMESTAMP WITH LOCAL TIMEZONE
  6. TIMESTAMP WITH TIMEZONE

Composite Data Types
  1. RECORD
  2. TABLE
  3. VARRAY

Reference Data Types
  1. REF CURSOR
  2. REF Object_Type  

LOB Data Types
  1. BFILE
  2. BLOB
  3. CLOB
  4. NCLOB

Boolean Data Types
  1. Boolean

What is the difference between Views and Materialized Views in Oracle?

Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query resultset has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.

Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.

What is difference between a PROCEDURE & a FUNCTION ?

Below are the differences between FUNCTION and PROCEDURE

  1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
  2. Function can be called from SQL statements where as procedure can not be called from the sql statements
  3. Functions are normally used for computations where as procedures are normally used for executing business logic.
  4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
  5. Function returns 1 value only. Procedure can return multiple values (max 1024).
  6. Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
  7. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
  8. Stored procedure is pre-compiled execution plan where as functions are not.
  9. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
  10. We can use DDL in Procedure using Execute Immediate statement while that is not possible in functions

What are the Advantages of Using Database Triggers??

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
  1. Audit data modifications
  2. Log events transparent
  3. Enforce complex business rules
  4. Maintain replica tables
  5. Derive column values
  6. Implement Complex security authorizations
  7. Triggers can be used as an alternative method for implementing referential integrity constraints
  8. By using triggers, business rules and transactions are easy to store in database and can be used consistently even if there are future updates to the database
  9. It controls on which updates are allowed in a database
  10. When a change happens in a database a trigger can adjust the change to the entire database
  11. Triggers are used for calling stored procedures.

What are two virtual tables available during database trigger execution in PL/SQL?

The table columns are referred as OLD.column_name and NEW.column_name.

  1. For triggers related to INSERT only NEW.column_name values only available.
  2. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
  3. For triggers related to DELETE only OLD.column_name values only available.


Is it mandatory to close the Cursor in a PL/SQL block ?

PL/SQL block works correctly even when we do not close the cursor. But if we need to open another cursor after the previous one (first cursor) then we need to close the first cursor.

What is difference between % ROWTYPE and TYPE RECORD?

Advantages:
% ROWTYPE is to be used whenever query returns a entire row of a table or view.

TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

Example:
TYPE r_emp IS RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is SELECT empno,deptno FROM emp;
e_rec c1 %ROWTYPE.

Disadvantages
When we have a variable of type RECORD we have to declare additional variables but with %ROWTYPE, we can have all the fields that are present in the table.

How does an Explicit Cursor work in PL/SQL?

  1. Declaring the cursor: This initializes the cursor into memory.
  2. Opening the cursor: The declared cursor is opened, and memory is allotted.
  3. Fetching the cursor: The declared and opened cursor can now retrieve data.
  4. Closing the cursor: The declared, opened, and fetched cursor must be closed to release the memory allocation.

Can we use RETURN STATEMENT and RETURN key word in Procedure, if we use what will happen?

We can use RETURN in procedure without any expression.. the control will just go to invoker after return statement.

How to Generate Debug Logging for Custom Extensions

It would be tiresome for most of us to debug issues especially in extensions when we have no clue of flow of process execution and also the values of variables in intermittent stages. Debugging becomes more complex when most of custom logic is in huge pl/sql scripts.
Today in this article I will show how to implement debugging feature which I regularly use in the custom extensions I develop.
To generate debugging mechanism we need
  1. A table to store log information
  2. A sequence to generate unique ID value to each record in the table
  3. A trigger to assign unique value to ID column
  4. A PL/SQL procedure with a logic to insert debug messages to debug table
  5. A PL/SQL statement to call the PL/SQL Debug procedure

Create Table to store Debug Messages

CREATE TABLE XX_DEBUG_LOG
  (
    id             NUMBER,
    component_name VARCHAR2(220),
    log_text       VARCHAR2(220),
    creation_date  VARCHAR2(220) DEFAULT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MM:SS')
  );
creation_date column has default value as sysdate which acts like a time stamp.

Create a Sequence to generate unique ID

CREATE SEQUENCE XX_DEBUG_LOG_SEQ
 START WITH 1 
 INCREMENT BY 1 
 NOCYCLE 
 NOCACHE;

Create a Trigger to assign unique value to ID column

CREATE OR REPLACE TRIGGER XX_DEBUG_LOG_t1 BEFORE
  INSERT ON XX_DEBUG_LOG FOR EACH ROW BEGIN IF :NEW.id IS NULL THEN
  SELECT XX_DEBUG_LOG_seq.nextval INTO :NEW.id FROM dual;
END IF;
END;
/

Create a Procedure to insert Debug Message

CREATE OR REPLACE
PROCEDURE XX_INSERT_LOG_P(
    p_component_name VARCHAR2 ,
    p_log_text       VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT
  INTO XX_DEBUG_LOG
    (
      component_name,
      log_text
    )
    VALUES
    (
      p_component_name,
      p_log_text
    );
  COMMIT;
END;
/
This procedure is run as an PRAGMA AUTONOMOUS_TRANSACTION so that it will run independently of current session and the COMMIT statement in this procedure would not impact the parent session which calls this procedure. Since this is an AUTONOMOUS transaction debug messages will be captured even if there is any ROLLBACK statement in the parent session.

Create PL/SQL statement to call the PL/SQL Debug procedure

XX_INSERT_LOG_P('comp_name','log message');

Example

DECLARE
  CURSOR EMP_CUR
  IS
    SELECT * FROM EMP;
  --
  c_comp_name VARCHAR2(20) := 'LIST EMP';
  --
BEGIN
  --
  XX_INSERT_LOG_P(c_comp_name,'in begin block');
  --
  FOR I IN EMP_CUR
  LOOP
    BEGIN
      --
      XX_INSERT_LOG_P(c_comp_name,'in loop '||i.ename);
      dbms_output.put_line(i.ename);
      --
    EXCEPTION
    WHEN OTHERS THEN
      XX_INSERT_LOG_P(C_COMP_NAME,'in Exception - '||I.ENAME||' Err - '||SQLERRM);
    END;
  END LOOP;
END;
Content in Debug Log table:



In this was you can use this debugging mechanism in extensions like workflows, forms/OA pages, Forms Personalizations, Business Event Subscriptions, what not.

https://tekslate.com/oracle-plsql-interview-questions/

What is PL-SQL ?
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
What is the basic structure of PL/SQL?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
What are the most important characteristics of PL/SQL?
A list of some notable characteristics:
  • PL/SQL is a block-structured language.
  • It is portable to all environments that support Oracle.
  • PL/SQL is integrated with the Oracle data dictionary.
  • Stored procedures help better sharing of application.
How is a process of PL/SQL compiled?
Compilation process includes syntax check, bind and p-code generation processes.Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?
Before getting into the PL/SQL, we must create a REST API using JSON for establishing connection. You can import the Retrofit libraries along with dependencies to establish communication with your Android App. Then prepare the functions and procedures using PL/SQL, and then once your oracle database is connected.
Name few schema objects that can be created using PL/SQL?
Stored procedures and functions
Packages
Triggers
Cursors
How is PL/SQL differs from SQL?
SQLPL/SQL
Query/Command executionsProgramming Language Execution at a time
Data source for web pagesBuild, format and display web pages that contains data source
Declarative in natureProcedural in nature
Manipulating dataCreating Web Applications

What are the three basic sections of a PL/SQL block?
Declaration section
Execution section
Exception section
What is a trigger?
A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.
What are the uses of triggers?
Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for −
  • Creating validation mechanisms involving searches in multiple tables
  • Creating logs to register the use of a table
  • Update other tables as a result of inclusion or changes in the current table.
How can we debug in PL/SQL?
We can make use of the DBMS_OUTPUT for printing breakpoint activities. We can also use DBMS_DEBUG.
How can we implement Rollback or Commit statement in a Trigger?
We cannot. It is not logical to put a Rollback or Commit within a Trigger because these statements impose a savepoint which affects the logical transaction processing.
What is an Oracle sequence?
A Sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
Pl SQL Interview Questions
What is PL/SQL Records?
PS/SQL Records is type of data structure that contain a set of data(can be of various types), or distinct information values that can be referenced with each other as fields. They are useful for classifying and retrieving data with common attributes or properties. With this, it is much easier to identify similar data by tracing the attributes.
PL/SQL can manage three types of records:
  • Table based records
  • Programmer based records
  • Cursor based records
How can we make an IF Statement within a SELECT Statement?
We make use of the DECODE keyword. For example,
e.g. select DECODE (EMP_CAT,’3′,’Third’,’4′,’Fourth’Null);
What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.
How are functions and procedures called in PL/SQL?
CALL <procedure name> to call it directly
EXECUTE <procedure name> from calling environment
<Procedure name> from other procedures or functions or packages
Functions are called directly from other programs or procedures, no additional keyword is required.
What are the different datatypes available in PL/SQL?
PL SQL data types can be broadly divided into following categories. There are many data types available in PL SQL but mostly you will be using some of the popular ones.
  • Numbers – INT, INTEGER, FLOAT, NUMBER, SMALLINT, REAL etc.
  • Character or String – CHAR, CHARACTER, RAW, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2 etc.
  • Boolean – BOOLEAN
  • Date Time – DATE, TIMESTAMP etc.
  • Refer them in detail at oracle Database Documentation.
Give the stages of instance startup to a usable state where normal users may access it. 
STARTUP NOMOUNTInstance startup
STARTUP MOUNTThe database is mounted
STARTUP OPENThe database is opened
How to convert date to Julian Date format?
Using ‘J’ format string
SQL > select to_char(to_date(’24-Jan-2013′,’dd-mm-yyyy’),’J’) as julian from dual;
JULIAN
What is meant by Exception Handling in PL/SQL and what are its distinct types?
The exception is raised in PL/SQL whenever an error occurs. In other words, these are raised to handle undesired situations where PL/SQL scripts terminated unexpectedly including an error handling code in the program. Exception Section in PL/SQL contains this exception handling code.
There are 3 types of Exception:
Predefined Exceptions:  General Errors with pre-defined names
Undefined Exceptions: Less common errors and are not pre-defined
User-defined Exceptions: Violates business rules but don’t disturb running
Discuss PL/SQL Records?
The collection of PL/SQL server training Hyderabad is referred as the collection of values or information that is divided into multiple pieces, each of which is of simpler types and related to one another as fields.
There are three types of records supported in PL/SQL
  • Programmer based records
  • Table based records
  • Cursor based records

Question 1) What is Oracle plsql?
Answer
PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language
Question 2) What are the components of a PL/SQL Block?
Answer
Declarative part, Executable part and Exception part.
Question 3) What are the datatypes a available in PL/SQL?
Answer
Some scalar data types such as
NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
Question 4) What is a cursor? Why Cursor is required?
Answer
Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.
Question 5) What is the difference between Procedure and Function ?
Answer
FunctionProcedure
Function must return a valueProcedure need not
Syntax 
FUNCTION name (argument list …..) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;


Syntax
PROCEDURE name (parameter list…..)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
Function can be used in SQL with some restrictionProcedure cannot be called directly from SQL.
Question 6) What is the difference between DELETE and TRUNCATE ?
Answer
DeleteTruncate
DELETE is a DML commandTRUNCATE is a DDL command.
Syntax 
Delete * from table
Commit;

Syntax
Truncate table <table name>;
It does not change the high-water markTRUNCATE re-set the memory blocks after execution i.e. it sets the high-water mark of the object
It can be rollbackIt cannot be rollback
Delete trigger are firedNo triggers will be fired
Rollback segments are usedLess rollback segments are used and it is much faster
Question 7) What is Implicit Cursor and Explicit Cursor ?
Answer
Implicit CursorExplicit Cursor
Implicit Cursor is defined and controlled by Oracle Internally.Explicit Cursor is defined and controlled programatically.
Syntax 
declare
v_dept varchar2(50);
begin
select dept into v_dept from dept where location = ‘XYZ’;
end;
Syntax
declare
v_dept varchar2(50);
Cursor Cur_dept is select dept into v_dept from dept where location = ‘XYZ’;
begin
Open Cur_dept;
Fetch Cur_dept into v_dept;
Close Cur_dept;
end;
All select/update query used in PL/SQL block is an implicit cursorExplicit cursor are open, fetch and closed

Question 8) What is rowid and rownum?
Answer
Rowid is a hexa decimal unique number for each row of a table which is used for searching.
Rownum is a unique integer number for each row of a table which can be used for sorting or analysis.
Question 9) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE
Answer
Function-A function has a return type in its specification and must return a value specified in that type.
-They are are stored in compiled form in database
– Functions can be called anywhere in an valid expression
Select fnd_profile.value(‘xyz)  from dual; 
Procedure-A procedure does not have a return type in its specification and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller
-They are are stored in compiled form in database
-Procedures are called as stand-alone executable statements:my_proc(parameter1,parameter2…);
Packages-Packages contain function, procedures and other data structures.
– The advantages of packages are Modularity, Easier Application Design, and Information.
Hiding, Reusability and Better Performance.
-There are a number of differences between packaged and non-packaged PL/SQL programs.
Package The data in package is persistent for the duration of the user’s session. The data in package thus exists across commits in the session.
-If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package.-You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.
Question 10)  Define Commit, Rollback and Save-point.
Answer.
CommitCommit is used to make the transaction permanent
Following things happens when commit is executed
a)All work done by the transaction becomes permanent.
b)Other users can see changes in data made by the transaction.
c)Any locks acquired by the transaction are released.
RollbackRollback is used to undo the transaction.
Following things happens when rollback is executed
a)All work done by the transaction is undone, as if it hadn’t been issued.
b) Any locks acquired by the transaction are released.
Save pointA SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Question 11) What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Answer
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
Question 12) How can you find within a PL/SQL block, if a cursor is open?
Answer
Use the %ISOPEN cursor status variable.
Question 13) How can you generate debugging output from PL/SQL?
Answer
Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
Question 14) What are PL/SQL exceptions?
Answer
Some of them are
1.Too_many_rows
2.No_Data_Found
3.Value_error
4.Zero_error etc.
Question15) What is the maximum number of triggers, you can apply on a single table?
Answer
12 triggers.
Question 16) What are two parts of package ?
Answer
The two parts are package specification and package body
They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package body contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.
Question 17) What is Overloading of procedures ?
Answer
Repeating of same procedure name with different parameter list
Question 18) What are the modes of parameters that can be passed to a procedure ?
Answer
IN, OUT, IN-OUT parameters.
Question 19)What command would you use to encrypt a PL/SQL application?
Answer
WRAP
Question 20) How many types of triggers exist in PL/SQL?
Answer
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
◦BEFORE ALL ROW INSERT
◦AFTER ALL ROW INSERT
◦BEFORE INSERT
◦AFTER INSERT etc.
Question 21) Name the tables where characteristics of Package, procedure and functions are stored?
Answer
dba_objects, dba_Source and dba_error.
or
user_objects,user_Source and user_error.
Question 22) What is stored Procedure?
Answer
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
Question 23) How to execute a stored procedure?
Answer
There are two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name
Question 24) What is Raise_application_error?
Answer
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.
Question 25)  What is Pragma EXECPTION_INIT? Explain the usage?
Answer
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)


1. Tell me about yourself
2. What is difference between TRUNCATE & DELETE?
1. Truncate is a DDL command
2. We can remove bulk amount of records at a time
3. We can't rollback the records
4. Release the space in database
5. Truncate reset the high water mark
6. Truncate explicitly commit
1. Delete is a DML command
2. We can delete record by record
3. We can rollback the records
4. Can’t release the memory in database
5. Delete can’t reset the water mark
6. Delete implicitly commit
(OR)
Ans: Differences:
TRUNCATE commits after deleting entire table i.e., cannot be rolled back.
Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion.
Deleted records can be rolled back or committed.Database triggers fire on DELETE.
3. Difference between view and materialized view
Difference
View is a logical table
View can hold the query
We can’t create indexes on view
View will create security purpose
Mv is a physical table
Mv can hold the query with refresh data
We can create indexes on mv
Mv will create performance issues
4. Difference between procedure and function?
Procedure:
Procedure allow the DML statements without any restrictions
We can’t call procedure in sql language
We can store images in stored procedure
Function:
Function not allow the DML statements (If you need to use we can use pragma)
We can call Function in sql language
Function can’t store images
5. What is cursor?
Cursor is private sql area which is used to execute sql statements and store processing information
6. What is explicit and implicit cursor and examples?
The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every implicit cursor attribute start with sql%.
An explicit cursor is created and managed by the user. And used for multi row select statement.
7.What do u understand by database and what is objects in oracle
Ans: A database is defined as a collection of meaningful data. Objects in oracle means Table, Views, Procedures, Triggers, Synonym etc
8.What is a table, view, snapshot?
Table: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.a
Views: A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Snapshot: A Snapshot is a recent copy of a table from database or in some cases ,a subset of rows/columns of a table. It is also known as Materialized view.
9.Do a view contain data?
Ans: Views do not contain or store data
What are the advantages of views?
Ans: Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.
10.What is an Oracle sequence?
Ans: A Sequence generates a serial list of unique numbers for numerical columns of a database's tables.
11.What is a synonym?
Ans: A synonym is an alias for a table, view, sequence or program unit.
12.What are the types of synonyms?
Ans: There are two types of synonyms private and public.
13.What is a private synonym?
Ans: Only its owner can access a private synonym.
14.What is a public synonym?
Ans: Any database user can access a public synonym
15.What is an Oracle index?
Ans: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. Index may also be considered as a ordered list of content of a column.
16.What is a schema?
Ans: The set of objects owned by user account is called the schema.
17.What is a join? Explain the different types of joins?
Ans: Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by not equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
18.Difference between SUBSTR and INSTR?
Ans: INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
19.What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
Ans: CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2
20.How to access the current value and next value from a sequence?
Ans: Current Value : Sequence name.CURRVAL
Next Value sequence name.NEXTVAL.
21.What are the components of physical database structure of Oracle database?
Ans: Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.
22.Query to delete duplicate row from a table
Ans: Delete from emp where rowid not in (Select min(rowid) from emp Groupby emp_dept)
23.What is a cursor its attribute and types?
Ans: The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL operation and is called Cursor.
Types of Cursor:
Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It is invoked implicitly.
Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is know as Explicit Cursor.
Attributes Of a Implicit Cursor:
%ISOPEN —returns TRUE if cursor is open else FALSE.
Syntax is SQL%ISOPEN
%ROWCOUNT--- returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE. Syntax is cursorname%ISOPEN %ROWCOUNT--- returns number of records processed from cursor syntax is cursorname %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is cursorname %NOTFOUND
24.What are inline views?
Ans: Inline view is Sub-query(queries written in a where clause of SQL statements.). It is a query whose return values are used in filtering conditions of the main query.
25.How can we refresh a snapshot?
Ans: Refreshing Snapshots: A snapshot can be refreshed automatically or manually. If a snapshot has to be automatically refreshed then refresh clause must be specified in the CREATE SNAPSHOT. The FAST, COMPLETE or FORCE specifies the type of REFRESH used for automatic refresh. For automatic refresh we can specify the START WITH and NEXT parameter to decide the time interval for the next update.
COMPLETE refresh: In complete refresh the snapshot query is executed and places the result in the snapshot.
FAST refresh : In this only the changes made to the master table will be updated to the snapshot. The corresponding log file is used to update. Fast refresh will be done only if * The snapshot is a simple snapshot. * The snapshot's master table has a snapshot log \ * The snapshot log was created before the snapshot was last refreshed or created.
FORCE refresh : In this ORACLE decides how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible it performs a fast refresh else it does a complete refresh.
26.What is a tablespace?
Ans: A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
27.Is sequence cyclic?
Ans: Yes
28.Select nth highest value from a list of values ?
Ans: SELECT a.emp_name,a.sal FROM emp a WHERE &n - 1= (SELECT COUNT(DISTINCT sal) FROM emp b WHERE b.sal > a.sal )
29.What are triggers and its types?
Ans: A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT
30.What is the maximum number of triggers, can apply to a single table?
Ans: 12 triggers(Oracle).
31.Difference between rowid and rownum?
Ans: ROWID is pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format BBBBBBBBB.RRRR.FFFF (block, row, file) FFFF is the fileid of the datafile that contains the row. BBBBBBBBB is the address of the datablock within the datafile that contains the row. RRRR is the ROW NUMBER with the data block that contains the row. They are unique identifiers for the any row in a table. They are internally used in the construction of indexes.
Rownum is the sequential number of rows in the result set object.
32.What is the fastest query method for a table?
Ans: By rowid
33.What is the difference of a LEFT JOIN and an INNER JOIN statement?
Ans: A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables
34.How can I avoid a divide by zero error?
Ans: Use the DECODE function. This function is absolutely brilliant and functions like a CASE statement, and can be used to return different columns based on the values of others.
35.Is view updatable?
Ans: Only if the view is a simple horizontal slice through a single table.
36.What is Dual ?
Ans: The DUAL table is a table with a single row and a single column used where a table is syntactically required.
37.What is the difference between CHAR and VARCHAR ?
Ans: CHAR is fixed length character type at storage level, and that VARCHAR will be variable length.
38.Do we use commit in triggers.
Ans: No
39.How will the fetch the last inserted record in any table ?
Ans: select column 1, column 2.... From where rowid = (select max(rowid) from table);
40.What are constraints and its types?
Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.
Types of integrity constraints : The following integrity constraints are supported by ORACLE:
1. NOT NULL : disallows nulls (empty entries) in a table's column
2. UNIQUE : disallows duplicate values in a column or set of columns
3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns
4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.
5. CHECK : disallows values that do not satisfy the logical expression of the constrain
41.What is Referential Integrity and Referential integrity constraint ?
Ans: Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
42.What is groups by and having clause? Explain with example
Ans: Group by clause tells oracle to group rows based on distinct values that exists for specified columns. The group by clause creates a data set , containing several sets of records grouped together based on condition.
Having Clause: Having clause can be used with GROUP BY clause. Having imposes a condition on the group by clause which further filters the group created by the GROUP BY clause. Select ename,empno From Empl Group by empno having empno > 10;
43.What are LOCKS? What are types of different types of Lock?
Ans: Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data. Locks are used to achieve two important database goals : Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. Integrity : Ensures that the database's data and structures reflect all changes made to them in the correct sequence.
Types of Locks :
1. Data Locks (DML)
2. Dictionary Locks (DDL)
3. Internal Locks and Latches
4. Distributed Locks
5. Parallel Cache Management Locks
Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level
1. Row Share Table Locks (RS)
2. Row Exclusive Table Locks (RX)
3. Share Table Locks (S)
4. Share Row Exclusive Table Locks (SRX)
5. Exclusive Table Locks (X)
Dictionary Locks :
1. Exclusive DDL Locks
2. Share DDL Locks
3. Breakable Parse Locks Restrictiveness of Locks : In general, two levels of locking can be used in a multi-user database: • Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released. • Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.
44.Difference between unique key,primary key and foreign key ?
Ans: Foreign key: A foreign key is one or more columns whose values are based on the primary or candidate key values from another table. Unique key can be null; Primary key cannot be null.
45.What are Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?
Ans: The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.
1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.
2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).
3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.
5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.
46.What are steps involved in Execution of SQL statements?
Ans: STEPS IN EXECUTION OF SQL STATEMENTS :
1. Create a cursor
2. Parse the statement
3. Describe Results
4. Defining outputs
5. Bind any variables
6. Execute the statement
7. Fetch rows of a query result
47.What do you mean by Parsing?
Ans: Parsing : Parsing is the process of: 1. Translating a SQL statement, verifying it to be a valid statement 2. Performing data dictionary lookups to check table and column definitions 3. Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing 4. Checking privileges to access referenced schema objects 5. Determining the execution plan to be used when executing the statement 6. Loading it into a shared SQL area 7. For distributed statements, routing all or part of the statement to remote nodes that contain referenced data
48.What is a HINT and what are types HINT?
Ans: Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.
TYPES OF HINTS :
ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.
FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.
FULL : The FULL hint explicitly chooses a full table scan for the specified table.
ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.
CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.
HASH : The HASH hint explicitly chooses a hash scan to access the specified table.
INDEX : The INDEX hint explicitly chooses an index scan for the specified table.
AND_EQUAL: The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. (You can specify multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.
INDEX_DESC: The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.
ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.
USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loops join using the specified table as the inner table.
USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.
49.What do u mean by EXCEPTION_INIT Pragma ?
Ans: EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called "pseudoinstructions") are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler. The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package
using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where "exception_name" is the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is "no data found," in which case SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
What do u mean by JSP query?
Ans: JSP Query : The JSP Query is a standard query for number to words conversion, used especially for converting amount in number into equivalent amount in words. The query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ), ‘JSP’ ) words from dual; For eg : Select to_char ( to_date ( '23949','j' ), 'JSP' ) "words" from dual; The value that can pass to &no cannot exceed 7 digits.
50.Describe Oracle database’s physical and logical structure ?
Ans: Physical: Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.
51.What is “Check Constraints” and “with check options” and “Default Specification”?
Ans: CHECK Integrity Constraints: A CHECK integrity constraint on a column or a set of columns requires that a specified condition be true or unknown (ie. Not false) for every row of the table. If a DML statement is issued so that the condition of the CHECK constraint evaluates to false, the statement is rolled back. With check Option: With Check option restricts inserts and updates performed through the view to prevent them from creating rows that the view cannot itself select .based on where clause of the create view statement. For eg: Create or replace view Women As select name from Employee Where Sex= ‘Female’ With Check Option; Default Specification It supplies a default value if column value is not specified on INSERT It can contain literals (constants) and SQL functions, USER, SYSDATE, sequence It cannot include references to any columns.
52.What is the maximum no. Of columns a table can have ?
Ans: 254(Oracle)
53.Can a trigger written for a view ?
Ans: No
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
Ans: 3
54.Can you create index on view ?
Ans: No
55.What is the difference between alias and synonym ?
Ans: Alias is temporary and used with one query. Synonym is permanent and not used as alias.
What’s the length of SQL integer ?
Ans: 32 bit length
56.What is tkprof and how is it used?
Ans: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool . This can also be used to generate explain plan output.
57.What is explain plan and how is it used?
Ans: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
58.What is The Dynamic Performance Tables?
Ans: Throughout its operation, ORACLE maintains a set of "virtual" tables that record current database activity. These tables are called Dynamic performance tables. Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users. The dynamic performance tables are owned by SYS and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.
59.What is Savepoint ?
Ans: Savepoints are intermediate markers that can be declared in long transactions that contain many SQL statements. By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
60.What is Deadlocks?
Ans: A deadlock is a situation that can occur in multi-user systems that causes some number of transactions to be unable to continue work. A deadlock can occur when two or more users are waiting for data locked by each other. It typically happens when each of two or more users are waiting to access a resource that another user has already locked. This creates a deadlock situation because each user is waiting for resources held by the other user. Eg Transaction 1 Time Point Transaction 2 UPDATE emp 1 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 1000; WHERE empno = 2000; UPDATE emp 2 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 2000; WHERE empno = 1000; ORA-00060 3 deadlock detected while waiting for resource
61.What is Privilege ?
Ans: A privilege is a right to execute a particular type of SQL statement or to access another user's object. Types of privileges : • system privileges • object privileges System Privileges : System privileges allow users to perform a particular systemwide action, or to perform a particular action on a particular type of object. E.g. Create Tablespace, Delete the row of any table, etc. Object Privileges : Object privileges allow users to perform a particular action on a specific object. E.g. Delete row of specific table, etc. Roles : Roles are named groups of related privileges that are granted to users or other roles. Advantages of Roles : 1. Reduced granting of privileges 2. Dynamic privilege management (Changing of privileges) 3. Selective availability of privileges (Enalbling/Disabling roles) 4. Application awareness (Enalbling/Disabling of roles by application)
62.What is Two Phase Commit ?
Ans: Two Phase Commit is a mechanism wherein ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database. The Phases of the Two-Phase Commit Mechanism :
• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.
63.Explain about snapshots in detail?
Ans: Snapshots are read-only copies of a master table (or multiple tables) located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. A snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. Simple vs. Complex Snapshots : Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot. Internals of Snapshot Creation: When a snapshot is created, several operations are performed internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store the rows retrieved by the snapshot's defining query; this is the snapshot's base table. • ORACLE creates a read-only view on the SNAP$ table (base table) for queries issued against the snapshot. • ORACLE creates a second local view on the remote master table. It uses this view when it refreshes the snapshot. • Additionally, if the snapshot is a simple snapshot, ORACLE creates an index on the SNAP$ table. All of these internal objects are created in the schema of the snapshot. Do not alter, change data in, or delete these objects manually.
64.What is Ref Cursor?
Ans: A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
65.What is row chaining, how does it happen?
Ans: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
66.Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
Ans: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. An instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
67.What is a Cartesian product?
Ans: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
68.What is a mutating table error and how can you get around it?
Ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
69.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Ans: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
70.What are Transactional Triggers ? Give the uses of Transational Trigger ?
Ans: Transactional Triggers fire in response to transaction processing events. These events represent points during application processing at which Oracle Forms needs to interact with the data source. Examples of such events include updating records, rolling back to savepoints, and committing transactions. By default, Oracle Forms assumes that the data source is an ORACLE database, and issues the appropriate SQL statements to optimize transaction processing accordingly. However, by defining
transactional triggers and user exits, you can build a form to interact with virtually any data source, including even non-relational databases and flat files. Calling User Exits When you define transactional triggers to interact with a non-ORACLE data source, you will usually include a call to a user exit in the appropriate triggers. The code in your user exit interacts with the non-ORACLE data source. Once the user exit has performed the appropriate function (as indicated by the trigger from which it was called), it returns control to Oracle Forms for subsequent processing. For example, a user exit called from an On-Fetch trigger might be responsible for retrieving the appropriate number of records from the non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over the display and management of those records in the form interface, just as it would if the records had been fetched from an ORACLE database. Uses for Transactional Triggers • Transactional triggers, except for the commit triggers, are primarily intended to access certain data sources other than Oracle. • The logon and logoff transactional triggers can also be used with Oracle databases to change connections at run time.
71.What is Autonomous transaction ? Where do we use it?
Ans: In Oracle's database products, an autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will be tracked for auditing purposes. When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database. Autonomous transactions can be nested. That is, an autonomous transaction can operate as a calling transaction, initializing other autonomous transactions within itself.
72.What is a package, procedure and function?
Ans: Package : A package is a group of related program objects stored together as a unit in the database. A package is an encapsulated collection of related program objects stored together in the database. Program objects are: procedures, functions, variables, constants, cursors, exceptions. Procedure/Function : A procedure or function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task. The main difference between a procedure and function is functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
73.What do u mean by overloading?
Ans: Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.
74.What are the constructs of a procedure, function or a package ?
Ans: The constructs of a procedure, function or a package are : • variables and constants • cursors • exceptions
75.What are cascading triggers? What is the maximum no of cascading triggers at a time?
Ans: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32
76.What is the significance of the & and && operators in PL/SQL ?
Ans: The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
77.If all the values from a cursor have been fetched and another fetch is issued, the output will be?
Ans: Last Record
78.What is a forward declaration ? What is its use ?
Ans: PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
79.Any three PL/SQL Exceptions?
Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
80.Describe the use of %ROWTYPE and %TYPE in PL/SQL
Ans: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
81.How can you call a PL/SQL procedure from SQL?
Ans: By use of the EXECUTE (short form EXEC) command.
82.What are the various types of Exceptions ?
Ans: User defined and Predefined Exceptions.
83.What is RAISE_APPLICATION_ERROR ?
Ans: DBMS_STANDARD provides a procedure named raise_application_error, which lets you issue user-defined error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. The calling syntax is : raise_application_error(error_number, error_message); where error_number is a negative integer in the range -20000...-20999 and error_message is a character string up to 2048 bytes in length. An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application. The error number and message can be trapped like any ORACLE error. The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. • The statement Raise_Application_Error can be called either from a procedure body or from an exception handler. • Irrespective of whether an error occurred or not, a raise_application_error command always raises an exception in the calling program (eg a forms trigger). If an exception handler is not written in that forms trigger, then a forms error occurs.





What are Schema Objects?

Schema objects are tables, views, sequences, synonyms, indexes,databases triggers, procedures, functions, packages.

What is a Table?

The table is collection inforamation.A table is the basic of rows and columns.data’s are stored rows and columns.

What is a View?

A View is a virtual table.Not a physical table.
representation of one or more tables
Every view has a query attached to it.

What is an Index?

An index is uset to performace tunnig in queries. which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

What is a Data file?

A database’s data files contain all the database data. The data of logical database structures is tables and indexes is physically stored in the data files allocated for a database.

What is the difference between UNIQUE or PRIMARY KEY Constraint?

A column defined as PRIMARY KEY can contain unique values
same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.

What is PL/SQL ?

PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching..

What is the basic structure of PL/SQL ?

PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.

What is the difference between %ROWTYPE and TYPE RECORD ?

%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different table or views and variables.

What is a cursor ? Why cursor is required ?

Cursor is a named private sql area from where information can be passed and excuted or accessed. Cursors are required to process rows individually for queries returning multiple rows.

Explain the two types of cursors ?

Implicit cursors
Explicit cursors.
For loop cursors.
PL/SQL uses Implicit cursors for queries.
User defined cursors are called explicit cursors.
User using Cursors in for loop the Cursor called for loop Cursor.

What is Raise_application_error ?

Raise_application_error is a procedure of package DBMS_STANDARD. that allows to user_defined error or error code and error messages from stored sub program or database trigger.

What is the difference between a PROCEDURE and FUNCTION ?

A PROCEDURE may return one or more values or may not return at all.
A FUNCTION must returns a value using the return statement.


What is Overloading of procedures ?

The same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

What is a package ? What are the advantages of package ?

database object that groups logically related procedures.
The advantages of packages are modularity, easier application design, information hiding, reusability and better performance

What is difference between TRUNCATE and DELETE?

Truncate is Dml Statements. Cannot be rolled back. Delete allows the filtered deletion.
Deleted records can be rolled back or committed.


What is a join? Explain the different types of joins?

Join is a query,retrieves data from related columns or rows from multiple tables.
Self join – Joining the table with itself
Equi Join – Joining two tables by equating two common columns
Non Equi Join – Joining two tables based on conditions other than equating two common columns
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have the corresponding join value in the other table.

What is a Subquery?

Subquery is a query embedded with another Query
Outer Query based on the Inner Query

What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.

Difference between SUBSTR and INSTR?

INSTR(string1, string2 [,n,[m]])
its find the position of the string
SUBSTR(string1, n, m)
SUBSTR returns a character string of size m in string 1, starting from nth position of string1.

Explain UNION, MINUS, UNION ALL, INTERSECT?

INTERSECT returns all commmon rows selected by both queries.
MINUS returns all distinct rows selected by the first query but not by the second.
UNION returns all unique rows selected by either query.
UNION ALL returns all rows selected by either query, included all duplicates.

What are the Usages of SAVEPOINTS?

SAVEPOINTS are Transaction control Language.
its used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction.
Maximums of five save points are allowed.

What is ROWID?

ROWID is a pseudo column attached to each row of a table.
It is 18 characters long, blockno. Rownumber.filenumber are the components of ROWID.

What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?

PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

What is ON DELETE CASCADE?

ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

What is difference between CHAR and VARCHAR2? What is maximum SIZE allowed for each type?

CHAR pads spaces to the maximum length. VARCHAR2 does not pad blank spaces.
For CHAR it is 255 and 2000 for VARCHAR2.

How to store the results of a query into a file?

The results of a query can be stored into a file by spooling into a file.
Eg. SPOOL filename.

Which two statements about subqueries are true? (Choose two.)

A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its subquery,
unless table B is included in the main query’s FROM clause.
C. A SQL query statement can display data from table B that is referred to in its subquery,without including table B in its own FROM clause.
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Which three functions can be used to manipulate character, number, or date column values? (Choose three.)

A. CONCAT
B. ROUND
C. TRUNC
D. RPAD
E. INSTR

Which statement about a table is true?

A. A table can have up to 10,000 columns.
B. The size of a table does NOT need to be specified.
C. A table CANNOT be created while users are using the
database.
D. The structure of a table CANNOT be modified while the table is online.

What will be returned from SIGN(ABS(NVL(-32,0)))?

A. 1
B. 32
C. 1
D. 0
E. NULL

Which functions could you use to strip leading characters from a character string. Select two.

A. LTRIM
B. SUBSTR
C. RTRIM
D. INSTR
E. MOD

All the operators are used in single row subquery except one

a) Between … and
b) < >
c) =
d) in

All the commands executes in iSQLplus except one

a) Column
b) Compute
c) define
d) Accept

Ascript file which will be executed automatically in iSQLPlus is

a) afiedt.buf
b) login.sql
c) both a and b
d) none of the above.

A command in iSQL plus is used to give the status of old and new value of variable

a) set feedback
b) set verify
c) set confirm
d) none of the above

All commands are used to save the changes of the transaction except one

a) Commit
b) exitting from sqlplus
c) DDL command
d) savepoint
e) none of the above

A Clause which is used in joining two tables other than equality operator is

a) join
b) on
c) in
d) using

A Clause which is the pseudocolumn used to know the current value of the sequence

a) nextval
b) current_val
c) currval
d) none of the above

A Query which is used in top-N analysis is

a) subquery
b) correlated subquery
c) inline query
d) outer query

An operator is used to get and display the redundant records

a) Union all
b) Distinct
c) Union
d) Intersect

All the datatypes with respect to Oracle 9i is true except one

a) DATE
b) TIMESTAMP
c) TIMSTAMP with TIME ZONE
d) TIMESTAMP WITH LOCAL TIME ZONE
e) None of the above

What is database?

A database is a logically collection of data with some inherent meaning.the data’s stored rows and collumns
database are used to store the informations.

To select records from the given row?

select * from SIVAEMP where rownum < =(select count(*)-&n from SIVAEMP);

To check the leap year

select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual

To delete duplicate records

delete from sivaemp where rowid not in(select max(rowid) from sivaemp group by empno)

To keep latest single record

delete from sivaemp where rowid not in(select min(rowid) from sivaemp group by empno)//to keep oldest record

To select second max salary

select * from sivaemp where salary = (select max(salary) from sivaemp where salary not in (select max(salary) from sivaemp))

To select nth max salary

select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary < b .salary)
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary < =b.salary)

To select nth min salary

select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary > b.salary)
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary > =b.salary)

To select top(n) max salaries

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary < b .salary)

To select top(n) min salaries

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary > b.salary)

To select records from particular row(the remaining rows)

select * from sivaemp minus select * from sivaemp where rownum < = (select count(*)- &n-1 from sivaemp )

to select first n rows

select * from sivaemp where rownum < = (select count(*) + &n -count(*) from sivaemp)

Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur when DML operations are occur Trigger is Fire.
Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Differences between DATE and TIMESTAMP in Oracle

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:

Create a copy of EMP table without any data?

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE‘CHIRU’=’VENKY’;

Delete the 10th record of EMP table?

DELETE FROM EMP WHERE ENAME= (SELECT ENAME FROM EMP WHERE ROWNUM < =10 MINUS SELECT ENAME FROM EMP WHERE ROWNUM<10);

Find all the departments which have more than 3 employees?

SELECT D.DNAME, COUNT (E.ENAME) FROM EMP E, DEPT D WHERE D.DEPTNO=E.DEPTNO GROUP BY DNAME HAVING COUNT (EMPNO) > 3;

Display the manager who is having maximum number of employees working under him?

SELECT DISTINCT M.ENAME, COUNT (E.ENAME)
FROM EMP E, EMP M WHERE E.MGR=M.EMPNO
GROUP BY M.ENAME HAVING COUNT (E.ENAME) > =ALL
(SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME);

Display the name of employees who joined on the same date?

SELECT A.ENAME FROM EMP A, EMP B
WHERE A.EMPNO < > B.EMPNO AND A.HIREDATE=B.HIREDATE;

Delete those employees who joined the company 10 years back from today?

SELECT ENAME FROM EMP
WHERE (SYSDATE-HIREDATE)/365 > 10

Display those employees whose salary is ODD value?

SELECT ENAME, SAL FROM EMP
WHERE MOD (SAL, 2) < > 0;

Find out the number of employees whose salary is greater than their manager salary?

SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO AND E.SAL > M.SAL

Select the count of employees in each dept where count is greater than 3?

SELECT JOB, COUNT (*) FROM EMP
GROUP BY JOB HAVING COUNT (*) > 3;

Display name of those employees who are getting the highest salary?

SELECT * FROM EMP WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

What is a transaction ?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

What is implicit cursor and how is it used by Oracle ?

An implicit cursor is user pass single line queries that time a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?

Public synonyms

Is there a PL/SQL Engine in SQL*Plus?

SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Can one read/write files from PL/SQL?

Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.
Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

How can I protect my PL/SQL source code?

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap name=myscript.sql oname=xxxx.yyy

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’,
DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

What are the various types of parameter modes in a procedure ?

IN, OUT AND INOUT.

What are the constructs of a procedure, function or a package ?

The constructs of a procedure, function or a package are :variables and constants
cursors
exceptions

Why Create or Replace and not Drop and recreate procedures ?

So that Grants are not dropped.

Can you pass parameters in packages ? How ?

Yes.You can pass parameters to procedures or functions in a package.

What are the parts of a database trigger ?

A triggering event or statement
A trigger restriction
A trigger action

What are the various types of database triggers ?

There are 12 types of triggers
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.

What is the advantage of a stored procedure over a database trigger ?

We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What are cascading triggers? What is the maximum no of cascading triggers at a time?

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

What are mutating triggers ?

A trigger giving a SELECT on the table on which the trigger is written.

What are constraining triggers ?

A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

Describe Oracle database's physical and logical structure ?

Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

Can you increase the size of a tablespace ? How ?

Yes, by adding datafiles to it.

What is the use of Control files ?

Contains pointers to locations of various data files, redo log files, etc.

What is the use of Data Dictionary ?

It Used by Oracle to store information about various physical and logical structures e.g.Tables

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

The various states of a rollback segment are :
ONLINE
OFFLINE
PARTLY AVAILABLE
NEEDS RECOVERY
INVALID.

What is the maximum no.of columns a table can have ?

254.

Can you pass a parameter to a cursor ?

Explicit cursors can take parameters,User create cursor called Explicit Cursor
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

What are the various types of RollBack Segments ?

The types of Rollback sagments are as follows :
Public Available to all instances
Private Available to specific instance

What are the disadvantages of SQL ?

Cannot drop a field
Cannot rename a field
Cannot manage memory
Procedural Language option not provided
Index on view or index on index not provided
View updation problem

When to create indexes ?

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

How can you avoid indexes ?

To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another

What is the difference between a view and a synonym ?

Synonym is Schema Object,just a second name of table,.View can be created with many tables, and with virtual columns and with conditions.

What is the difference between foreign key and reference key ?

Foreign key is attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

What is a package cursor ?

a cursor declare in the package specification without an SQL statement.
The SQL statement for the cursor is attached at runtime from calling procedures.

What are snap shots and views

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.




6 comments:

  1. taranstechJune 4, 2021 at 4:35 AM

    This comment has been removed by the author.

    ReplyDelete
  2. AnonymousJuly 27, 2021 at 10:22 PM

    Nice post devops online training

    ReplyDelete
  3. taranstechOctober 11, 2021 at 12:26 AM

    Hi,
    Hi read your whole article on Oracle DBA. This blog is too long but this is really worth to read it. As a student I got many information that I was looking for. Thank you for you informative blog.

    ReplyDelete
  4. upGrad CampusMay 28, 2023 at 1:00 PM

    Nice Post!!
    Please look into this-
    Are you ready to unlock your potential and take your career to new heights? Look no further than the Business Analyst Course at upGrad Campus. Designed for ambitious professionals like you, this comprehensive program equips you with the skills and knowledge needed to excel in the dynamic field of business analysis.

    ReplyDelete
  5. kalpanaSeptember 5, 2023 at 7:04 AM

    Nice interview Question answer collection with Oracle apps thanks for sharing

    ReplyDelete
  6. Anny TechOctober 10, 2023 at 5:39 AM

    I recently embarked on my journey to become an AWS Certified Solutions Architect, and your blog has provided me with valuable tips and guidance. It's reassuring to know that others have successfully navigated this path. Please visit our website:- aws solution architect

    ReplyDelete

Subscribe to: Posts (Atom)
  • Oracle Apps AR Interview Questions
    Oracle Apps AR Module Interview Questions 1 Name some of the Oracle receivables Interfaces? a) Auto Invoice b) Auto Lockbox c) Custome...