meisteroreo.blogg.se - Interview questions for oracle sql developer

#Interview questions for oracle sql developer update
#Interview questions for oracle sql developer code
#Interview questions for oracle sql developer series

%ROWCOUNT : The number of rows that are updated, deleted or fetched. %ISOPEN : Checks if the cursor is open or not TTITLE and BTITLE commands that control report headers and footers.Ģ8) Show the cursor attributes of PL/SQL. Group functions give results based on groups of rows, as opposed to individual rows. INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions.

#Interview questions for oracle sql developer series

It is much more efficient as each statement is not individually stripped off.Ģ4) What packages are available to PL SQL developers?ĭBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

#Interview questions for oracle sql developer code

Thus, all PL/SQL code is sent directly to database engine. No, SQL*Plus does not have a PL/SQL Engine embedded in it. IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.Ģ3) Does SQL*Plus also have a PL/SQL Engine? NVL converts NULL to another specified value. The %ISOPEN cursor status variable can be used.Ģ1) Show the two PL/SQL cursor exceptions. SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.Ģ0) If a cursor is open, how can we find in a PL SQL Block? If it is used, it must come first in a stand alone file.ġ8) How many triggers can be applied to a table?Ī maximum of 12 triggers can be applied to one table.ġ9) What is the importance of SQLCODE and SQLERRM?

It is fixed by using views or temporary tables, so database selects one and updates the other.ġ7) When is a declare statement required?ĭECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures.

#Interview questions for oracle sql developer update

It occurs when a trigger tries to update a row that it is currently using.

This allows the application to process each row sequentially as the cursor returns it. If a query returns multiple rows of data, the program defines an explicit cursor. The user cannot control or process the information in this cursor. With SAVEPOINT, only part of transaction can be undone.ġ5) Define Implicit and Explicit Cursors.Ī cursor is implicit by default. It undoes all the work done by the user in a transaction.

All locks acquired by transaction are released.

The work done in a transition is undone as if it was never issued.

The work done by the transaction becomes permanent.Ī ROLLBACK statement gets issued when the transaction ends, and the following is true.

The locks acquired by the transaction are released.

Other users can see the data changes made by the transaction.

For eg, SELECT INTO statement, which does not return any rows.ġ4) Explain Commit, Rollback and Savepoint.įor a COMMIT statement, the following is true: For eg, incorrect spelling.Ī runtime error is handled with the help of exception-handling section in an PL/SQL block. P-code is stored in the database for named blocks and is used the next time it is executed.ġ3) Differentiate between Syntax and runtime errors.Ī syntax error can be easily detected by a PL/SQL compiler. P-code is a list of instructions for the PL SQL engine. When all errors are corrected, a storage address is assigned to the variables that hold data. Syntax checking checks the PL SQL codes for compilation errors. Table columns are referred as OLD.column_name and NEW.column_name.įor INSERT related triggers, NEW.column_name values are available only.įor DELETE related triggers, OLD.column_name values are available only.įor UPDATE related triggers, both Table columns are available.ġ1) What are the rules to be applied to NULLs whilst doing comparisons?Ģ) NULL cannot be equal or unequal to other valuesģ) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)Ĭompilation process includes syntax check, bind and p-code generation processes. Procedure is called as a statement in PL/SQL.ġ0) Explain two virtual tables available at the time of database trigger execution. It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.ĩ) Show how functions and procedures are called in a PL SQL block.įunction is called as a part of an expression.