Skip to main content

What are the advantage of packages in oracle sql plsql interview question and answer?

 Advantages of packages in oracle pl sql

It is more used in software engineering in oracle sql pl/sql development career. It provides the important features like reliablity,maintainability,resusablity of codes and used in the large and complex systems by using the group of team efforts. It will group the related procedure,function,variable and making the code which is easier to maintain and manage. Codes are reused in the multiple applications which reside in it reducing duplication and maintenance problems which occur in the future. 









It is loaded into the memory as per the session. It avoids repetitive use and improve the speed of execution of the source codes. It supports the overloading with the multiple procedures or functions with the same name exist with different and various parameters. It acts like the code management in inventory management in which the several sub-programs have come under the single package for add and modify item. It is used instead of the standalone subprograms.

Modularity:
It helps the programmer to encapsulate all the logical related items, variables,constants,cursors,exceptions,types and sub-programs in the named PLSQL module. Each package is very easy to understand. Its interfaces are very simple, clear and easy to understand for the group of programmers which are working on the specific project. It helps in the application development.

Build in easier application design:
Whenever you have to design the application, you need to know about the interface which is in the package specification. You can do coding work and compile the source codes without package specification without its body. Stored sub-program which is referenced to the package can be compiled as per business requirements. You don't need to define the package body fully unless the complete application is ready.

Information Hiding:
With the help of packages, we can specify the types,items,sub-programs are made public and private. Public which refers to the visible and accessible. Private which refers to the hidden and inaccessible. If the package contains the four programs, out of which three are made public and out of which one is made private. It hides the implementation of the private subprogram. If the implementation gets changed, then only the package will be impacted not the entire running application. It simplifies the maintenance and enhancement problem. Hiding the implementation details from the end users can protect the integrity of the package.

Add functionality:

Package public variables and cursors are responsible for the duration of the session. Added functionality are shared by all the subprograms which are executing in the environment. It maintains the data which are using in the transaction without storing in the database.

Better(best) performance:

Whenever the package sub-program is called for the first time, the whole package is loaded into the memory. Related sub-program will not require input or output disk, if it is called later. It stops the cascading of dependencies and try to avoid or stop the unwanted or unrequired compilation. If you change the body of the package function, oracle will not recompile all the sub-program which are calling the function. Sub-programs which depend on the parameters and return value which are declared in the package specification. They can be recompiled if the specification get changes.


Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea,IBM and Mphasis etc" INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results.In other words,multiple select statements of similar column name in same order and having same data type to produce the unified result.In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of co...
Post a Comment
Read more

Difference between decode and case oracle sql plsql interview question and answer

" This is the most accepted question for PLSQL developer role.It has been asked in many interviews like Mphasis and Kyndryl. "  INTRODUCTION: Case was came after oracle database version 8.1.6 whereas decode was came before oracle database version 8.1.6 .Decode and case statements in an oracle work or acts like if then elsif conditional statements. if sal='2000000' then 'General Manager' elsif sal='3000000' then 'Senior Manager' elsif sal='1200000' then 'Manager' else 'Employee' end if; create table student_details(Student_RollNo Number,Student_Name Varchar2(20),student_marks Number,student_result varchar2(1)); insert into student_details values (100,'Rahul',90,'P'); insert into student_details values (101,'Rakesh',30,'F'); insert into student_details values (102,'Ram',60,'P'); insert into student_details values (103,'Rohan',10,'F'); insert into student_detai...
Post a Comment
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Post a Comment
Read more