Coutesy: www.IBTIMES.com
Thursday, November 17, 2011
Triggers for Master Detail Relation in Forms
NON-ISOLATED (DEFAULT)
--------------------------------
On-Check delete Master
On Populate Details
On Clear Details
ISOLATED
--------------------------------
On Clear Details
On Populate Details
CASCADE
--------------------------------
Pre-Delete
On Clear details
On Populate Details
Saturday, October 15, 2011
NOCOPY Hint
The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.
When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.
With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.
Monday, October 10, 2011
Forms Triggers - Firing Order
Runform
1. Pre-Logon Form
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-Item-Instance Item
***********
Enter Query
1. Post-Text-Item Item
2. Post-Record Block
3. When-Clear-Block Block
4. When-New-Record-Instance Block
5. When-New-Item-Instance Item
Note: If you define the Key-ENTQRY trigger, this is the only trigger
which fires in an Enter Query situation.
*****************
Execute Query
After Enter Query 1. Pre-Query Block
2. Pre-Select Block
3. On-Select Block
4. When-Clear-Block Block
5. Post-Select Block
6. On-Fetch Block
7. On-Close Form
8. When-Clear-Block Block
Note: If you define the Key-EXEQRY trigger, this is the only trigger
which fires in an Execute Query situation.
********************************
Execute Query
Without Enter Query
1. Post-Text-Item Block
2. Pre-Query Block
3. Pre-Select Block
4. On-Select Block
5. Post-Select Block
6. On-Fetch Block
7. On-Close Form
8. When_Create_Record Block
9. Pre-Record Block
10. Pre-Text-Item Item
11. When-New-Record-Instance Block
12. When-New-Item-Instance Item
********************************
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
********************************
Next Field and
Previous field 1. When-New-Item-Instance Item
********************************
Next Record and
Previous Record 1. When-New-Record-Instance Block
2. When-New-Item-Instance Item
********************************
Next Block and
Previous Block
1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. When-Create-Record Block
5. Pre-Block Block
6. Pre-Record Block
7. Pre-Text-Item Block
8. When-New-Block-Instance Block
9. When-New-Record-Instance Block
10. When-New-Item-Instance Form
********************************
Records Are Queried
1. Post-Query Block
2. Post-Change Block
3. Post-Change Item
4. Post-Change Block
5. Post-Change Block
Go back to Post-Query
NOTE: This cycle is repeated for each record retrieved.
********************************
No Records Are Queried 1. When-New-Record-Instance Block
2. When-New-Item-Instance Item
NOTE: To observe this Trigger Firing Order:
a. Enter a query.
b. Enter a nonexistent record.
c. Execute the query.
The two triggers listed above, the Enter Query triggers, and
the Execute Query triggers fire.
********************************
Create Record
1. Post-Change Block
2. When-Validate-Item Block
3. Post-Text-Item Block
4. When-Validate-Record Block
5. Post-Record Block
6. Post-Block Block
7. On-Savepoint Form
8. Pre-Commit Form
9. Pre-Insert Block
10. On-Insert Form
11. Post-Insert Block
12. Post-Forms-Commit Form
13. On-Commit Form
14. Post-Database-Commit Form
15. Pre-Block Block
16. Pre-Record Block
17. Pre-Text-Item Block
18. When-New-Item-Instance Form
********************************
Update Record 1. When-Database-Record Block
2. Post-Change Block
3. When-Validate-Item Block
4. Post-Text-Item Block
5. When-Validate-Record Block
6. Post-Record Block
7. Post-Block Block
8. On-Savepoint Form
9. Pre-Commit Form
10. Pre-Update Block
11. On-Update Block
12. Post-Update Block
13. Post-Forms-Commit Form
14. On-Commit Form
Here the transaction is complete and one record added.
15. Post-Database-Commit Form
16. Pre-Text-Item Block
17. When-New-Item-Instance Form
NOTE: To observe this Trigger Firing Order:
a. Execute a query.
b. Change a value.
c. Choose Action->Save from the menu.
d. Record the triggers from that point.
********************************
Delete Record
1. On-Lock Block
2. When-Remove-Record Block
3. Post-Change Block
4. Post-Change Block
5. Post-Change Block
6. Post-Change Block
7. Post-Change Block
8. Post-Change Block
9. Post-Change Item
10. Post-Query Block
11. Post-Text-Item Block
12. Post-Record Block
13. Pre-Record Block
14. Pre-Text-Item Block
15. When-New-Record-Instance Block
16. When-New-Item-Instance Form
NOTE: To observe this Trigger Firing Order, delete a detail record.
********************************
Collection Methods - Actions
EXISTS(n)
: Returns TRUE if nth element of the
table exists.
COUNT
: The number of elements (rows) in the plsql table
FIRST : First and Last index no.s in the table
LAST
Returns NULL if table is empty
PRIOR(n)
: Returns index no that preceeds n in the plsql table
NEXT(n) : Returns index no that succeeds n in the plsql table
EXTEND(n,i) : Append n copies of the 'i'th element to a plsql table
i defaults to NULL n defaults to 1
TRIM(n) : Remove n elements from the end of a plsql table
n defaults to 1
DELETE(m,n) : Delete
elements in range m...n
m defaults to = n
n defaults to ALL elements
COMMIT_FORM & FORMS_DDL('COMMIT') - Differences
COMMIT_FORM
-----------
All DML operations/transactions within the form awaiting COMMIT are committed to the database.
All Database blocks gets COMMITed .
FORMS_DDL('COMMIT')
-------------------
Only DML operations/transactions within the form awaiting COMMIT are committed to the database.
COMMIT_FORM applies all changes, while FORMS_DDL('COMMIT') just commits current transaction.
COMMIT_FORM calls both POST_FORM and COMMIT
-----------
All DML operations/transactions within the form awaiting COMMIT are committed to the database.
All Database blocks gets COMMITed .
FORMS_DDL('COMMIT')
-------------------
Only DML operations/transactions within the form awaiting COMMIT are committed to the database.
COMMIT_FORM applies all changes, while FORMS_DDL('COMMIT') just commits current transaction.
COMMIT_FORM calls both POST_FORM and COMMIT
Subscribe to:
Posts (Atom)