varchar  2000, varchar2 4000 raw 32767 blob 2gb

months_between()

coalesce will return first not null value

coalesce(val1,val2,val3), if val1 is null then val2.. and so on

translate single char, replace word by word

translate goes by one char to one char mapping

replace picks as word

 

#What is key preserved table?

A table is set to be key preserved table if every key of the table can also be 

the key of the result of the join. It guarantees to return only one copy of each

row from the base table.

#WITH CHECK OPTION

if you create a view with check option then if you update or insert through view

it will not allow if that insert or update , changes the view filter query

CREATE VIEW ford_cars AS SELECT car_id FROM cars WHERE brand_id = 3 WITH CHECK OPTION;

u can not update this view which changes brand_id from 3 to 2

#What is a sub query and what are the different types of subqueries?

Correlated sub query:

1) can not be independent

2) can access column from outer query in inner query

non-Correlated sub query:

1)can be run independent

2)result from inner is submitted to main outer query

#cursor attributes

Attributes of Cursor are

%FOUND

Returns NULL if cursor is open and fetch has not been executed

Returns TRUE if the fetch of cursor is executed successfully.

Returns False if no rows are returned.

%NOT FOUND

Returns NULL if cursor is open and fetch has not been executed

Returns False if fetch has been executed

Returns True if no row was returned

%ISOPEN

Returns true if the cursor is open

Returns false if the cursor is closed

%ROWCOUNT

  Returns the number of rows fetched. It has to be iterated through entire cursor

  to give exact real count.

#12 is the maximum number of triggers that can be applied to a single table.

Classification based on the timing

BEFORE Trigger: It fires before the specified event has occurred.

AFTER Trigger: It fires after the specified event has occurred.

INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for DML )

Classification based on the level

STATEMENT level Trigger: It fires one time for the specified event statement.

ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)

Classification based on the Event

DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)

DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)

DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

There are 3 types of Exceptions.

a) Named System Exceptions

NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.

TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.

ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.

CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.

INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.

INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.

DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.

LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.

NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.

VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unnamed system exception.

These exceptions do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or

2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.

EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.

c) User-defined Exceptions

defined, raised using raise, then capture in exception

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i') FROM dual;

Result: 'e'