Here are the list of most frequently asked ORACLE PL/SQL Interview Questions in technical interviews
1.Difference between Varchar and Char ?
Varchar doesn’t sets aside memory location during declaration of a variable. It stores the value only after a variable is defined or assigned a value. Its storage capacity is 32767 Bytes.
Char however preserves the memory location mentioned in the variable declaration even if it is not used. The maximum storage capacity for a Character variable is 255 Bytes.
2.what is % ROWTYPE ?
% ROWTYPE is used when a query returns an entire row of a table or view.
3. what is TYPE RECORD ?
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views
4. How can you enable or disable a trigger?
To Enable a Trigger, the Syntax is: Alter Trigger Trigger_Name Enable;
To Disable a Trigger, the Syntax is: Alter Trigger Trigger_Name Disable;
5. 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 you are good to go.
6. How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
7. difference between Truncate and Delete?
Truncate is much faster than Delete Command. It basically resets the Memory Blocks after Execution.
Delete is a Database Manipulation Language (DML) Command whereas Truncate is a Data Definition Language (DDL) Command and it is comparatively slower.
8. Explain TTITLE & BTITLE ?
TTITLE & BTITLE commands that control report headers and footers.
9. Difference between UNION and UNION ALL?
Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
UNION ALL does not remove duplicates, and it therefore faster than UNION.
10. Difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed