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.
Question:- How to Display Records having the Maximum Salary from an Employee Table?
Answer:- Select * from emp where sal= (select max(sal) from emp)
Question:- What is the Syntax to Disable a Trigger?
Answer:- ALTER TRIGGER TRIGGER_NAME DISABLE;
Question:- How to Display the Highest Salary from an Employee Table?
Answer:- Use the following code to display the highest salary from an employee table: Select max(sal) from emp;
Question:- Which Command is used for Deleting a Package?
Answer:- The command used for deleting a package is DROP PACKAGE
Question:- How to Display the Second Highest Salary from an Employee Table?
Answer:- Select max(sal) from emp where sal not in ( select max(sal) from emp
Question:- How can you view the User-defined Functions and Procedures in PL/SQL?
Answer:- The table USER SOURCE is used to store user-defined functions and procedures. To examine them, the function and procedure names should be specified in uppercase (in select command). The following command is used to inspect the source code of a user-defined function or method: Select text from user_source where name=’PROCEDURE_NAME’;
Question:- In PL/SQL, what is the Purpose of the DBMS_OUTPUT Package?
Answer:- The PL/SQL output is shown on the screen using the DMS_OUTPUT package. get_line, put_Line, new_line, and many more are found in DBMS_OUTPUT. The put_line procedure, which is a part of the DBMS_OUPUT package, is used to display the information in the line.
Question:- How can you Execute a Stored Procedure?
Answer:- There are two steps to execute a stored procedure: Use the EXECUTE keyword. The EXEC keyword can also be used. Call the name of the procedure from a PL/SQL block. Syntax EXECUTE procedure_name; Or Exec procedure_name;
Question:- What are the differences between ANY and ALL operators?
Answer:- ALL Operator: Value is compared to every value returned by the subquery using the ALL operator. • > ALL denotes greater than the maximum • < ALL denotes less than the minimum • <> ALL is the same as NOT IN condition ANY Operator: Value is compared to each value returned by the subquery using the ANY operator. SOME is a synonym for ANY operator. • ANY denotes something more than the bare minimum • < ANY denotes a value lower than the maximum • = ANY is the same as the IN operator