A.
A.
TRANSLATE:
Translate function does character by character substitution in a string
format:
TRANSLATE(STRING,IF,THEN)Translate looks at each character in 'STRING' and then check 'IF' to see if that character is there, if it is there then it notes the position in 'IF' where it found the character and then looks the same position in 'THEN' for example :
SELECT TRANSLATE(7671234,234567890,'BCDEFHIJ') from dual;Result of query above is: GFG1BCD
REPLACE:
REPLACE function replaces a character or characters in a string with zero or more characters.
REPLACE('ADAH','A','BLAH')This evaluate the string 'ADAH'.
A.
TRUNCATE is much faster than DELETE.
(Reason: When you type DELETE. All the data get copied into the ROLLBACK Tablespace first then delete operation get performed. That`s why when you type ROLLBACK after deleting a table, you can get back the data(The system get it for you from the Rollback Tablespace). All this process take time. But when you type TRUNCATE, it removes data directly without copying it into Rollback Tablespace.That's why truncate is faster.Once you truncate you cannot get back the data.)
A.
SELECT DEPARTMENT_ID, EMPLOYEE_ID, SALARY FROM EMPLOYEE E_0 WHERE 1= ( SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE E_I WHERE E_0.DEPARTMENT_ID = E_I.DEPARTMENT_ID AND E_0.SALARY<=E_I.SALARY );
A.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS D WHERE EXISTS ( SELECT 1 FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID);
A.
Using the IN clause, you`re telling the rule-based optimizer that you want the inner query to drive the outer query(IN= inside to outside)
When you write EXISTS in a where clause,you`re telling the optimizer that you want the outer query to be run first, using each value to fetch a value from inner query(EXISTS= outside to inside)
Note − Exists is much faster than IN(when subquery result is very large)
IN is faster than EXISTS(when subquery result is very small)
CREATE TABLE my_data ( v VARCHAR2(100), n NUMBER(4), d DATE ) /Then I construct following block
DECLARE c_maximum CONSTANT INTEGER := 100; TYPE strings_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; l_strings strings_t; l_counter PLS_INTEGER := l_strings.FIRST; BEGIN LOOP INSERT INTO my_data VALUES ( TO_CHAR (l_counter), l_counter, SYSDATE + l_counter); l_counter := l_counter + 1; EXIT WHEN l_counter > c_maximum; END LOOP; COMMIT; END; /How many rows will be in the table if I execute this block ?
A.
0
Note − Whenever you are using a variable as an iterator inside a loop, make sure that it is initialized to a non-NULL value (usually 0). Otherwise, the iterator will almost certainly stay NULL throughout the entire loop's execution.So the script will stuck in loop.
* ** *** **** *****
A.
SELECT LPAD('*',LEVEL,'*') FROM dual CONNECT BY LEVEL<=5;
A.
An exception is a run time error or warning condition, which can be predefined or user defined. Predefined exceptions are raised implicitly (automatically) by runtime system. User defined exceptions must be raised explicitly by RAISE statements.
The WHEN OTHERS clause must be the last exception handler in the exception section. If you place any other WHEN clause after WHEN OTHER, you will receive following compilation error:
PLS-00370: OTHERS handlers must be last among the exception handlers of a block.
A.
SQLCODE : it returns the error number for the last encountered error.
SQLERRM : it returns the actual error message of the last encountered error.
A.
PRAGMA EXCEPTION_INIT statement associate the declared exception with standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.
PRAGMA (also called pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when PL/SQL block is executed. Rather, it directs the PL/SQL compiler to interpret all occurrences of the exception name within the block as the associated Oracle server error number.
Syntax
PRAGMA EXCEPTION_INIT(exception, error_number);
A.
UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
UNION ALL does not remove duplicates and it therefore faster than UNION.
A.
There are two types of triggers based on the which level it is triggered.
A.
A trigger is a pl/sql block structure which is fired when a DML statement like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML is executed.