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.
Question:- What is raise_application_error?
Answer:- This procedure can be used to send user-defined error messages from stored subprograms. You can prevent returning unhandled exceptions by reporting failures to your application. It appears in two places, the executable section and the exceptional section.
Question:- In PL/SQL, how can you verify whether an Update Statement is Executed or not?
Answer:- The SQL % NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run had no effect on any rows, this variable returns TRUE.
Question:- Explain the Day-to-day Activities in PL/SQL.
Answer:- • Create database objects—tables, synonyms, sequences, etc. • Implement business rules, create procedures, functions, etc. • Impose business rules, create constraints, triggers, etc. • Create cursors for data manipulation
Question:- How can you locate a PL/SQL Block when a Cursor is Open?
Answer:- The %ISOPEN variable cursor status can be used to find the PL/SQL block.
Question:- What do you know about pragma_exception_init in PL/SQL?
Answer:- The pragma_exception_init command in PL/SQL instructs the compiler to associate an exception name with an Oracle error number. This enables one to refer to any internal exception by name and create a custom handler for it.
Question:- In PL/SQL, what are the differences between Stored Procedure and Stored Function?
Answer:- The key differences between stored procedure and stored function are: Returning the value in a stored procedure is optional, while returning the value in a stored function is required. A stored procedure can have both input and output parameters, while a stored function can only have either an input parameter or an output parameter. Exception handling is possible in a stored procedure, whereas it is not possible in a stored function.