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