Oracle Apps Techno Functional................

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods. Index-By Tables (Associative Arrays) Nested Table Varrays Collection Methods Multiset Operations Multidimensional Collections Related articles. Associative Arrays in Oracle 9i Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle Index-By Tables (Associative Arrays) The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using  BINARY_INTEGER  values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist. SET SERVEROUTPUT ON SIZE 1000000 DECLARE TYPE table_type IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; v_tab table_type; ...

The  CREATE TRIGGER  statement has a lot of permutations, but the vast majority of the questions I'm asked relate to basic DML triggers. Of those, the majority are related to people misunderstanding the order of the timing points and how they are affected by bulk-bind operations and exceptions. This article represents the bare minimum you should understand about triggers before you consider writing one. DML Triggers The Basics Timing Points Bulk Binds How Exceptions Affect Timing Points Mutating Table Exceptions Compound Triggers Should you use triggers at all? (Facts, Thoughts and Opinions) Non-DML (Event) Triggers Enabling/Disabling Triggers Related articles. Mutating Table Exceptions Trigger Enhancements in Oracle Database 11g Release 1 Cross-Edition Triggers: Edition-Based Redefinition in Oracle Database 11g Release 2 DML Triggers The Basics For a full syntax description of the  CREATE TRIGGER  statement, check out the documentatio...

Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions. The following types of PL/SQL blocks can be defined as autonomous transactions: Stored procedures and functions. Local procedures and functions defined in a PL/SQL declaration block. Packaged procedures and functions. Type methods. Top-level anonymous blocks. The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited. CREATE TABLE at_test ( id NUMBER NOT NULL, description VARCHAR2(50) NOT NULL ); INSERT INTO at_test (id, description) VALUES (1, 'Description for 1'); INSERT INTO at_test (id, description) VALUES ...

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes  data file , as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called  log file ,  bad file  or  reject file, discard file . Log file  tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.  Bad file or reject file  gives you the records that were rejected because of formatting errors or because they caused Oracle errors.  Discard file  specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records. Structure of the data file: The data file can be in fixed record format or variable re...