Procedures - Functions - Packages Interview Questions

What are advantages of Stored Procedures ?

To help you build powerful database applications, stored procedures provide several advantages :

• Better performance
• Higher productivity
• Ease of use
• Increased scalability
• Simplifies maintainability
• Interoperability
• Security
• Replication

What are the modes of parameters that can be passed to a procedure ?

In, Out and In-Out are different modes of a PL/SQL Procedure.

How we can create a table through procedure ?

Using dynamic sql.

Explain about Package in short.

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. Packages support the development and maintenance of reliable, reusable code with modularity, easier application design, information hiding, and better performance.

A package has two parts : specification which declares the public items and body to code for public subprograms.

Name the tables where characteristics of Package, procedure and functions are stored ?

user_objects

What is the difference between procedure and function ?

• A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.Although, OUT parameters can still be used in functions, but restricts a function from being used in a SQL Statement.
• Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't.
• Functions are normally used for computations where as procedures are normally used for executing business logic.
• Stored procedure is precompiled execution plan where as functions are not.
• Oracle provides the provision of creating "Function Based Indexes" to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

What is the difference between package and procedure ?

Package is a collection of procedures and functions while procedure is a standalone pl/sql unit.

Which is best to use package or procedure ?

Always use a package. Never use a standalone procedure except for demos, tests and standalone utilities because packages

• break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)
• support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
• increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding
• support overloading
• support session variables when you need them
• promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together...
• have better performance

Packages have better performance, Why ?

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

The scope of a cursor declared in a procedure is limited to that procedure only. While the scope of cursor declared in a package specification is global.

Can we have a procedure declared in package body but not declared in package specifications ?

Yes. But that procedure will be local and can not be called by other procedures in the package. When we declare a proc in package spec, it becomes global for the package.

Can we have a package spec without a body ?

Yes

Can we have a procedure spec without body ?

No. It should atleast have NULL; inside begin and end.

Name some Oracle's built-in packages ?

• DBMS_OUTPUT - used to display a message, especially while debugging
• DBMS_PIPE - This allows 2 different sessions of the database to communicate over the named pipe.
• DBMS_DDL - Contains procedures to wrap single PL/SQL units like package specs, package body, function, procedure, type specs, type body, etc. Eg : DBMS_DDL.CREATE_WRAPPED();
• DBMS_ALERT - lets you use database triggers to alert an application when some database values change
• UTL_FILE - it allows the PL/SQL program to read from and write into text files.
• UTL_SMTP - allows the PL/SQL program to send emails over Simple Mail Transfer Protocol
• UTL_HTP, HTF - allows PL/SQL to generate HTML tags.
• UTL_HTTP - allows PL/SQL programs to make HTTP calls. It gets the data from Internet. The inputs ar the URL and the contacts of the website. The return data is usually in HTML format.
• DBMS_SQL - Helps you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. For ex: it allows us to create a procedure based on a table, whose name is given at run-time.