75 TOP Procedure and Functions PLSQL Interview Questions and Answers pdf

1.     What is a subprogram? What are its advantages?
A named Procedural Language/Structured Query Language (PL/SQL) block is called a subprogram. It has three sections:
declarative, executable, and exception handling (optional). The main advantage of a subprogram is that it can be compiled and stored in a database. In addition, it has the following advantages:
a. Modularization —Refers to a property, which allows you to break the large programs into smaller modules and make the program easy to write and debug.
b. Easy maintenance —Refers to a property, which allows you to make the modifications in a code because the code is modular and written in one place.
c. Reusability—Refers to a property, which allows you to reuse the same subprogram within any application. The code of the subprogram need not be re-written each time.
d. Data integrity and security —Refers to a property, which prevents unauthorized users from accessing the subprogram without the proper rights.
e. Better performance —Helps in avoiding repeated parsing on subsequent calls to the same subprogram as the code is compiled, parsed, and available in the shared Structured Query Language (SQL) area. This reduces the number of calls to the database; and therefore, increases the network performance.
 An anonymous block has no specification or header section. It only consists of a PL/SQL block in the body that contains the declaration, executable, and exception sections.

6.     Give the basic block structure of a subprogram.
The block structure of a subprogram is given as follows:
 <header>
 IS | AS
 Declaration Section
 BEGIN
 Executable section
 EXCEPTION (optional)
 Exception Section
 End;

7. What is a procedure? Can it be stored in the database?
A procedure is a named PL/SQL block (subprogram) that can have more than one parameter as an input from the calling environment and it may return none, one, or more than one value.
Yes, it can be compiled and stored in the database as a schema object and used for execution at any point of time.

8. What does the REPLACE option indicates in the procedure definition?
The REPLACE option specifies that if the procedure with the same name exists in the database, it will be dropped and recreated in the database as a new version.

9. Can the size of the parameter passed to the procedure be restricted in the procedure definition?
No, the size of the parameter passed to the procedure cannot be restricted in the procedure declaration. The datatype for the parameter has to be specified only.

10.   What is the difference between formal and actual parameters in a subprogram specification?
Formal parameters are variables declared as the parameter list in the subprogram specification.
Actual parameters are the values that are passed to the subprogram from the calling environment. The actual parameter values are evaluated and resultant values are assigned to the formal parameters when the program is executed.
Both formal and actual parameters should be of compatible datatypes. Following code shows the formal parameters of the function:
CREATE OR REPLACE procedure proc_disp_percentage(p_student_id Number, p_percentage Number)
..........
END proc_disp_percentage
 In the preceding function, p_student_id and p_percentage are the formal parameters.
In the following function code, v_student_id and v_percentage are the actual parameters:
proc_disp_percentage  (v_student_id,  v_percentage);

11. Which datatypes can be used for parameters within a procedure?
Explicit datatypes, %TYPE and %ROWTYPE, without size specification can be used for parameters in a procedure.

12. What are the different parameter modes, which can be used in the procedure specification?
There are three parameter modes for a procedure, given as follows:
 IN —Refers to a constant value, which is assigned to this parameter from the calling environment. The IN parameter value cannot be modified within a procedure. It is a default mode.
 OUT— Refers to a return value, which is passed from the procedure back to the calling environment.
 IN OUT —Refers to a value, which is assigned from the calling environment to this parameter and a same or different value is passed back from the procedure to the calling environment using this parameter.

14. How can a parameter be initialized in a procedure?
The IN OUT parameter mode can be used to initialize a parameter in a procedure, as any value can be initially assigned to this parameter when the procedure is called from the calling environment. The value of this parameter can be changed during the execution of the procedure and the result can be returned back to the calling environment from the procedure.

15. Can default values be assigned to IN OUT parameters in a procedure?
No, default values cannot be assigned to IN OUT parameters only IN parameters can be assigned default values.

16. What are the different methods for passing parameters to a procedure?
There are following three different methods to pass values to a procedure:
Positional—Refers to a method in which actual parameters of a procedure are passed in the same order as the formal parameters
? Named—Refers to a method in which actual parameters of a procedure are passed in any arbitrary order but are associated with their corresponding formal parameters
? Combination—Refers to a method in which some parameters are passed as positional and some are passed as named parameters.

17. Can a procedure be declared and defined within a PL/SQL block?
Yes, any procedure can be declared and defined within a PL/SQL block.

18. What is the scope of the procedure defined within a PL/SQL block?
The scope of any procedure that is defined within the PL/SQL block will be limited as it can be called only within the PL/SQL block and not by any other procedure or calling environment.

20. What happens if an exception is unhandled within a procedure?
If an exception is unhandled within a procedure, the control is passed back to the calling environment. Any DML statements, which were executed within the procedure, are rolled back.

21. What is the basic specification for a function?
The basic specification for a function is given as follows:

22. Can  there  be  multiple  RETURN  statements  within  a function?
Yes, there can be multiple RETURN statements within a function though only one is executed. After the value is retuned, the control passes back to the calling environment and function processing is stopped.

23. Is it possible to define a RETURN statement in the exception part of a function?
Yes, it is possible to define RETURN statement in the exception section. This RETURN statement would be executed if an exception is raised and control is passed to the exception section of the code.

24. What are the different parameter modes, which can be used in the function specification?
The three different modes, IN, OUT, and IN OUT, can be used in the function specification. However, it is recommended to use only the IN mode. This is because functions are supposed to accept values from the calling environment and return a single value.

25. Can a function be defined without a RETURN statement?
No, a function cannot be defined without a RETURN statement because it has to return a value back to the calling environment.
In case a RETURN statement is not defined, runtime error is raised.

27. Can functions be stored within a database as database objects?
Yes, functions can be stored as database objects in the database.

28. Which datatypes can be used within functions that can be invoked in SQL statements?
Stored functions can use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.

29. What restrictions can be applied on stored functions, which are called from SQL statements?
The main restrictions on stored functions that are called from SQL statements are:
a. These functions can use only positional methods for passing the parameters.
b. These functions can use only SQL specific datatypes. PL/SL datatypes, such as RECORD or TYPE, cannot be used.
c. User should have EXECUTE privilege on the function to use it within SQL statements
d. These functions cannot have the OUT and IN OUT parameter modes.

30. What is the difference between CREATE OR REPLACE FUNCTION and DROP FUNCTION commands?
The CREATE OR REPLACE FUNCTION command creates the function if it does not exist and replaces it with the new version if it already exists. The DROP FUNCTION command deletes the function altogether from the database. Any privilege assigned on the function remains the same when the CREATE OR REPLACE FUNCTION command is used; however, in the case of the DROP FUNCTION command, all the privileges are revoked.

31. Can a role be used to define privileges to access database objects of another schema in procedures and functions?
Database objects of another schema cannot be accessed in a procedure or function through roles; however, explicit privileges have to be granted to the user for those database objects.

32. How can a compiled object code be viewed?
Compiled object code cannot be viewed, as it is not accessible to the user.

33. What is the use of the USERJERRORS data dictionary view?
The USERJERRORS data dictionary view is used to view the compilation errors of the subprograms.

34. Write the SQL query to view all the procedures within a schema.
SQL query to view all the procedures within a schema is as follows:
Select object_name from USER_OBJECTS where
object_type='PROCEDURE'order by object_name;

35. How can the text of a procedure or function specification be viewed?
The text of a procedure or function specification can be viewed using the USER_SOURCE data dictionary view.

36. Write the syntax for the SHOW.ERROR command.
The syntax for the SHOW_ERROR command is as follows:
 SHOW_ERROR [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY} schema.object_name]

37. How can the parameter list for a function or procedure be displayed?
The parameter list for a function or procedure can be displayed using the DESCRIBE command, given as follows:
DESCRIBE subprogram_name

38. What are the advantages of using stored procedures and functions?
Stored procedures are a collection of SQL, PL/SQL, and Java statements that allow a programmer to shift the code from the application side to the database side. This enables the business rules and application logic to be stored as procedures within the database. The main advantages of using stored procedures and functions are as follows:
The complex business rules processing is performed within the database on the server side. Therefore, this change of processing from the application on the client side to the database on the server side enhances and improves the performance of the application.
The procedural code stored within the database can be reused anywhere within the application.
When a stored procedure is executed for the first time, the shared SQL area in the System Global Area (SGA) of the database stores the parsed versions of the executed commands. After this, whenever the command is executed, it takes the advantage of the parsing, which was performed the first time, and improves the performance of the procedure's execution.
a Developmental efforts are reduced by keeping the code in a stored procedure. As the business rules are part of the database, they need not be re-written in each code module of the application; and therefore, saves the creation time of the application.
As the code is stored in a common place, any change can be easily managed; and therefore, reduces the maintenance effort.

39.   What is a package and how does it differ from procedure and function?
A package is a group of logically related PL/SQL subprograms bundled together with PL/SQL types and other associated objects. It consists of the following two parts:
a Package specification —Includes declaration of all the objects and subprograms that are part of the package.
Package body—Includes the total definition of all the parts of a package that have been declared in the specification.
The whole package is fully loaded into the memory when any package construct is called for the first time. Therefore, it does not require any disk input/output on later calls to constructs in the same package.

40.   What is the difference between private package construct and public package construct?
Public package constructs are declared in the package specification but defined in the package body and can be invoked from any other object in the oracle environment.
Private package constructs are those that are declared and defined only within the package body. They are not declared in the package specification and can only be referenced by other constructs, which are part of the same package.
Private package construct is always defined before a public package construct within the package body.

41. Can a complete package be called?
No, a complete package is not possible to call, invoke, or parameterize; however, any construct from the package can be called or invoked.

42. What is the difference between local and global variables within a package?
A local variable is defined within a package body and is only available to the constructs within the same package.
A global variable is one that is declared in the package specification. This can be referenced outside the package and is visible, to external objects that have access privilege on the package.

43. Is it possible to write a package specification without a package body?
Yes, it is possible to write a package specification without a package body but not the vice versa. This is specifically used if there is a package, which is only used for the declaration of public variables, types, constants, and exceptions. In such case, there is no need for a package body and only package specification is enough.
a_package in the a_user schema. Write the statement to invoke the a_proc procedure on the SQL prompt from the user, b_user.
The statement is given as follows:EXECUTE a_user.a_package.a_proc(10);

46. When does a package need to be recompiled?
A package needs to be recompiled completely whenever any changes are made in the package specification. Any change to the package body does not require recompilation of the package.

47. What are the advantages of using packages?
The main advantages of using packages are given as follows:
Encapsulation —Packages provide encapsulation of all the logically related constructs together in a single module, which is easy to code and understand.
Better application design —Both modularity and encapsulation leads to better designing of applications. Separate package specification and body helps in simplifying the coding and better definition of the subprograms.
Hiding —Public and private constructs can be defined and declared so that any definition can be hidden to protect integrity of the package. Any changes in the private constructs only need the package body to be recompiled without the need for the entire application to be recompiled.
Better performance —A call to any construct within the package loads the entire package in the memory; therefore, enables better performance of the application.

48. Is DROP PACKAGE command used to drop the package specification or the package body?
The DROP PACKAGE command is used to drop the entire package including the specification and the body.

49. Is it possible to drop the package body without dropping the package specification?
Yes, it is possible to drop the package body without dropping the package specification using the DROP PACKAGE BODY command.

50. How the concept of overloading is implemented within a package?
PL/SQL allows you to overload the subprogram names and type methods. The package can include subprograms that have the same name as long as they have different formal parameters.
PL/SQL determines which subprogram to be called depending on the formal parameters. In PI/SQL, only packaged subprograms can be overloaded.

51. What is forward declaration and how can it be achieved within a package?
 PL/SQL does not allow the use of any subprogram before it is declared and defined. However, in a package, forward declaration is allowed in case the subprogram specification is terminated by a semi-colon. This enables the subprogram to be defined in a logical manner and groups the subprograms in a package. It also helps in defining mutually recursive programs that call each other directly or indirectly.

52. What is a One-Time-Only procedure and how is it declared within a package?
A One-Time-Only procedure is executed only once, when the package is called for the first time. While declaring a One-Time-Only procedure the keyword END is not used at the end of the one-time-only procedure; however, the procedure uses the package body's END clause, as the procedure's code is placed at the bottom of the package body.

53. Which privileges are required to execute a subprogram owned by another user?
 To execute a subprogram owned by another user, the user must be granted either the EXECUTE privilege on a procedure or function or the EXECUTE ANY PROCEDURE system privilege.

55. What  is  the  significance  of AUTHID  property  while creating a procedure?
The AUTHID property affects the name resolution and privilege checking of SQL statements at runtime; however, it does not affect the compilation, and has no meaning for blocks that have no code, such as collection types.
Any procedure may be created with its AUTHID property set to either the DEFINER or the INVOKER rights. If a procedure  is created with the DEFINER rights, then the user executing the procedure need not have an access to database objects, which the procedure is accessing. However, if a procedure is created with the INVOKER rights, then the user must have access rights to all the objects that the procedure is accessing.

56. How can you bug a procedure or function for errors?
The procedures or functions can be debugged using the SHOW_ERROR command or the DBMS_OUTPUT command within the subprogram code.

58. When does a subprogram need recompilation?
When any database object associated with the subprogram changes, the program needs to be recompiled. When the program is executed the next time, after the object is changed, the system will automatically perform runtime recompilation.
To avoid this performance overhead, it is better to explicitly recompile the subprogram using the ALTER [PROCEDURE | FUCNTION] command, as shown in the following statement: ALTER PROCEDURE proc_get_marks compile;

60. When should a procedure and a function be used?
Procedure is used when there are more than one return values however, if there is only one return value, then a function should be used. Although functions may have more than one OUT parameters, it is considered as a poor programming style.
In addition, when a subprogram has to be invoked within an SQL statement, function should be used.

61. Can stand-alone programs be overloaded?
No, stand-alone programs cannot be overloaded; however, packaged sub-programs can be overloaded within the same package.

62. What are function purity levels?
Function purity levels define what type of data structures can be read or modified by a function. There are four function purity levels, which are given as follows:
?     WNDS —Writes no database state. The function does not modify any database table using any DML statement.
?     RNDS—Reads no database state. The function does not read any database tables using the SELECT statement.
?     WNPS —Writes no package state. The function does not modify any packaged variables.
?     RNPS— Reads no package state. The function does not read any packaged variables.

63. What are the restrictions on functions that are called within SQL statements?
The functions that are called within SQL statements have the following restrictions:
a. They must be stored in a database.
b. They cannot modify any database table.
c. They can only take the IN parameter modes. The OUT and IN OUT parameter modes are not allowed in a function.
d. They can only use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.
e. The return type of a function should be of SQL datatype.
f. They cannot have the COMMIT and ROLLBACK statements.
h.      They cannot have the ALTER SESSION and ALTER SYSTEM commands.

64. Which of the following is a correct option to input a parameter to a procedure from a calling block?
a. Studentjd OUT STUDENT.STUDENTJD%TYPE
b. Studentjd IN OUT NUMBER (2)
c. STUDENT OUT NUMBER
d. STUDENT IN STUDENT.STUDENT JD%TYPE
Option d is correct because it uses the IN parameter mode and defines the parameter using the %TYPE datatype for the STUDENTJD column in the STUDENT table.

66. Suppose a procedure proc__calc_student_marks inserts the values into the student_total_marks table. If the table is modified by adding another column, what needs to be done to make the procedure valid?
Oracle recompiles the procedure automatically when the procedure is invoked. Therefore, no action has to be taken if the INSERT statement is using column names. However, if the INSERT statement is just adding values without using the column names, then the procedure has to be modified, as another column has been added.

67. Suppose a procedure is created using the CREATE PROCEDURE command; however, it gives a syntax error.
Which data dictionary views will have the source code of the procedure?
 The USER_SOURCE, ALL_SOURCE, and DBA_SOURCE data dictionary views will have the source code, as a procedure is stored in the database even if it has a syntax error.

68. Can a parameter use a sequence generator for the assignment of default value to a parameter?
No, a sequence generator cannot be used to assign a default value to a parameter.

69. Can an OUT parameter, v_total_marks, be referenced within the procedural code without being assigned a value?
 Any OUT parameter cannot be referenced without being assigned a value, as the OUT parameter cannot be assigned a default value. Therefore, the v_total_marks parameter cannot be referred within the code until the executable statements of the procedure have begun; the OUT parameter is assigned a value
using the PL/SQL assignment statements.

70. Can an IN parameter be assigned a value within a procedure?
No, an IN parameter cannot be assigned values within a procedure.

71. Suppose a procedure proc_get_lighest_rank is stored in the user schema, user_a. How can you access the procedure within this schema?
The proc_get_hlghest_rank procedure can be accessed within the user_a schema from another stored procedure. It can also be accessed either from an anonymous PL/SQL block executed on the SQL prompt logged in as user_a or directly at the SQL prompt using the EXECUTE command. In addition, it can be accessed by other users if they have the EXECUTE privilege.

72. Can BOOLEAN datatype be used in functions that are called from SQL statements?
No, BOOLEAN datatype cannot be used in functions that are called from SQL statements.

73. Is it possible to make procedure or function part of the IF THEN ELSE block?
Procedure cannot be called from within the IF THEN ELSE block however, functions can become a part of the IF THEN ELSE block because functions return the value in such a manner that the function call in the block acts like a variable.

74. Can you invoke a stored function or procedure from the Oracle Forms in the database?
Yes, a stored procedure or function can be invoked from the Oracle Forms and the result can be used for further programming.

75. What is the meaning of the following error statement:ORA-06575: Package or function func_test is in an invalid state?
This error is displayed if the function is executed when it is in the invalid state. The ORA-06575 error indicates that for some reason the function is not valid and needs to be checked and compiled again.

Posted in: PL/SQL Frequently asked questions