Tell Me Something About Pl Sql You Know Interview Questions and Answer

Welcome to Mindmajix - The leading global online training platform.  If you're looking for Oracle PL SQL Interview Questions or Freshers, y'all are in the right place.

In that location are a lot of opportunities from many reputed companies in the world. Co-ordinate to research, Oracle PL SQL has a market place share of nearly ii.2% . So, You however take the opportunity to move ahead in your career in Oracle PL SQL Evolution.

Mindmajix offers Advanced Oracle PL SQL Interview Questions 2022 that assist y'all in cracking your interview & larn a dream career as an Oracle PL SQL Developer. Hither are frequently asked Oracle PL SQL Interview Questions, permit's take a await into them.

We have categorized Oracle PL SQL Interview Questions - 2022 (Updated) into 3 levels they are:

• For Beginners
• For Developers
• For Experienced

Summit x Frequently Asked Oracle PL SQL Interview Questions

1. What is the Difference Between Pl SQL and SQL?
2. What is an Alias in SQL Statements?
3. What is a Dual Table?
4. What is a Merge Argument?
5. What is the Difference Between the "verify" And the "feedback" Command?
6. Write a Pl/SQL Plan that Raises a User-defined Exception On Thursday?
7. What are Triggering Events (Or) Trigger Predicate Clauses?
8. What is Invalid_number, Value_error?
9. What is Flashback Query?
10. Explain Different Methods to Trace the Pl/SQL Lawmaking?

If yous want to enrich your career and become a professional in Oracle PL SQL, then enroll in "Oracle PL SQL Training" - This grade will aid yous to achieve excellence in this domain.

Oracle PL SQL Interview Questions for Beginners

ane) What is the difference between PL SQL and SQL?

Comparison	SQL	PL/SQL
Execution	Unmarried command at a time	Block of lawmaking
Application	Source of data to be displayed	Application created by data acquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing Grime operations on information	Creating applications to display data obtained using SQL
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

2) 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 admission, manipulate, and control information.

There are 5 dissimilar types of SQL statements.

1. Data Retrieval: SELECT

2. Information Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE

3. Information Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.

4. Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT

5. Data Manipulation Language (DCL): GRANT, REVOKE

iii) What is an alias in SQL statements?

Alias is a user-defined alternative name given to the column or table. By default column, allonym headings appear in upper example. Enclose the alias in double quotation marks (" ") to make it example-sensitive. "AS" Keyword before the alias proper noun makes the SELECT clause easier to read.

For example Select emp_name Equally proper noun from employee; (Here AS is a keyword and "proper name" is an alias).

iv) What is a Literal? Give an case of where it can be used?

A Literal is a string that can contain a graphic symbol, a number, or a date that is included in the SELECT list and that is not a column proper name or a column alias. Date and grapheme literals must exist enclosed within single quotation marks (' '), number literals need not.

For exp: Select last_name||' is a'||job_id As "emp details" from the employee; (Hither "is a" is a literal).

5) What is the difference between SQL and iSQL*Plus?

SQL	iSQL*Plus
Is a Language	Is an Surround
Character and engagement cavalcade headings are left-justified and number column headings are right-justified.	Default heading justification is in the Centre.
Cannot be Abbreviated (short forms)	Can exist Abbreviated
Does not take a continuation graphic symbol	Has a nuance (-) equally a continuation character if the command is longer than one line
Employ Functions to perform some formatting	Use commands to format information

Subscribe to explore the latest tech updates, career transformation tips, and much more.

6) Define the lodge of Precedence used in executing SQL statements?

Order Evaluated	Operator
1	Arithmetic operators (*, /, +, -)
2	Chain operators (||)
3	Comparison weather condition
4	Is[NOT] Zip, Like, [Non] IN
v	[Non] Betwixt
6	Not Logical condition
7	AND logical condition
8	OR logical condition

seven) What are SQL functions? Describe in cursory different types of SQL functions?

SQL Functions are a very powerful feature of SQL. SQL functions can take arguments only ever return some value.ane
There are ii singled-out types of SQL functions:

one) Unmarried-Row functions: These functions operate on a single row to requite i result per row.

Types of Unmarried-Row functions:

1. Character
2. Number
3. Engagement
4. Conversion
5. General

2) Multiple-Row functions: These functions operate on groups of rows to give 1 result per group of rows.

Types of Multiple-Row functions:

1. AVG
2. COUNT
3. MAX
4. MIN
5. SUM
6. STDDEV
7. VARIANCE

eight) Explicate the graphic symbol, number, and engagement function in detail?

Character functions: accept character input and render both grapheme and number values. Types of character function are:

• Instance-Manipulation Functions: LOWER, UPPER, INITCAP
• 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 Modern

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.

9) What is a Dual Table?

The dual tabular array is owned past the user SYS and tin exist accessed by all users. It contains i columnDummy and one row with the value X. The Dual Tabular array is useful when yous want to return a value but one time. The value can be a constant, pseudocolumn, or expression that is non derived from a table with user data.

10) Explain the Conversion function in detail?

Conversion Functions convert a value from 1 data blazon to another. Conversion functions are of ii types:

Implicit Data type conversion:

1. VARCHAR2 or CHAR To NUMBER, Appointment
2. NUMBER To VARCHAR2
3. Engagement To VARCHAR2

Explicit data type conversion:

1. TO_NUMBER
2. TO_CHAR
3. TO_DATE

TO_NUMBER part is used to catechumen a Character string to Number format. TO_NUMBER function employ fx modifier. Format: TO_NUMBER ( char[, ' format_model'] ). fx modifier specifies the exact matching for the graphic symbol argument and number format model of the TO_NUMBER function.

TO_CHAR part is used to convert NUMBER or DATE information type to Graphic symbol format. TO_CHAR Function uses fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (engagement, 'format_model').The format model must be enclosed in unmarried quotation marks and is case sensitive.

For exp: Select TO_CHAR (hire date, 'MM/YY') from the employee.

TO_DATE function is used to convert a Character string to date format. TO_DATE function use fx modifier which specifies the verbal matching for the character argument and date format model of TO_DATE function. TO_DATE part format: TO_DATE ( char[, ' format_model'] ).

For exp: Select TO_DATE ('May 24, 2007',' mon dd RR') from dual;

eleven) Depict different types of General Function used in SQL?

General functions are of the following types:

NVL: Converts a cypher value to an actual value. NVL (exp1, exp2) .If exp1 is cipher then the NVL function returns the value of exp2.

NVL2: If exp1 is not cypher, nvl2 returns exp2, if exp1 is zero, nvl2 returns exp3. The argument exp1 can take any data type. NVL2 (exp1, exp2, exp3)

NULLIF: Compares 2 expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)

COALESCE: Returns the beginning not-null expression in the expression listing. COALESCE (exp1, exp2… expn). The advantage of the Coalesce function over the NVL function is that the COALESCE function can take multiple alternative values.

Conditional Expressions: Provide the use of IF-And then-ELSE logic within a SQL argument. Example: CASE Expression and DECODE Part.

12) What is the divergence betwixt COUNT (*), COUNT (expression), COUNT (singled-out expression)? (Where expression is whatsoever column name of Tabular array)?

• COUNT (*): Returns a number of rows in a table including duplicates rows and rows containing zippo values in any of the columns.
• COUNT (EXP): Returns the number of non-null values in the column identified past expression.
• COUNT (DISTINCT EXP): Returns the number of unique, non-zip values in the column identified by expression.

thirteen) What is a Sub Query? Describe its Types?

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery tin be placed in WHERE HAVING and FROM clause.

Guidelines for using subqueries:

1. Enclose sub queries within parenthesis
2. Identify subqueries on the right side of the comparison condition.
3. Use Unmarried-row operators with single-row subqueries and Multiple-row operators with multiple-row subqueries.

Types of subqueries:

• Single-Row Subquery: Queries that return just i row from the inner select argument. Single-row comparison operators are: =, >, >=, <, <=, <>
• Multiple-Row Subquery: Queries that render more than ane row from the inner Select statement. There are also multiple-cavalcade subqueries that return more 1 column from the inner select argument. Operators include: IN, ANY, ALL.

14) What is the difference between Any and ALL operators?

Whatsoever Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.

• > Any ways more than the minimum.
• < ANY means less than the maximum
• = Whatsoever 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 Non IN condition.

xv) What is a MERGE statement?

The MERGE statement inserts or updates rows in one table, using data from some other table. It is useful in information warehousing applications.

sixteen) What is the divergence between the "VERIFY" and the "FEEDBACK" command?

• VERIFY Control: Use VERIFY Command to confirm the changes in the SQL argument (Sometime and New values). Divers with SET VERIFY ON/OFF.
• Feedback Control: Displays the number of records returned by a query.

17) What is the use of Double Ampersand (&&) in SQL Queries? Give an example?

Utilize "&&" if you want to reuse the variable value without prompting the user each fourth dimension.

For ex: Select empno, ename, &&column_name from employee order by &column_name;

18) What are Joins and how many types of Joins are in that location?

Joins are used to think data from more than one table.

There are v different types of joins.

Oracle 8i and Prior	SQL: 1999 (9i)
Equi Join	Natural/Inner Bring together
Outer Join	Left Outer/ Right Outer/ Full Outer Join
Self Join	Join ON
Not-Equi Join	Join USING
Cartesian Product	Cross Bring together

19) 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, e'er include a valid join condition in a "where" clause. To Join 'N' tables together, you lot need a minimum of North-1 Join conditions.

For exp: to join four tables, a minimum of three joins is required. This dominion may non apply if the tabular array has a concatenated primary key, in which case more than one column is required to uniquely place each row.

Equi Join: This type of Bring together involves main and foreign cardinal relations. Equi Join is likewise called Elementary or Inner Joins.

Non-Equi Joins A Not-Equi Bring together status containing something other than an equality operator. The relationship is obtained using an operator other than an equal operator (=). The weather such as <= and >= tin can be used, but BETWEEN is the simplest to correspond 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 data missing. It returns those rows from one table that has no direct friction match in the other tabular array. A status involving an Outer Join cannot use IN and OR operators.

Cocky Join: Joining a table to itself.

xx) Explain all Joins used in Oracle 9i and later release?

Cantankerous Bring together:

The Cross Bring together clause produces the cross-product of 2 tables. This is the same as a Cartesian product betwixt the two tables.

Natural Joins:

This is used to bring together two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. Information technology is equal to the Equi-Join. If the columns accept the same names but different data types, then the Natural Join syntax causes an error.

Join with the USING clause:

If several columns have the aforementioned names but the information types do not match, then the NATURAL Join clause can be modified with the USING clause to specify the columns that should exist used for an equi Join. Use the USING clause to match only one column when more one column matches. Exercise not apply a tabular array proper 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=ten;

Joins with the ON clause:

Utilize the ON clause to specify a bring together condition. The ON clause makes the code easy to empathize. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.

Left/ Right/ Total Outer Joins:

Left Outer Join displays all rows from the tabular array 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 Total OUTER JOIN clause.

Best Pl/SQL Programmer Interview Questions

21) What is the difference between Entity, Attribute, and Tuple?

Entity: A significant matter about which some information is required. For exp: EMPLOYEE (table). Attribute: Something that describes the entity. For exp: empno, emp name, emp address (columns). Tuple: A row in a relation is called Tuple.

22) What is a Transaction? Describe common errors that can occur while executing whatever Transaction?

Transaction consists of a collection of DML statements that forms a logical unit of measurement of piece of work.

The common errors that tin can occur while executing whatsoever transaction are:

The violation of constraints.

1. Data type mismatch.
2. Value also broad to fit in the cavalcade.
3. The system crashes or the Server gets down.
4. The session Killed.
5. Locking takes place. Etc.

23) 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: This occurs for all SQL statements except SELECT.
• Explicit Locking: This tin can be done by the user manually.

Further, at that place are two locking methods:

1. Exclusive: Locks out other users
2. Share: Allows other users to admission

24) What is the 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 roll back the transaction to a detail named savepoint.

25) 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.

26) Draw naming rules for creating a Table?

Naming rules to be considered for creating a tabular array is:

1. The table proper noun must begin with a letter,
2. The table name tin can exist 1-xxx characters long,
3. Table name can contain only A-Z, a-z, 0-nine,_, $, #.
4. The table name cannot duplicate the name of another object owned by the same user.
5. The table proper noun cannot exist an oracle server reserved give-and-take.

27) What is a DEFAULT option in a tabular array?

A column can be given a default value past using the DEFAULT option. This option prevents cipher values from entering the cavalcade if a row is inserted without a value for that column. The DEFAULT value tin be a literal, an expression, or a SQL function such equally SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.

28)  What is the divergence between USER TABLES and Data Lexicon?

• USER TABLES: This is a collection of tables created and maintained by the user. Contain USER information.
• Data Dictionary: This is a collection of tables created and maintained by the Oracle Server. It contains database data. All data dictionary tables are owned by the SYS user.

29)  Describe a few Information 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 'ane'and Maximum size is '2000'
3. NUMBER(P,South): " Precision" can range from 1 to 38 and the "Scale" can range from -84 to 127.
4. Engagement
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.

30)  In what scenario you tin can modify a cavalcade in a tabular array?

During modifying a column:

1. You tin increase the width or precision of a numeric column.
2. You tin can increment the width of numeric or character columns.
3. You can subtract the width of a column only if the column contains aught values or if the table has no rows.
4. You lot can change the data type just if the column contains null values.
5. You can catechumen a CHAR cavalcade to the VARCHAR2 data type or catechumen a VARCHAR2 cavalcade to the CHAR data type only if the column contains null values or if you do not change the size.

31) Describe a few restrictions on using the "LONG" data type?

A LONG column is not copied when a table is created using a subquery. A LONG column cannot be included in a GROUP BY or an Order By clause. Merely one LONG cavalcade can be used per table. No constraint can be defined on a LONG cavalcade.

32) What is a SET UNUSED option?

SET UNUSED option marks one or more than columns every bit unused and so that they tin be dropped when the demand on system resource is lower. Unused columns are treated as if they were dropped, even though their cavalcade data remains in the table'southward rows. Later a column has been marked as unused, yous accept no access to that column.

A select * query will not call back information from unused columns. In improver, the names and types of columns marked unused will non be displayed during a DESCRIBE, and you can add to the table a new column with the aforementioned name as an unused column. The Set UNUSED data is stored in the USER_UNUSED_COL_TABS dictionary view.

33) What is the divergence betwixt Truncate and Delete?

The primary departure betwixt Truncate and Delete is as below:

SQL Truncate	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 non release storage space used by that table.
TRUNCATE Control is faster.	DELETE command is slower.
Is a DDL statement and cannot exist Rollback.	Is a DDL statement and can be Rollback.
Database Triggers do not burn down on TRUNCATE.	Database Triggers fire on DELETE.

34) What is the main deviation between CHAR and VARCHAR2?

CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.

35) What are Constraints? How many types of constraints are in that location?

Constraints are used to forbid invalid data entry or deletion if there are dependencies. Constraints enforce rules at the tabular array level. Constraints tin can be created either at the same time equally the table is created or after the tabular array has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table tin can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except Non NULL which is divers only at the column level. In that location are 5 types of constraints:

• Non Goose egg Constraint
• Unique Cardinal Constraint
• Chief Key Constraint
• Foreign Key Constraint
• Check Key Constraint.

36) Describe types of Constraints in brief?

• NOT NULL: Not Aught Constraint ensures that the cavalcade contains no nothing values.
• UNIQUE Primal: 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 tin have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than 1 column, that group of columns is called a Composite Unique Central. In that location can be more than than 1 Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Cardinal automatically creates an alphabetize on the column it is created.
• Primary Fundamental: Uniquely identifies each row in the Table. But one Principal KEY can be created for each tabular array but can accept several UNIQUE constraints. Chief KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY Key is chosen a Parent central.
• Strange Primal: This is also called Referential Integrity Constraint. FOREIGN KEY is ane in which a column or set of columns accept references of the Principal/Unique cardinal of the same or another tabular array. Strange Key is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be nil.
• CHECK KEY: Defines a status that each row must satisfy. A unmarried column can have multiple Cheque Constraints. During CHECK constraint following expressions is not immune:

         i. References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM Pseudo columns.

         two. Calls to SYSDATE, UID, USER, and USERENV Functions

37) What is the master difference between Unique Primal and Primary Fundamental?

The main difference between Unique Cardinal and Chief Cardinal is:

Unique Key	Primary Fundamental
A table can take more one Unique Cardinal.	A table can accept but 1 Primary Key.
The unique key cavalcade can store NULL values.	The primary key cavalcade cannot store Aught values.
Uniquely identify each value in a column.	Uniquely identify each row in a tabular array.

38) What is the difference between ON DELETE Cascade and ON DELETE Gear up NULL?

ON DELETE Pour Indicates that when the row in the parent table is deleted, the dependent rows in the child tabular array will besides be deleted. ON DELETE Ready NULL Covert foreign key values to nada when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE Fix Zilch options, the row in the parent table cannot be deleted if information technology is referenced in the child table.

39) What is a Candidate Key?

The columns in a table that tin can act as a Primary Key are called Candidate Fundamental.

40)  What are Views and why they are used?

A View logically represents subsets of data from ane or more tables. A View is a logical table based on a table or another view. A View contains no data of its own only 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 tabular array: USER_VIEWS.

Views are used:

• To restrict data access
• To make circuitous queries easy
• To provide information Independence
• Views provide groups of users to admission data co-ordinate to their requirements.

Pl/SQL Interview Questions For 10 Years Experienced

41) What is the divergence between Uncomplicated and Circuitous Views?

The main differences betwixt the two views are:

Simple View	Complex View
Derives data from only one table.	Derives data from many tables.  Contain functions or groups of data.
Contains no functions or group of data	Derives data from many tables.  Contain functions or groups of data.
Tin can perform DML operations through the view.	Does non always allow DML operations through the view

42) 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 Past clause
3. The Distinct Keyword
4. The Pseudo cavalcade ROWNUM Keyword.

Yous cannot Modify data in a View if information technology contains the following:

1. Group Functions
2. A Group By clause
3. The Singled-out Keyword
4. The Pseudo column ROWNUM Keyword.
5. Columns divers by expressions (Ex; Salary * 12)

You cannot INSERT data through a view if it contains the following:

43)  What is PL/SQL, Why do nosotros need PL/SQL instead of SQL, Describe your experience working with PLSQL and What are the difficulties faced while working with PL SQL and How did you overcome them?

1. PL/SQL is a procedural linguistic communication extension with SQL Language.
2. Oracle introduced PL/SQL
3. Information technology is a combination of SQL and Procedural Statements and used for creating applications.
4. Basically, PL/SQL is a block-structured programming language whenever nosotros 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.
6. Explain your electric current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you've faced in your project while working with PL/SQL.

44) What are the different functionalities of a Trigger?

Trigger is also the aforementioned equally stored procedure & likewise it volition automatically exist invoked whenever DML operation performed confronting tabular array or view.

There are two types of triggers supported past PL/SQL

1. Statement Level Trigger.
2. Row Level Trigger

Statement Level Trigger:In a statement-level trigger, the trigger trunk is executed just in one case for the DML statement.

Row Level Trigger:In a row-level trigger, the trigger body is executed for each row DML argument. It is the reason, nosotros are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers: onetime, new, are also called records blazon variables.
These qualifiers are used in trigger specification & trigger body.

Syntax

          erstwhile.column_name        

Syntax

          new column_name        

When we utilise these qualifiers in trigger specification so we are not allowed to use ":" in form of the names of the qualifiers.

45) Write a PL/SQL Plan that raises a user-defined exception on Thursday?

          declare a exception begin If to_char(sysdate, 'DY)='THU' so heighten a; end if; exception when a then dbms_output.put_line('my exception raised on thursday'); end ;        

Output: my exception raised on Thursday

46) Write a PL/SQL program to think the emp table and and then brandish the salary?

          declare v_sal number(10); begin select max(sal)intr v_sal; from emp; dbms_output.put_line(v.sal); stop; /        

(or)

          declare A number(x); B number(x); C number(10); begin a:=70; b:=30; c:=greatest+(a,b); dbms_output.put_line(c); end; /        

Output:70

47) Write a PL/SQL cursor program that is used to calculate total bacon from emp tabular array without using sum() function?

          Declare cursor c1 is select sal from emp; v_sal number(ten); due north.number(ten):=0; begin open up c1; loop fetch c1 into v_sal; get out when c1%not found; n:=n+v_sal; finish loop; dbms_output.put_line('tool salary is'||' ' ||n); close c1; end; /  Output: total salary is: 36975        

48) Write a PL/SQL cursor program to display all employee names and their bacon from the emp table past using % non plant attributes?

          Declare Cursor c1 is select ename, sal from emp; v_ename varchar2(x); v_sal number(10); begin open c1; loop fetch c1 into v_ename, v_sal; be when c1 % notfound; dbms_output.put_line(v_name ||' '||v_sal); finish loop; close c1; end; /        

49) What is a Mutating Trigger?

• Into a row-level trigger based on a table, the trigger body cannot read information from the aforementioned tabular array and likewise we cannot perform DML operation on the same table.
• If we are trying this oracle server returns mutating mistake oracle-4091: table is mutating.
• This error is chosen a mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
• Mutating errors are not occurred in argument-level trigger because through this statement-level trigger when nosotros are performing DML operations automatically data committed into the database, whereas in the row-level trigger when nosotros are performing transaction information is not committed and also again we are reading this data from the same table and so only mutating errors is occurred.

fifty) What are Triggering Events (or) Trigger Predicate Clauses?

If we want to perform multiple operations in different tables and then nosotros must apply triggering events inside the trigger body. These are inserting, updating, deleting clauses. These clauses are used in the argument, row-level triggers. These triggers are also called trigger predicate clauses.

51) What is the Discard File?

• This file extension is .dsc

• Discard file we must specify inside the control file by using the discard file clause.

• The discard file also stores reflected records based on when clause condition within the control file. This condition must be satisfied in the table clause.

52) What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR?

Oracle 7.ii introduced ref cursor, This is a user-divers blazon that is used to process multiple records and also this is a record by tape process.

In static cursor database servers execute simply one select statement at a time for a single agile set expanse wherein ref cursor database servers execute a number of select statements dynamically for a single active set surface area that's why those cursors are also called a dynamical cursor.

Generally, we are not allowed to laissez passer static cursor every bit parameters to utilise subprograms whereas we can also pass ref cursor as a parameter to the subprograms considering basically precursor is a user-defined type in oracle nosotros can too laissez passer all user-divers type as a parameter to the subprograms.

Generally, the static cursor does not return multiple records into the customer application whereas the ref cursor is allowed to render multiple records into the customer application (Java, .Net, PHP, VB, C++).

This is a user-defined blazon so we are creating information technology in two steps process i.e first nosotros are creating a blazon then simply nosotros are creating a variable from that blazon that's why this is as well called a cursor variable.

53) What are The Types of Ref Cursors?

In all databases having two ref cursors.

1. Strong ref cursor
2. Weak ref cursor

A strong ref cursor is a ref cursor that has a return type, whereas a weak ref cursor has no return type.

Syntax:

          Blazon typename is ref cursor render record blazon information blazon; Variable Name typename        

Syntax

          Type typename is ref cursor Variable Proper name typename;        

In the Weak ref cursor, nosotros must specify a select statement by using open for clause this clause is used in the executable section of the PL/SQL block.

Syntax:

          Open up ref cursor varname for SELECT * FROM table_name condition;        

54) What is the Difference Between the trim, delete collection methods?

          SQL> declare            blazon t1 is tabular array of number(ten);            v_t t1;=t1(10,20,thirty,forty,fifty,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));             Finish loop;              vt.delete(2);             dbms_output.put_line('afterwards deleting second element;);              for i in v_t.first..v_t.concluding             loop              If v_t.exists(i) and so             dbms_output.put_line(v_t(i));              stop if;             terminate loop;             end;              /        

55) What are Overloading Procedures?

Overload refers to the same name that can be used for a different purpose, in oracle we can also implement an overloading procedure through the parcel. Overloading procedure having the same name with different types or different numbers of parameters.

56) What are the Global Variables?

In oracle, nosotros are declaring global variables in Package Specification only.

57) What is Forwards Annunciation?

In oracle declaring procedures inside the package body are chosen forward declaring generally earlier we are calling private procedures into public procedure first we must implement individual procedure within body otherwise use a forrard annunciation inside the package body.

58) What is Invalid_number, Value_Error?

In oracle when we attempt to catechumen "string type to number blazon" or" data string into data type" and so the oracle server returns 2 types of errors.

1. Invalid_number
2. Value_error (or) numeric_error

Invalid_number:

When PL/SQL block has a SQL argument and too those SQL statements try to convert string type to number type or data string into information type then oracle server returns an mistake: ora-1722-Invalid Number

For treatment this error oracle provides number exception Invalid_number exception proper noun.

Case:

          begin Insert  intoemp(empno, ename, sal)  values(1,'gokul', 'abc') exception when invalid_number then dbms_output.put_line('insert proper data but'); finish;/        

value_error:

Whenever PL/SQL cake having procedural statements and too those statements find to convert string type to number type and so oracle servers return an error: ora-6502: numeric or value error: grapheme to a number conversion error
For handling, this error oracle provided exception value_error exception proper name

Example:

          begin declare z number(10); brainstorm z:= '&x' + '&y'; dbms_output.put_line(z); exception when value_error then dbms_output.put_line('enter numeric data value for x & y just'); end;/        

 Output:

          Enter value for x:three Enter value for y:2 z:=5  Enter value for x:a Enter value for y:b  Error:enter numeric information value for x & y merely.        

59) What is Flashback Query?

• Flashback query is handled by the Database Administrator the just flashback queries along to allow the content of the table to be retrieved with reference to the specific point of fourth dimension past using as of clause that is flashback queries retrieves adventitious data subsequently committing the transaction also.
• Flashback queries mostly utilize undo file that is flashback queries retrieve old information earlier committing the transaction oracle to provide two methods for flashback queries

         Method1: using the timestamp
         Method2: using scn number

threescore) Explain what PL/SQL package consists of?

PL/SQL consists of two major parts, they are package specification and packet body.

1. Bundle specification: it acts as a public interface for your application which includes procedures, types, etc.
2. Package Torso: It contains the lawmaking required to implement the Package Specification

61) Explain what the benefits of PL/SQL Packages are?

These are the benefits of PL/SQL Packages

• We can shop functions and procedures in a single unit called a package.
• Packages provide security to grant privileges.
• Functions and procedures, inside the package, shares a common variable among them.
• Packages support even if the functions are overloaded.
• Packages enhance the performance even when multiple objects loaded into memory.

62) Explicate different methods to trace the PL/SQL code?

Tracing code is a necessary technique to test the operation of the lawmaking during runtime.  We take different methods in PL/SQL to trace the code, which are,

• DBMS_ TRACE
• DBMS_ APPLICATION_INFO
• Tkproof utilities and trcsess
• DBMS_SESSION and DBMS_MONITOR

63) What does it mean by PL/SQL Cursors?

In  PL/SQL to retrieve and process more, it requires a special resource, and that resource is known equally Cursor. A cursor is divers as a pointer to the context area. The context area is an area of retention that contains information and SQL statements for processing the statements.

64) What is the departure between Implicit and Explicit Cursors?

An implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch, etc.

An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select part. An explicit office is used PL/SQL to execute tasks such as update, insert, delete, etc.

65) What is a trigger?

It is a program in PL/SQL, stored in the database, and executed instantly before or after the UPDATE, INSERT and DELETE commands.

66) What are the uses of database triggers?

Triggers are programs that are automatically fired or executed when some events happen and are used for:

• To implement complex security authorizations.
• To bulldoze cavalcade values.
• To maintain duplicate tables.
• To implement complex business rules.
• To bring transparency in log events.

67) Proper name the two exceptions in PL/SQL?

Error handling office of PL/SQL is chosen an exception.  Nosotros accept 2 types of exceptions, and they are User-defined and predefined.

68) Which command is used to delete the package?

To delete the 'Package' in PL/SQL we utilise the DROP PACKAGE command.

69) what is the process for PL/SQL compilation?

The compilation procedure consists of syntax check, bind, and p-code generation. It checks the errors in PL/SQL lawmaking while compiling. Once all errors are corrected, a storage address allocated to a variable that stores this data. This procedure is called binding. P-Lawmaking consists of a list of rules for the PL/SQL engine. It is stored in the database and triggered when the next fourth dimension it is used.

References: Stackoverflow | Scribd | Slideshare

          If inserting then stmts; else if updating then stmts; else if deleting and then stmts; stop if;        

Related Oracle Interview Questions

• Oracle Projection Accounting Questions & Answers
• Oracle IExpense Interview Questions
• Oracle Data Guard Interview Questions
• Oracle DAC Interview Questions
• Oracle Financials India Localization Interview Questions

bondladdelf.blogspot.com

Source: https://mindmajix.com/oracle-pl-sql-interview-questions

Share this post