pragma exception_init : It associates an exception name with oracle error number.ORA_error is handled by writing specific handler. Word pragma signifies or mean that it is compiler directive. Function SQLCODE is used to return the valid oracle error number.EXCEPTION_INT is used in the declarative part (before BEGIN keyword) of any PLSQL block, subprogram or package. Pragma must be appeared in the declaration section as it is associative exception which is used somewhere after the exception declaration. One exception name is assigned to the particular error number. It is allowed to handle the specific errors using the custom exception names. It is directive used to handle the user defined exception with a specific error number. It is useful for mapping oracle error codes like ORA-00942, ORA-01400 to user defined exceptions which enables more readable and structured error which is going to handle in the code. Error number in the future may arise conflict among the unrelated application u...

 

 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 standalon...

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

Collection: It is the group of elements having the same data type and it is made of arrays, lists and sets. It is divided into three parts i.e.  Varray. Nested Tables. Associative array or index by tables. It is used for storing large chunks of data and performing manipulation operations with the help of keyword BULK Collect. Collection has built in methods which are as follows: count : It gives the number of methods in the collection. delete: It will delete all the elements from the collection and setting count to zero. delete(n): It  will delete nth element from the collection. If mth element is null, then no action is performed. delete(n,q): It will delete elements from nth position to q position. exists(n): Return true if n element is present in the collection else returns false. extend: It adds null element to the collection end. extend(n): It  add m element to the collection end. extend(n,q): It add n copies of the qth element to the collection end....