If you want to execute the same sequence of statements for more than one exception then list the exception names separated by the keyword OR. We should not use the others exception in the list separated by OR. It must appear by itself
If you want to execute the same sequence of statements for more than one exception then list the exception names separated by the keyword OR. We should not use the others exception in the list separated by OR. It must appear by itself
Compiler hint nocopy
By default in subprograms
IN parameter is passed by reference.
OUT and IN OUT parameters are passed by value.
When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.
NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned
By default, if a subprogram exits with an unhanded exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
A subprogram can have only one other exception in a program.
If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.
If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
From Session1
From Sesion2Code:SQL> UPDATE emp 2 SET sal = 5000 3 WHERE empno = 7369; 1 row updated.
SQL> ALTER TABLE emp ADD (Dateofjoin DATE);
ALTER TABLE emp ADD (Dateofjoin DATE)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database irrespective of whether it is hard parsing or soft parsing.
A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
Why do you want to join our team?
The BEFORE and AFTER options cannot be used for triggers created over views.
Difference between Object Type and Record Type
Record type works only in Pl/Sql.
Object type gets stored in database.
We can use it both in sql and pl/sql.
A goto statement can’t be branch to an exception handler nor can it branch from an exception handler to the current block
Cast Function
cast function converts one datatype to another.
It is available from Oracle 9i version.
Syntax:- cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )
Ex:- SELECT CAST(SUBSTR('ABZ123',-3,3) AS NUMBER) FROM DUAL
In the above example cast converts last three digits of string to number data type.
How to find number of ‘e’s in a word ?
SELECT length('geekinterview.com') - length (replace ('geekinterview.com', 'e'))
FROM DUAL;
Exceptions cannot be propagated across remote procedure calls
ADD_MONTHS returns the date plus integer months.
If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.Otherwise, the result has the same day component as date.
Code:SQL> SELECT add_months('31-JAN-2008',1) 2 FROM dual; ADD_MONTH --------- 29-FEB-08 SQL> SELECT add_months('31-JAN-2008',2) 2 FROM dual; ADD_MONTH --------- 31-MAR-08
Aggregate functions can appear in
- select lists
- ORDER BY
- HAVING clauses
Single Row functions can appear in
- select lists
- WHERE clauses
- START WITH
- CONNECT BY clauses
- HAVING clauses.
We can create a view with the same name in two ways.
1. A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.
2.A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current definition of a view, but preserves the present security authorizations.
when referring to the new or old values in a WHEN clause, you must omit the colon:
WHEN (:NEW.mgr is null)-- is wrong.
It will raise error
"ORA-00920: invalid relational operator"
How to goto dos prompt from Sql *Plus?
At sql prompt type $ or host and press enter
Oracle database contains both logical and physical structures.
Logical structures are
Table space
Schema
Extent
Segment
Physical structures are
Data files
Redo Log files
Control files