 |
PL stands for Procedural Language which is an extension to SQL where the developer writes the complex database interaction by using the procedures, control structures like branching and iteration, modules and functions. it supports both dynamic and static SQL.
In this article, you can go through the set of Oracle PL/SQL interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR training.
Let us have a quick review of the Oracle PL/SQL interview questions.
Ans:
SQL:
PL/SQL:
Become an Oracle PL SQL Certified professional by learning this HKR Oracle PL SQL Training!
Ans: The benefits of PL/SQL Packages are.
Ans:
Ans: Tracing the code is a technique that measures performance during the runtime. The different methods for tracing the code includes the following:
Ans: Yes. The IF statement is used as a DECODE in versions 9 and above.
Example:
SELECT day_of_week,
DECODE (number, 0, 'Sunday',
1, 'Monday',
2, 'Tuesday',
3, 'Wednesday',
4, 'Thursday',
5, 'Friday',
6, 'Saturday',
'No match') result FROM weekdays;
Ans:
%TYPE:
It is the attribute that declares a variable of the same data type as of a table column.
Example:
DECLARE
studentId
students.student_id%TYPE;
%ROWTYPE:
It is the attribute that declares a variable of type RECORD which has the same structure as that of a table row. The row is a RECORD that contains fields having the same data types and names as that of columns of a table or view.
Example:
DECLARE
Stud_rec
students.%ROWTYPE;
Ans: The trigger is a stored procedure which invokes automatically when an event occurs. The event could be any of these DML commands like insert, update, delete, etc.
Syntax:
create trigger [trigger_name]
[before | after]
on [table_name]
[for each row]
[trigger_body]
Ans: There are two different types of cursors available in PL/SQL.
Implicit cursor: PL/SQL applies these implicit cursors for the DML commands such as INSERT, UPDATE, DELETE and SELECT statements which return a single row.
Explicit cursor: It is created by the programmer for queries that return more than a single row.
Syntax:
CURSOR is
SELECT statement;
OPEN ;
FETCH INTO ;
CLOSE ;
Ans:
Ans: The outcome of the statement is saved in 4 cursor attributes. They are.
Ans:
Ans: Comments are the text which is used while implementing the code to enhance the readability such that a reader is able to understand the code. These comment codes are not executed. There are two types of comments in PL/SQL.
Single line comment: This comment starts with double “–”.
Example:
DECLARE
num NUMBER(2); — it is a local variable.
BEGIN
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
Ans: The three modes of parameters are.
Ans:
Actual parameters:
The actual parameters are the variables or an expression which is referred to as parameters that appear in the procedure call statement.
Example:
raise_sal(emp_num, merit+amount);
In this above example, “emp_num” and “amount” are the two actual parameters.
Formal parameters:
The formal parameters are the variables which are declared in the procedure header and are referenced in the procedure body.
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;
In the above example, “emp_id” acts as a formal parameter.
Ans: In PL/SQL, the exception is raised when an error occurs. In other terms, to handle unpredicted events such as when the PL/SQL scripts are terminated unexpectedly than an error-handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section. There are three types of Exception Handling in PL/SQL.
Ans: A View is a logical representation of data subsets from one or more tables. It is a logical table that is based on a SQL table or another view. A view doesn’t contain the 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.
The views are used for the following purposes.
Ans:
Example:
CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)
AS
BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;
Ans:
DECLARE
n NUMBER;
avg NUMBER :=0 ;
sum NUMBER :=0 ;
count NUMBER :=0 ;
BEGIN
n := &enter_a_number;
WHILE(n<>0)
LOOP
count := count+1;
sum := sum+n;
n := &enter_a_number;
END LOOP;
avg := sum/count;
DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
END;
Ans:
Select * from emp where sal= (select max(sal) from emp)
Ans: Select max(sal) from emp where sal not in (select max(sal) from emp)
Want to know more about Oracle PL/SQL, visit here Oracle PL/SQL Tutorial!
Ans:
Function: A function is specified with a return type and must return a value specified in that type. The main purpose of a PL/SQL function is to compute and return a single value.
Procedure: A procedure doesn’t have a return type and it doesn’t return any value, but it can have a return statement which stops its execution and returns to the caller. A procedure returns multiple values, unlike a general function.
Package: A package is a group of functions, procedures, variables, and record TYPE statements. It is a schema object which groups logically related PL/SQL types, items, and subprograms. It provides modularity for application development. It is used for hiding the information from unauthorized users.
Ans:
SET SERVER OUTPUT ON
DECLARE
BEGIN
FOR i IN REVERSE 1..99
LOOP
IF Mod(i,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
Ans:
Triggers:
Constraints:
Ans:
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;
/
Ans: The PLV msg allows for.
Ans: PL/Vision offers two packages for managing the transaction processing in PL/SQL. It is PLVcmt and PLVrb.
PLVcmt: This package wraps the logic and complexity to deal with commit processing.
PLVrb: This package provides a programmatic interface to roll-back activity in PL/SQL.
Ans: Use the J format string.
SQL > select to_char(to_date('29-Mar-2013','dd-mon-yyyy'),'J') as julian from dual;
JULIAN
Ans: 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
Ans: The spool command is used for printing the output of SQL statements in a file.
spool/tmp/sql_outtxt
select emp_name, emp_id from emp where dept='accounts';
spool off;
Ans:
PLVprs: It is an extension for string parsing for PL/SQL, and the functionality of string parsing works at the lowest level.
PLVprsps: It is the highest level package used in parsing the PL/SQL source code into separate atomics. It relies on other parsing packages to get the work done.
Ans: Literals in PL/SQL are the specific string, character, numerical, or Boolean values not constituted by an identifier. Below are the different types of literals that PL/SQL supports:-
Ans: The date function runs on the values of the date data types, and they return the value of the data type "DATE". A character function accepts one or more character values as input and returns both characters and numbers as values. The character function has different types, such as UPPER, LOWER, CONCAT, INSTR, LENGTH, TRIM, INTICAP, etc. Number functions in PL/SQL take the Numeric as input, and further, it gives back the numeric values. These functions include ROUND, MOD, POWER, CEIL, TRUNC, etc.
Ans: In PL/SQL, JOIN is a query that combines rows from two or more tables, views, or materialized views. Oracle Database executes a Join query when different types of tables occur within the FROM clause of the query.
Ans: A VERIFY command is helpful to verify or validate the changes in the SQL statement, and we can define it by SET VERIFY On or OFF.
Ans: The FEEDBACK command in PL/SQL shows the total number of records returned by a query.
Ans: A mutating table in PL/SQL is a table which can be modified or it is changing. We can modify it using the DML, update it, or insert a statement. A constraining table is a type of table helpful to read for referential integrity constraints.
Ans: An error is a kind of bug where an exception is an error condition which is user-defined or internally defined. Exception raises where an error occurs.
Ans: A mutating table error takes place when a trigger tries to modify or update a row which is currently in use. Also, we can fix this error using temporary tables or views.
Ans: A rollback statement is just the opposite of a commit supplied when the transaction comes to an end. It undoes all the database changes made during the existing transaction process.
Ans: The following are the various PL/SQL Data Types:-
Ans: A Dual Table in PL/SQL is automatically built by the Oracle DB while creating a data dictionary. The user SYS owns this table, and all the users can access this table. Moreover, we can use this table when we wish to return a value only once.
Ans: A Subquery is a query inside a query or a SELECT statement nested within another statement. We can place a subquery within a WHERE HAVING and the FROM clause. There are two types of Subqueries: Single row subquery and Multiple row subquery. Also, there is another type of subquery, such as correlated subquery.
Ans: The command TITLE denotes the top title, and the BTITLE represents the bottom title in PL/SQL. Using these commands, we can set the title for the top of the page and the bottom of the page.
Ans: In general, PL/SQL uses the block structure where a PL/SQL program includes SQL and PL/SQL statements. The statements develop a PL/SQL block. Further, the PL/SQL block comprises three sections - declaration, execution, and exception handling.
Ans: To delete or remove a trigger, we use the command - DROP TRIGGER.
Ans: The "WHEN" clause in trigger states a condition on which a trigger has to be triggered.
Ans: A trigger in PL/SQL is similar to the stored procedure that automatically invokes when we perform DML operations against a view or table. There are two different triggers that PL/SQL supports- Row Level Trigger and Statement Level Trigger.
The row level trigger performs once for each row within the transaction. A Statement level trigger performs only once for a single transaction.
Ans: A Stored Procedure is a series of SQL statements executing specific functions. It is similar to other programming languages' procedures which are saved within a database and can perform frequently. Further, we can invoke them using triggers or other methods as well.
Ans: In PL/SQL, SYSDATE is a keyword that means the current server system date. It returns the current date and time set for the OS where the database server exists. The USER keyword is a pseudo column that returns an existing user's login ID for the current session.
Ans: The following are the basic components of a Trigger:-
Ans: A Syntax error is an error that a compiler or an interpreter quickly detects.
Ans: We can use the command DROP PACKAGE to remove the package in PL/SQL.
Ans: The compilation process in PL/SQL includes syntax checking, binding, and P-code generation. While compiling, the syntax checking verifies the errors within PL/SQL code. After fixing all the errors/mistakes, we allot a storage address to the variables holding data which is a Binding process. P-code consists of a set of instructions from the PL/SQL engine. This code is stored within the database and executed the next time it comes to use.
Ans. In PL/SQL, global variables are stated within the farthest block or a package. These variables can store a maximum length of 255 characters of the character string. Moreover, we cannot formally declare global variables similar to the local ones.
Ans: In PL/SQL, Ref Cursor is a data type whose value is the memory location of a database query workspace. Two types of Ref Cursors are available- Strong and Weak Ref Cursors.
Ans: The PL/SQL package includes the following:-
Ans: This package procedure enables us to send a user-defined error message from a stored program/block. It raises an exception based on the error provided by the user. We can report an error to the caller using this process rather than returning unhandled exceptions.
Ans: An overloading procedure repeats the same name but with different parameter types. In Oracle, we can also apply an overloading procedure using a package.
Ans: Database links are helpful to build communication across different databases or environments like test, production, development, etc. We can also access any other information through database links as read-only. In other words, it is like a schema object within a database that allows users to access another database's objects.
Ans: It is a statement that unknown blocks use under PL/SQL as a stand-alone procedure. While using them, the statement within the stand-alone file will come first. Moreover, there is a need for a declaration section in case any variables are used within the PL/SQL block.
Ans: A discard file is a file that includes records not placed into any table within a database. The extension for a discard file is .dsc, and we need to state the discard file through the discard file clause.
Batch starts on 9th Feb 2023, Weekday batch
Batch starts on 13th Feb 2023, Weekday batch
Batch starts on 17th Feb 2023, Fast Track batch
28th February | 07:00 pm
2 Registered
©2023 HKR Trainings. All rights Reserved.