ORACLE AND MSSQLSERVER

What is CYCLE/NO CYCLE in a Sequence? CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending...

What are the uses of MERGE? MERGE is used to combine multiple DML statements into one. Syntax : merge into tablename using(query) on(join condition) when not matched then [insert/update/delete] command when matched then [insert/update/delete] command

Explain the uses of Control File. It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file...

'Performance tuning' is the term used to improve the performance of a query..that is, quickening the query execution time...a query that involves a large no of db tables may take around 20-30 min to execute for ex... In such case, the developer shud fine...

Differ between DECODE and CASE. DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place. select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from...

Explain autonomous transaction. An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction. There are several situations to use autonomous transactions like event...

What is the importance of SQLCODE and SQLERRM? SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error

Explain Commit, Rollback and Savepoint. For a COMMIT statement, the following is true: Other users can see the data changes made by the transaction. The locks acquired by the transaction are released. The work done by the transaction becomes permanent....

What are the cursor attributes used in PL/SQL? %ISOPEN: it checks whether the cursor is open or not. %ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT. %FOUND: it checks whether cursor has fetched any row. If...

What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL? Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified...