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:
<ul>
<li style="text-align: justify;">PL/SQL is a block-structured language.</li>
<li style="text-align: justify;">It is portable to all environments that support Oracle.</li>
<li style="text-align: justify;">PL/SQL is integrated with the Oracle data dictionary.</li>
<li style="text-align: justify;">Stored procedures help better sharing of application.</li>
</ul>
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.
<h2 style="text-align: justify;">Interview Questions On PL SQL</h2>
Name few schema objects that can be created using PL/SQL?
Stored procedures and functions
Packages
Triggers
Cursors
<div id="Section_1" class="accord-heading active">How is PL/SQL differs from SQL?</div>
<div>&nbsp;</div>
<div>
<table style="width: 682px; height: 198px; margin-left: auto; margin-right: auto;" border="1">
<tbody>
<tr>
<td style="width: 225px; text-align: center;">SQL</td>
<td style="width: 441px; text-align: center;">PL/SQL</td>
</tr>
<tr>
<td style="width: 225px;">Query/Command executions</td>
<td style="width: 441px;">Programming Language Execution at a time</td>
</tr>
<tr>
<td style="width: 225px;">Data source for web pages</td>
<td style="width: 441px;">Build, format and display web pages that contains data source</td>
</tr>
<tr>
<td style="width: 225px;">Declarative in nature</td>
<td style="width: 441px;">Procedural in nature</td>
</tr>
<tr>
<td style="width: 225px;">Manipulating data</td>
<td style="width: 441px;">Creating Web Applications</td>
</tr>
</tbody>
</table>
</div>
&nbsp;
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
<ul>
<li style="padding-left: 30px; text-align: justify;">Creating validation mechanisms involving searches in multiple tables</li>
<li style="padding-left: 30px; text-align: justify;">Creating logs to register the use of a table</li>
<li style="padding-left: 30px; text-align: justify;">Update other tables as a result of inclusion or changes in the current table.</li>
</ul>
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.
<a href="https://tekslate.com/oracle-pl-sql-training/"><img class="size-full wp-image-45993 aligncenter" src="https://tekslate.com/wp-content/uploads/2016/04/PL-SQL-Interview-Questions.png" alt="Pl SQL Interview Questions" width="600" height="60" /></a>
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:
<ul>
<li>Table based records</li>
<li>Programmer based records</li>
<li>Cursor based records</li>
</ul>
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,&rsquo;3&prime;,&rsquo;Third&rsquo;,&rsquo;4&prime;,&rsquo;Fourth&rsquo;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 &lt;procedure name&gt; to call it directly
EXECUTE &lt;procedure name&gt; from calling environment
&lt;Procedure name&gt; 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.
<ul>
<li>Numbers &ndash; INT, INTEGER, FLOAT, NUMBER, SMALLINT, REAL etc.</li>
<li>Character or String &ndash; CHAR, CHARACTER, RAW, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2 etc.</li>
<li>Boolean &ndash; BOOLEAN</li>
<li>Date Time &ndash; DATE, TIMESTAMP etc.</li>
<li>Refer them in detail at oracle Database Documentation.</li>
</ul>
Give the stages of instance startup to a usable state where normal users may access it.&nbsp;
<table style="width: 382px; height: 136px; margin-left: auto; margin-right: auto;" border="1">
<tbody>
<tr>
<td style="width: 177px;">STARTUP NOMOUNT</td>
<td style="width: 189px;">Instance startup</td>
</tr>
<tr>
<td style="width: 177px;">STARTUP MOUNT</td>
<td style="width: 189px;">The database is mounted</td>
</tr>
<tr>
<td style="width: 177px;">STARTUP OPEN</td>
<td style="width: 189px;">The database is opened</td>
</tr>
</tbody>
</table>
&nbsp; How to convert date to Julian Date format?
Using &lsquo;J&rsquo; format string
SQL &gt;&nbsp;select to_char(to_date(&rsquo;24-Jan-2013&prime;,&rsquo;dd-mm-yyyy&rsquo;),&rsquo;J&rsquo;) as julian from dual;
JULIAN
<hr />
<a href="https://tekslate.com/tutorials/oracle-pl-sql/"><img class="aligncenter size-full wp-image-46394" src="https://tekslate.com/wp-content/uploads/2016/04/PLSQL-Tutorial.png" alt="FREE PL SQL Tutorials" width="600" height="60" /></a>

Oracle PL SQL Interview Questions and Answers

plsql-interview-questions-and-answers-beginner-advanced-experienced

&nbsp;
<h2>Oracle PL SQL Interview Questions And Answers</h2>
<h3 style="text-align: justify;">Q1. What is PL-SQL?</h3>
Oracle PL/SQL is a procedural language that has both interactive <a href="tutorialsmysql" target="_blank" rel="noopener">SQL</a> and procedural programming language constructs such as iteration, conditional branching.
<h3 style="text-align: justify;">Q2. What is the basic structure of PL/SQL?</h3>
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in <a href="oracle-pl-sql-tutorials" target="_blank" rel="noopener">PL/SQL.</a>
<h3 class="h3" style="text-align: justify;">Q3. What are the most important characteristics of PL/SQL?</h3>
A list of some notable characteristics:
<ul>
<li style="text-align: justify;">PL/SQL is a <a href="https://www.encyclopedia.com/computing/dictionaries-thesauruses-pictures-and-press-releases/block-structured-languages">block-structured language</a>.</li>
<li style="text-align: justify;">It is portable to all environments that support Oracle.</li>
<li style="text-align: justify;">PL/SQL is integrated with the <a href="oracle-soa-11g" target="_blank" rel="noopener">Oracle</a> data dictionary.</li>
<li style="text-align: justify;">Stored procedures help better the sharing of applications.</li>
</ul>
<h3 style="text-align: justify;">Q4. How is the process of PL/SQL compiled?</h3>
The 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.
<pre style="box-sizing: border-box; font-family: SFMono-Regular, Menlo, Monaco, Consolas, 'Liberation Mono', 'Courier New', monospace; font-size: 13px; margin-top: 0px; margin-bottom: 10px; overflow: auto; color: #212529; padding: 8px; word-break: break-all; overflow-wrap: break-word; background-color: #f5f5f5; border: 1px solid #cccccc; border-radius: 4px; text-align: center;">Interested in mastering Oracle PL SQL Training? Enroll now for FREE demo on <a href="oracle-pl-sql-training" target="_blank" rel="noopener">Oracle PL SQL Training</a>.</pre>
<h3 style="text-align: justify;">Q5. How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?</h3>
Before getting into the PL/SQL, we must create a REST API using JSON for establishing connections. 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.
<h3 style="text-align: justify;">Q6. Name a few schema objects that can be created using PL/SQL?</h3>
Stored procedures and functions
Packages
Triggers
Cursors
<h3 id="Section_1" class="accord-heading active">Q7. How is PL/SQL differs from SQL?</h3>
<div>&nbsp;</div>
<div>
<table style="width: 682px; height: 198px; margin-left: auto; margin-right: auto;" border="1">
<tbody>
<tr>
<td style="width: 225px; text-align: center;">SQL</td>
<td style="width: 441px; text-align: center;">PL/SQL</td>
</tr>
<tr>
<td style="width: 225px;">Query/Command executions</td>
<td style="width: 441px;">Programming Language Execution at a time</td>
</tr>
<tr>
<td style="width: 225px;">The data source for web pages</td>
<td style="width: 441px;">Build, format, and display web pages that contain data source</td>
</tr>
<tr>
<td style="width: 225px;">Declarative in nature</td>
<td style="width: 441px;">Procedural in nature</td>
</tr>
<tr>
<td style="width: 225px;">Manipulating data</td>
<td style="width: 441px;">Creating Web Applications</td>
</tr>
</tbody>
</table>
</div>
&nbsp;
<h3 style="text-align: justify;">Q8. What are the three basic sections of a PL/SQL block?</h3>
Declaration section
Execution section
Exception section
<h3 style="text-align: justify;">Q9. What is a trigger?</h3>
A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, updates, and deletes commands.
<h3 style="text-align: justify;">Q10. What are the uses of triggers?</h3>
Basically, triggers are used to create consistencies, access restrictions, and implement securities to the database. Triggers are also used for &minus;
<ul>
<li style="padding-left: 30px; text-align: justify;">Creating validation mechanisms involving searches in multiple tables</li>
<li style="padding-left: 30px; text-align: justify;">Creating logs to register the use of a table</li>
<li style="padding-left: 30px; text-align: justify;">Update other tables as a result of inclusion or changes in the current table.</li>
</ul>
<h3 style="text-align: justify;">Q11. How can we debug in PL/SQL?</h3>
We can make use of the DBMS_OUTPUT for printing breakpoint activities. We can also use DBMS_DEBUG.
<h3 style="text-align: justify;">Q12. How can we implement Rollback or Commit statement in a Trigger?</h3>
We cannot. It is not logical to put a Rollback or Commit within a Trigger because these statements impose a savepoint that affects the logical transaction processing.
<h3 style="text-align: justify;">Q13. What is an Oracle sequence?</h3>
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
<h3 style="text-align: justify;">Q14. Difference between SUBSTR and INSTR?</h3>
INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from the nth position of string1.
SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from an n-th position of string1.
<a href="oracle-pl-sql-training/"><img class="size-full wp-image-45993 aligncenter" src="wp-content/uploads/2016/04/PL-SQL-Interview-Questions.png" alt="Pl SQL Interview Questions" width="600" height="60" /></a>
<h3 style="text-align: justify;">Q15. What is PL/SQL Records?</h3>
<a href="oracle-pl-sql-training/">PS/SQL</a> Records is a type of data structure that contains 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:
<ul>
<li>Table based records</li>
<li>Programmer based records</li>
<li>Cursor based records</li>
</ul>
<h3 style="text-align: justify;">Q16. How can we make an IF Statement within a SELECT Statement?</h3>
We make use of the DECODE keyword. For example, e.g. select DECODE (EMP_CAT,&rsquo;3&prime;,&rsquo;Third&rsquo;,&rsquo;4&prime;,fourth null);
<h3 style="text-align: justify;">Q17. What are the components of the physical database structure of the Oracle database?</h3>
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.
<h3 style="text-align: justify;">Q18. How are functions and procedures called in PL/SQL?</h3>
CALL &lt;procedure name&gt; to call it directly
EXECUTE &lt;procedure name&gt; from calling environment
&lt;Procedure name&gt; from other procedures or functions or packages
Functions are called directly from other programs or procedures, no additional keyword is required.
<h3 style="text-align: justify;">Q19. What are the different data types available in PL/SQL?</h3>
PL SQL data types can be broadly divided into the following categories. There are many data types available in PL SQL but mostly you will be using some of the popular ones.
<ul>
<li>Numbers &ndash; INT, INTEGER, FLOAT, NUMBER, SMALLINT, REAL etc.</li>
<li>Character or String &ndash; CHAR, CHARACTER, RAW, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2 etc.</li>
<li>Boolean &ndash; BOOLEAN</li>
<li>Date Time &ndash; DATE, TIMESTAMP etc.</li>
<li>Refer them in detail at oracle Database Documentation.</li>
</ul>
<h3>Q20. Give the stages of instance startup to a usable state where normal users may access it.&nbsp;</h3>
<table style="width: 382px; height: 136px; margin-left: auto; margin-right: auto;" border="1">
<tbody>
<tr>
<td style="width: 177px;">STARTUP NOMOUNT</td>
<td style="width: 189px;">Instance startup</td>
</tr>
<tr>
<td style="width: 177px;">STARTUP MOUNT</td>
<td style="width: 189px;">The database is mounted</td>
</tr>
<tr>
<td style="width: 177px;">STARTUP OPEN</td>
<td style="width: 189px;">The database is opened</td>
</tr>
</tbody>
</table>
<h3>Q21. How to convert date to Julian Date format?</h3>
Using &lsquo;J&rsquo; format string
SQL &gt;&nbsp;select to_char(to_date(&rsquo;24-Jan-2013&prime;,&rsquo;dd-mm-yyyy&rsquo;),&rsquo;J&rsquo;) 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: &nbsp;General Errors with pre-defined names Undefined Exceptions: Less common errors and are not pre-defined User-defined Exceptions: Violates business rules but don&rsquo;t disturb running Discuss PL/SQL Records? The collection of <a href="sql-server-t-sql-training/">PL/SQL server training Hyderabad</a> 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
<ul>
<li>Programmer based records</li>
<li>Table based records</li>
<li>Cursor based records</li>
</ul>

TOP 20 Oracle PL/SQL Interview Questions And Answers

Oracle PLSQL Interview Questions

oracle-plsql-interview-questions

Oracle added a procedural programming language known, as PL/SQL (Procedural&nbsp;Language/SQL). PL/SQL is a third generation language and contains the standard&nbsp;programming constructs. Anonymous Block: An Unnamed block which will not store any where in the database is&nbsp;know as Anonymous block. Block Structure: PL/SQL programs are divided up into structures known as blocks, with&nbsp;each block containing the PL/SQL and SQL statements. The syntax for the structure is&nbsp;given below. Syntax: [DECLARE&nbsp;Variable declaration Begin Executable_statements [Exception Exception_handling_statements] End; Ex: &nbsp;SQL&gt; SET SERVEROUT ON; SQL&gt; Declare v_1 number; v_2 number; v_3 number; Begin v_1:=&amp;v_1; v_2:=&amp;v_2; v_3:=&amp;v_3; if v_1 &lt; v_2 and v_1 &lt; v_3 then&nbsp;dbms_output.put_line('1st is small'); elsif v_2 &lt; v_3 then&nbsp;dbms_output.put_line('2nd is small'); else&nbsp;dbms_output.put_line('3rd is small'); end if; Exception When zero_divide then&nbsp;DBMS_OUTPUT.PUT_LINE(&lsquo;Division by ZERO&rsquo;); End; O/p: It will ask for the three input values and will display the smallest number in that. Difference between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values&nbsp;so we pass into variables. Here in SQL there is buffer to hold the data temporarily. Ex: SQL&gt; Select ename from emp where is empno = 7788; SQL&gt; Declare&nbsp;V_name varchar2(10); Begin Select ename into v_name from emp where empno = 7788; End; Cursors: Cursor is a private SQL area provided by the oracle engine. It is mainly used to retrieve the data from more than one column. There are two types of cursors they are given below.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Implicit Cursors  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Explicit Cursors Implicit Cursors: Implicit cursor raises implicitly when we use INSERT, UPDATE,&nbsp;DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed&nbsp;by the PL/SQL engine. Ex: SQL&gt; declare v_edata emp%ROWTYPE; begin select * into v_edata from emp where empno=7788; if sql%notfound then &nbsp;dbms_output.put_line('Record Not Found'); else&nbsp;dbms_output.put_line('Record Found'); end if; end; Processing Explicit Cursors: The four PL/SQL steps necessary for explicit cursor&nbsp;processing are as follows.  &nbsp; &nbsp; &nbsp; &nbsp;Declare the cursor.  &nbsp; &nbsp; &nbsp; &nbsp; Open the cursor for a Query.  &nbsp; &nbsp; &nbsp; &nbsp; Fetch the results into PL/SQL variables.  &nbsp; &nbsp; &nbsp; &nbsp; Close the Cursor. Declaration of Cursor: The declaration of the cursor is associated with the select&nbsp;statement. The syntax for the cursor is given below. Syntax: CURSOR cursor_name IS select_statement; Ex: -- To print the Cumulative SAL of the Employee table order by empno SQL&gt;declare v_sal number; v_dno number; l_count number; cursor test_cursor is select * from scott.emp order by empno; begin v_sal:=0; v_dno:=0; for i in test_cursor loop if(i.deptno!=v_dno) then v_sal:= 0; end if; v_sal:=v_sal+i.sal; dbms_output.put_line('Ename: ' || i.ename||' Sal: '|| i.sal || '_ Cum_sal: '|| v_sal || 'Deptno: ' || i.deptno); l_count:=test_cursor%rowcount; end loop; dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor'); end test_cursor; Cursor Attributes: There are four cursors available in PL/SQL that can be applied to&nbsp;cursors. Those are given below.  &nbsp; &nbsp; &nbsp; &nbsp; %FOUND  &nbsp; &nbsp; &nbsp; &nbsp; %NOTFOUND  &nbsp; &nbsp; &nbsp; &nbsp; %ISOPEN  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;%ROWCOUNT %FOUND:&nbsp; %FOUND is a Boolean attribute. It returns TRUE if the previous FETCH&nbsp;returned a row and FALSE if it didn&rsquo;t. %NOTFOUND:&nbsp; %NOTFOUND is also a Boolean attribute. It returns FALSE if the&nbsp;previous FETCH returned a row and TRUE if it didn&rsquo;t. It behaves opposite to the&nbsp;%FOUND. %ISOPEN:&nbsp; %ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is&nbsp;open other wise it will return FALSE. %ROWCOUNT: %ROWCOUNT is a Numeric attribute. It returns number of rows&nbsp;returned by the cursor so far. Declaring, Opening Fetching and Closing the Cursor: Ex: SQL&gt; declare v_eno number; v_ename varchar2(20); l_count number; cursor razia_cursor is select empno,ename from scott.emp; begin open razia_cursor; loop fetch razia_cursor into v_eno, v_ename; exit when razia_cursor%notfound; l_count:=razia_cursor%rowcount; dbms_output.put_line('Ename: ' || v_ename||' ENUM: '|| v_eno); end loop; close razia_cursor; dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor'); end; Ex: SQL&gt; -- Example to print the odd rows in the Table EMP declare n number; l_count number; cursor razia_cursor is select empno,ename,rownum from scott.emp; begin for i in razia_cursor loop n:=mod(i.rownum,2); if (n&gt;0) then dbms_output.put_line('Empno: ' || i.empno||' NAME: '|| i.ename||' ROWNUM: '|| i.Rownum); end if; l_count:=razia_cursor%rowcount; end loop; dbms_output.put_line(l_count||' Number of Row(s) Fetched by the Cursor'); end razia_cursor; Exceptions: An Exception is a error handling statement. It is used when ever our&nbsp;program terminates abnormally by using the exception we can execute the program&nbsp;from abnormal termination to normal termination. There are mainly two types of&nbsp;exceptions they are given below.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Predefined Exceptions.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User defined Exceptions. Predefined Exceptions: Oracle has predefined several exceptions that correspond to&nbsp;the most common oracle errors. Some predefined exceptions are given below. LOGIN_DENIED NO_DATA_FOUND ZERO_DIVIDE TOO_MANY_ROWS STORAGE_ERROR ROWTYPE_MISMATCH INVALID_CURSOR CURSOR_ALREADY_OPEN Ex: SQL&gt; declare cursor test_cursor is select e.ename, d.deptno,&nbsp;d. dname from scott.emp e, scott.dept d&nbsp;where e.deptno=20 and &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d.dname='RESEARCH'; begin for i in test_cursor&nbsp;loop dbms_output.put_line(i.ename || ' ' || i.deptno ||' '|| i.dname); end loop; exception when no_data_found then dbms_output.put_line('NO DATA FOUND EXCEPTION RAISED'); when others then dbms_output.put_line(' An Error Raised ' || sqlerrm); end test_cursor; User Defined Exceptions: A user defined exception is an error defined by the&nbsp;programmer. User defined exceptions are declared in the declarative section of the PL/SQL block just like variables. Syntax: DECLARE&nbsp;E_myexception EXCEPTION; Ex: SQL&gt; create or replace procedure razia_proc(v_sal in number) as v_sl number; razia_excep exception; begin if(v_sal &gt;= 5000) then raise razia_excep; else select sal into v_sl from emp order by deptno; end if; exception when razia_excep then dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION'); end razia_proc; Procedures: Procedures are also known as subprograms. Given below is the syntax for&nbsp;the creation of the procedure. Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}] type, &hellip; [(argument [{IN | OUT | IN OUT}] type) ] {IS | AS} BEGIN procedure_body END procedure_name; Ex: SQL&gt; create or replace procedure razia_proc as v_sal number; cursor razia_cursor is select * from emp order by deptno; begin v_sal := &amp;v_s; for i in razia_cursor loop if(i.sal &gt; v_sal) then dbms_output.put_line( 'Employee Name: ' || i.ename); end if; end loop; exception when others then dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION'); dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW'); end razia_proc; Execution: exec razia_proc; Passing IN Parameter to the Procedures: Ex: SQL&gt; create or replace procedure kanthi_proc(p_inpar in number) as v_name varchar2(30); begin select ename into v_name from emp where empno=p_inpar; dbms_output.put_line( 'Employee Name: ' || v_name); exception when others then dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION'); end kanthi_proc; &nbsp;Using IN and OUT parameters to Procedures: Ex: SQL&gt; create or replace procedure shabbir_proc(p_outpar out varchar2,p_inpar in number) as begin select ename into p_outpar from emp where empno=p_inpar; end; --To Catch the Output variable out side the procedure in the SQL declare v_name varchar2(20); v_num number; begin mypro(v_name,&amp;v_num); dbms_output.put_line( 'Employee Name: ' || v_name); end shabbir_proc; Functions: Function is similar to a procedure except that a function must return a value&nbsp;to the statement from which it is called. The syntax for creating a function is given&nbsp;below. Syntax: SQL&gt; CREATE [OR REPLACE] FUNCTION function_name [(parameter_name[IN | OUT | IN OUT] type [,&hellip;.])] RETURN type; {IS | AS} BEGIN Function_body END function_name; Ex: create or replace function haritha_func(p_empno number) return varchar2 is v_ename varchar2(20); v_sal number; begin select ename,sal into v_ename, v_sal from emp where empno=p_empno; if v_sal&gt;=6000 then return 'TRUE'; else return 'FALSE'; end if; end haritha_func; Execution: SQL&gt; select haritha_func(7788) from dual; Ex: SQL&gt; create or replace function haritha_fun(p_radius number) return number as v_pi number := 3.141; v_area number; begin v_area := v_pi * POWER(p_radius,2); return v_area; end haritha_fun; Execution: SQL&gt; select haritha_fun(7) from dual; Dropping Function and Procedure: When we want to drop the function or procedure&nbsp;then we use this DROP statement the syntax for the DROP is given below. Syntax for Dropping Function: DROP FUNCTION function_name; Syntax for Dropping Procedure: DROP PROCEDURE procedure_name; Packages: Package is declarative part of the functions and procedures which are stored in that package. There are two blocks in defining a package.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Package Specification  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Package body Package Specification: The package specification contains information about the&nbsp;contents of the package. It does not contain code for any subprograms. Syntax: CREATE [OR REPLACE] PACKAGE package_name {IS | AS} Type_definition | Procedure_specification | Function_specification | Variable_declaration | Exception_declaration | Cursor_declaration | Pragma_declaration END [package_name]; Package Body: The package body is separate data dictionary object from the package&nbsp;header. It cannot be successfully compiled unless the package header is already been&nbsp;successfully compiled. Syntax: CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} &hellip;. BEGIN Initialization_code; END package_name; Ex: SQL&gt; create or replace package vamsi_pack as procedure razia_proc(mynum in number); end vamsi_pack; -- Declaration of Package Body and passing value to the procedure create or replace package body vamsi_pack as procedure razia_proc(mynum in number) as cursor mycursor is select ename,empno,sal from emp where empno=mynum; begin dbms_output.put_line(' NAME ' || ' NUMBER ' || ' SALARY '); for i in mycursor loop dbms_output.put_line(' '||i.ename||' '|| i.empno ||' '|| i.sal); end loop; exception when others then dbms_output.put_line('YOU HAVE DONE AN ILLEGAL OPERATION '); end myproc; end vamsi_pack; Execution: SQL&gt; exec vamsi_pack.razia_proc(7788); Triggers: A Trigger is a procedure that will fire automatically by the database. When a&nbsp;specified DML statement is run against the specified table. Triggers are useful for doing&nbsp;things like advanced auditing of changes made to a column values in a table. The syntax for creating the trigger is given below. Syntax: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} trigger_event ON table_name [FOR EACH ROW [WHEN trigger_condition]] BEGIN Trigger_body; END; Ex: This example is when we insert into a table t1 then the same data has to be inserted&nbsp;into the table t2. For that we require two tables of one same column. SQL&gt; CREATE TABLE t1(&nbsp;Eno number); SQL&gt; CREATE TABLE t2(Eno number,Ins_date date); SQL&gt; CREATE OR REPLACE TRIGGER razia_trigger BEFORE INSERT ON TABLE t1 FOR EACH ROW BEGIN INSERT INTO TABLE t2 VALUES(:new.eno,sysdate); END; Note: For every insert operation on t1 it will insert the eno col values to t2 along with the current&nbsp;sysdate in the other column. Disabling and Enabling Trigger: We can stop the trigger from firing by disabling the&nbsp;trigger by using the ALTER TRIGGER statement. The syntax for enabling the trigger and&nbsp;disabling the trigger is given below. Syntax: ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE; Dropping a Trigger: If we want to drop the trigger from the database then we have to use DROP TRIGGER statement. The syntax for dropping the trigger is given below. Syntax: DROP TRIGGER trigger_name; Check out our Popular Trainings&nbsp; <a href="https://tekslate.com/structure-oaf//" target="_blank" rel="noopener">Structure of OAF</a> <a href="https://tekslate.com/advantages-disadvantages-oaf/">Advantages and Dis-Advantages of OAF</a> <a href="https://tekslate.com/sql-server-dba-training/" target="_blank" rel="noopener">SQL Server DBA Training in Hyderabad</a> <a href="https://tekslate.com/creating-first-oaf-page-project/">Creating First OAF Page in Project</a>