Cache and NoCache

• The cache option specifies how many sequence values will be stored in memory for faster access.

• Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

Nowait

NoWait specifies that the database should not wait for a lock to be released.

ORA-01000 - Maximun open cursors exceeded

If the error fired, have to increase MAXOPENCURSOR parameter in init.oraor have to alter the system parameter

ALTER SYSTEM SET OPEN_CURSORS = 1000 SCOPE=BOTH

It sets an array up to 1000, but not allocate 1000 cursors.

init.ora

The init.ora file stores the initialization parameters of oracle. The values that are currently in effect can be viwed through v$parameter.The default location of init.ora is %ORACLE_HOME%\database on windows.The location can be changed by changing ORA_%ORACLE_SID%_PFILE. The default name for the file is init%ORACLE_SID%.ora.However, it is possible to start the database with another init.ora file than the default one. In this case, there is noway to determine which init.ora was used when the database is running.

Query Collections

SELECT REPLACE(TRANSLATE('bernandas_S','@#^&$%*','.'),'.',NULL) FROM table_name;

outp

bernandas S

Sequence of trigger when form is opened & closed

Form Load

1. Pre-Logon From
2. On-Logon Form
3. Post-Logon Form
4. Pre-Form Form
5. When-Create-Record Block
6. Pre-Block Block
7. Pre-Record Block
8. Pre-Text-Item Item
9. When-New-Form-Instance Form
10. When-New-Block-Instance Block
11. When-New-Record-Instance Block
12. When-New-Iten-Instance Item

--------------------------------------------
Form Exit

1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. Post-Form Form
5. On-Rollback Form
6. Pre-Logout Form
7. On-Logout Form
8. Post-Logout Form

The TRUNC() function

ON NUMBERS
This function allows to make two types of action on a number:
- Keep the number of wished decimals (precision > = 0)
- Centre the whole part of a number in a range (precision <>
------------------------------------
select
trunc(12.98764, 5) "+5 dec."
trunc(12.98764, 4) "+4 dec."
trunc(12.98764, 3) "+3 dec."
trunc(12.98764, 2) "+2 dec."
trunc(12.98764, 1) "+1 dec."
trunc(12.98764, 0) "+0 dec."
from dual ;

+5 dec. +4 dec. +3 dec. +2 dec. +1 dec. +0 dec.
---------- ---------- ---------- ---------- ---------- ----------
12,98764 12,9876 12,987 12,98 12,9 12
---------------------------------------------------------------

select
trunc(1234567,-6) "million"
trunc(1234567,-5) "cent-mille"
trunc(1234567,-4) "dix-mille"
trunc(1234567,-3) "mille"
trunc(1234567,-2) "cent"
trunc(1234567,-1) "dix" from dual ;

million cent-mille dix-mille mille cent dix
---------- ---------- ---------- ---------- ---------- ----------
1000000 1200000 1230000 1234000 1234500 1234560
--------------------------------------------------------------------


ON DATE
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

Curent date
-------------------
08/10/2004 14:08:48

-- date truncated to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;

'YEAR'
-------------------
01/01/2004 00:00:00

-- date truncated to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;

'IYYY'
-------------------
29/12/2003 00:00:00

-- date truncated to the 1st day of the quarter --
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;

'Q'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;

'MONTH'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the week --
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'
-------------------
04/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'W') "'W'" FROM DUAL;

'W'
-------------------
08/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'WW') "'WW'" FROM DUAL;

'WW'
-------------------
07/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IW') "'IW'" FROM DUAL;

'IW'
-------------------
04/10/2004 00:00:00

-- date truncated to the day (suppress hours) --
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;

'DD'
-------------------
08/10/2004 00:00:00

-- date truncated to the hour (suppress minutes) --
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;

'HH'
-------------------
08/10/2004 14:00:00

-- date truncated to the minute (suppress seconds) --
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;

'MI'
-------------------
08/10/2004 14:08:00

VARRAYS

• Enable storage of multiple values for a single column per row
• Number of multiple values are fixed by definition
• Limited to one column per table
• Array values are stored in the table itself (in line storage)
• Query performance likely to suffer
• Indexing a varray column is not possible
• Selection of array values is complex
• DML operations require use of constructor methods
• NULL values need to be specified explicitly

ROWID

SELECT department_id, rowid FROM hr.departments;
DEPARTMENT_ID ROWID
————- ——————
10 AAABQMAAFAAAAA6AAA
20 AAABQMAAFAAAAA6AAB
30 AAABQMAAFAAAAA6AAC
40 AAABQMAAFAAAAA6AAD

In this example:
• AAABQM is the data object number
• AAF is the relative file number
• AAAAA6 is the block number
• AAA is the row number for the department with ID = 10

• every row in a non clustered table of Oracle database is assigned a unique ROWID
• corresponds to the physical address of the row
• every table contains a pseudo column called ROWID
• fastest way to access a row
• Import, Export operations affect the values of ROWID
• the ROWID of a deleted row may be reassigned by Oracle
• cannot be modified
• can be queried
• may not be supported in non-Oracle databases
• can be used to understand a table's organization
• use of locks while using ROWID is recommended
• ORACLE8 uses extended rowid format
• Object Ids can refer to various row objects
• ROWID changes especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will chang every time the record is physically moved
• The ROWID is intended to be immutable (that is, unchangeable)
• The ROWID is a valid data type of a column and is available in both SQL and Pl/SQL. It stores the ROWID pseudo column value of a row in the database.
• It's important to note that ROWID values are not necessarily unique within a database. It is entirely possible for two rows of two different tables stored in the same cluster to have the same ROWID
• ROWID may change if the row is physically moved on disk, such as:

                    Doing an export or import of the table
                    Doing ALTER TABLE XXXX MOVE
                    Doing ALTER TABLE XXXX SHRINK SPACE
                    Doing FLASHBACK TABLE XXXX
                    When splitting a partition
                    When updating a value so that it moves to a new partition
                    When combining two partitions

View

A view is the result of a SQL query stored in the Oracle data dictionary. One can think of a view as a virtual table or presentation of data from one or more tables. Views are useful for security and information hiding, but can cause problems if nested too deep. View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS. Can perform DML operations on simple view(Does not contains GROUP BY clause, CONNECT BY clause, JOINS, sub query or snapshot of operations). Cannot perform DML operations on complex view(Contains atleast anyone of the above).

Advantages

• Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of table.
• Hide data complexity.
• Simplify commands for the user.
• Present the data in different prespective from the base table.
• Stores complex queries

Pseudo column

A Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but they can not be updated. They provide extra information about a row set.

Eg:

ROW ID

ROW NUM

CURRVAL

NEXTVAL... etc.,

Joins

A SQL join clause combines records from two or more tables in a relational database, resulting in a new, temporary table, sometimes called joined table. It relates the tables by means of values common between them.

Types of Join

    
- Inner join

        - Equi-join

            - Natural join

        - Cross join

- Outer join

    - Left Outer join

    - Right Outer join

    - Full Outer join

Inner join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.

Eg:

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID

Equi join

is a specific type of comparator-based join, that uses only equality comparions in the join condition

Natural join

The join condition arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

Eg:

SELECT *
FROM employee NATURAL JOIN department

Cross or Cartesian join

A cross join return the cartesian product of the sets of records from the two joined tables. It equates to an inner join where the join-condition always evaluates to true or join condition is absent in statement.

Eg:

SELECT *
FROM employee CROSS JOIN department

SELECT *
FROM employee, department;

Outer joins

An outer join does not require each record in the two tables to have a matching record. The joined table retains each record - even if no other matching record exists. Outer joins subdivided further into left outer joins, right outer joins, depending on which table(s) one retains the rows from (left, right or both)

Left Outer join

A leftouter join returns all the values from the left table and matched values from the right table. (NULL in case of no matching)

Eg:

SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department

where employee.DepartmentID (+) = department.DepartmentID

Right outer join

A right outer join returns all the values from the right table and matched values from the left table. (NULL in case of no matching)

Eg:

SELECT *

FROM employee LEFT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department
where employee.DepartmentID (+) = department.DepartmentID

Full outer join

A full outer join combines the result of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Eg:

SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM department
LEFT JOIN employee
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *

FROM department RIGHT JOIN employee

ON employee.DepartmentID = department.DepartmentID

UNION

SELECT *

FROM employee RIGHT JOIN department

ON employee.DepartmentID = department.DepartmentID

WHERE employee.DepartmentID IS NULL

CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
eg:
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y where field1=X.field1);

SQLCODE and SQLERRM

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

%ROWTYPE and %TYPE

%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

Mutating table error

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Anonymous block

An anonymous block is an unnamed sequence of actions. Since they are unnamed, anonymous blocks cannot be referenced by other program units.
In contrast to anonymous blocks, stored/ named code blocks include Packages, Procedures, and Functions.

---

begin
DBMS_OUTPUT.put_line('Hello world!');
end;

---
declare
i number;
begin;
i := 10;
end;
---

TNSNAMES.ORA

TNS (Transparent Network Substrate) . TNSNAMES.ORA is a configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = )(Port = ))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)

The TNSNAMES.ORA files is located on both client and server systems.

IN vs Exists

Both are used as a filter for searching. IN clause wants the inner query to drive the outer query. EXISTS clause wants the outer query to be run first, using each value to fetch a value from the inner query. in clause is better if the results of the sub query are very small.exists forces to specify join condition, which invokes INDEX SCAN. so its faster than in clause.

- IN = inside to outside

- EXISTS = outside to inside

---

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

When a procedure or package gets invalidated?

- when the objects dropped, which used in the procedure or package.

- when the owner alters the name of the procedure or package.

ACID

Refers to the basic properties of a database transaction.

Atomicity

- the entire sequence of actions must be either completed or aborted. the transaction cannot be partially successful.

Consistency

- the transaction takes the resources from one consistent state to another.

Isolation

- a transaction's effect is not visible to other transactions until the transaction is committed.

Durability

- changes made by the committed transaction are permanent and must survive system failure.

Automomus transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. when an autonomous transaction is called, the origination transaction gets suspended. control is returned when the autonomous transaction does a commit or rollback. A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the transaction parameter to allow for the extra concurrent transactions.

Cartesian join

A Cartesian join is a join of every row of one table to every row of another table. this normally happens when no matching join columns are specified. a query must have at least n-1 joins to avoid a Cartesian product.

Coalesce

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

Syntax:

COALESCE (expression_1, expression_2, ...,expression_n).

Nvl

Nvl is an oralce sql function that will return a non-null value if a null value is passed to it.

Nvl2

Nvl2 is an oracle sql function that will return different values based on whether the input value is null or not. NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

Decode

Decode is a sql function that provides similar functionality to an if then else or case statement. SELECT decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') FROM employees;default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Nullif

Nullif is an sql function that returns a null value if both parameters are equal in value. if not,the first value will be returned.

Eg:

SELECT NULLIF(1, 2) FROM dual;

RETURN 1.

SELECT NULLIF(1, 1) FROM dual;

RETURN NULL

DNS

Domain name server is a system that translates host names into numeric internet addresses.

Varchar vs Varchar2

Both are variable length but varchar stores only 2000 bytes of character of data where as varchar2 stores4000 bytes of character of data. varchar reserves memory for the future usage where in varchar2, string value's length will be stored on disk with the value itself.

Dual table

The dual table is a special one row table presented by default in all oracle database installations. it is suitable for selecting a pseudocolumn such as sysdate or user. the table has a signle varchar2(1) column called dummy, that has a value 'x'. the owner of the dual is "sys", but it can be used by every user.