Question:- What are the Advantages of Stored Procedures?
Answer:- Stored procedures have various advantages to help you design sophisticated database systems. Some of the advantages of stored procedures as listed below: • Better performance • Higher productivity • Ease of use • Increased scalability • Interoperability • Advance security • Replication
Question:- What are the Various Types of Schema Objects that can be Created by PL/SQL?
Answer:- There are various types of schema objects that are created by PL/SQL. Some of them are mentioned below: • Stored procedures, packages, and functions • Object tables, object types, and object views • Database triggers • Database links • Cursors • Table • View
Question:- What is the Advantage of Implicit Records?
Answer:- Implicit records are handy since they do not require hard-coded descriptions. Because implicit records are based on database table records, any changes to the database table records will be reflected in the implicit records automatically.
Question:- What are the Ways of Commenting in PL/SQL?
Answer:- In PL/SQL, comments help readability by describing the purpose and function of code portions. Two types of comments are available in PL/SQL. They are as follows: • Single-line Comments: Single-line comments start with a double hyphen (- -) at the beginning of a line and go all the way to the conclusion. • Multi-line Comments: Multi-line comments start with a slash-asterisk (/*) and terminate with an asterisk-slash (*/), and they can span across several lines.
Question:- What is %TYPE?
Answer:- The %TYPE property is used to declare a column in a table that includes the value of that column. The variable’s data type is the same as the table’s column.
Question:- What is %ROWTYPE?
Answer:- The %ROWTYPE property is used to declare a variable that contains the structure of the records in a table. The variable’s data type is the same as the table’s columns.
Question:- Differentiate between a Temporary Tablespace and a Permanent Tablespace
Answer:- A temporary tablespace is used to store temporary items such as sort structures, while a permanent tablespace is used to store things that will be used as the database’s genuine objects.
Question:- How Many Triggers can be Applied to a Table?
Answer:- A maximum of 12 triggers can be added to a table.
Question:- What is a Mutating Table Error?
Answer:- A mutating table error occurs when a trigger tries to update a row that is currently in use. It can be fixed by using views or temporary tables so that the database selects one and updates the other.
Question:- What does the PLVtab Enable you to do when you Show the Contents of PL/SQL Tables?
Answer:- PLVtab enables you to do following when you show the contents of PL/SQL tables: • Display or suppress the row numbers for the table values • Show a prefix before each row of the table • Display or suppress a header for the table
Question:- How can you Save or Place your msg in a Table?
Answer:- To save a msg in a table, you either load the individual messages with calls to the add_text procedure or load sets of messages from a database table using the load_from_dbms procedure.
Question:- What are Pseudocolumns and how do they work? How can Pseudocolumns be used in Procedure Statements?
Answer:- Pseudocolumns aren’t genuine table columns but they behave like them. Pseudocolumns are used to retrieve specific information in SQL statements. Although pseudocolumns are recognized by PL/SQL as part of SQL statements, they cannot be used directly in a procedural language. The following are the pseudocolumns that are used: • CURRVAL and NEXTVAL • LEVEL • ROWID • ROWNUM
Question:- What is the use of the || Operator?
Answer:- The strings are concatenated using the || operator. The || operator is employed by both DBMS_OUTPUT.put line and select statements.
Question:- What is the difference between Sqlcode and Sqlerrm, and why are they important for Pl/SQL Developers?
Answer:- The value of the error number for the most recent error detected is returned by SQLCODE. The SQLERRM function returns the actual error message for the most recent issue. They can be used in exception handling to report or save the error that happened in the code in the error log database. These are especially important for the exception WHEN OTHERS.