Forms FAQ

http://knoworacle.wordpress.com/2009/01/16/oracle-forms-development-developer-2000-part-2/

Forms Personalization

http://oracle.anilpassi.com/forms-personalizations-2.html

How to Load Oracle Workflow Definition into Database

The Workflow Definitions Loader is called WFLOAD and can be found in: $FND_TOP/bin

By default it can be used the following ways.
To upload:      WFLOAD apps/pwd 0 Y UPLOAD file.wft
To force:         WFLOAD apps/pwd 0 Y FORCE file.wft
To download: WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2...ITEMTYPEn]
To upgrade:    WFLOAD apps/pwd 0 Y UPGRADE file.wft

You can use a concurrent program to load process definitions. Run Workflow Definitions Loader concurrent program

Oralce AIM templates and AIM software

Good place for AIM docs from www.bryanthompsononline.com
You can right click and download docs from here.
Oracle AIM (Applications Implementation Methodology)

OA framewok personalization and extension and all from anilpassi

http://oracle.anilpassi.com/oa-framework-screen-extension-by-embedding-a-custom-page-2.html

How we can create a table in PL/SQL block. Insert records into it??? is it possible by some procedure or function?? please give example...

CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
AS
l_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.put_line('STARTING ');
l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
execute IMMEDIATE l_stmt;
DBMS_OUTPUT.put_line('end ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);
END;

We can create table in procedure as explained in above case. but we can't create or perform any DDL in functions.

Oracle Programming with PL/SQL Collections: Nested Tables, Varrays, and Associative Arrays

PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retreived from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.

Why Collections?

Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.
Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. For example, a method might allow you to remove all elements from a collection in a single command.
Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database. As I stated earlier, PL/SQL programs are a good place to make expensive SQL calls but that doesn't mean that we shouldn't try to keep those calls to a minimum.
Oracle provides three types of PL/SQL collections: nested tables, varrays, and associative arrays. We will review each of these collection types in turn. But first, let's take a quick look at traditional approaches to collections programming. At its conclusion, this article will offer some suggestions on when to use each of them.

Traditional Approaches

With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.

TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY BINARY_INTEGER;

The only way to access the elements of a PL/SQL Table was through its numeric index. Still, it was the first construct that gave PL/SQL developers array-like access to data.
PL/SQL tables were often combined with PL/SQL Records. By creating a PL/SQL Record, developers could define a composite type that allowed you to group items of varying type together. Combining PL/SQL Tables and Records together was often referred to as a 'PL/SQL Table of Records'.

--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD 
(title                   book.title%TYPE,
author                  book.author_last_name%TYPE,
year_published published_date.%TYPE));

--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec%TYPE
INDEX BY BINARY_INTEGER;

my_book_rec  book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec.author);
...
...

In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did. However, the Associative Array does contain some enhanced functionality, as we will see.

Oracle Collections Today

Let's fast forward to today and take a good look at Oracle's three collection types: nested tables, varrays, and associative arrays.

Varrays

The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.
The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:

DECLARE
TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
Fiction_genres genres;
BEGIN
fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');
END;

We could have declared genres to be of type VARCHAR2(30) because all values here are text. However, in keeping with good Oracle programming practices, you should always prefer to declare variables that are based on table columns with the %TYPE attribute. This allows your code to grow with the database schema. If we were to populate genres with a variable like v_genre (versus a text literal), it would be easy for the column type to change in the database without modifying our code.
All PL/SQL collections contain a number of built-in methods that prove useful when working with them. Table 1 lists these Collection methods.

Table 1. Collection Methods
Method	Action It Performs
COUNT	Returns number of elements in the Collection
EXISTS	Returns Boolean true if element at specified index exists; otherwise, false
EXTEND	Increases size of Collection by 1 or number specified, ie. EXTEND(n) **Cannot use with Associative Array
FIRST	Navigates to the first element in the Collection
LAST	Navigates to the last element
PRIOR	Navigates to the previous element
NEXT	Navigates to the next element
TRIM	Removes the last element, or the last n elements if a number is specified, ie. TRIM(n) **Cannot use with Associative Array
DELETE	Removes all elements of a Collection, or the nth element, if a parameter is specified

The following code sample demonstrates how to use a few of these methods. We are using a Varray in the example, but the methods function similarly on all collection types. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration. This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.

--Add a new genre.
IF adding_new_genre  THEN

--Is this genre id already in the collection?
IF NOT fiction_genres.EXISTS(v_genre_id)     THEN
--**Add** another element to the varray.
fiction_genres.EXTENDS(1);
fiction_genres(v_genre_id) := v_genre;
END IF;
--Display the total # of elements.
DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is :
'||fiction_genres.COUNT();

END IF;
...
...
--Remove all entries.
IF deleting_all_genres THEN 
Fiction_genres.DELETE();
END IF;

The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.

CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
/
CREATE TABLE book_library (
library_id    NUMBER,
name          VARCHAR2(30),
book_genres   genres);
/

When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:

--Insert a new collection into the column on our book_library table.
INSERT INTO book_library (library_id, name, book_genres)
VALUES (book_library_seq.NEXTVAL,'Brand New Library',
Genres('FICTION','NON-FICTION', 'HISTORY',
'BUSINESS AND FINANCE'));

The query SELECT name, book_genres from book_library returns us:

NAME                  BOOK_GENRES
--------------------  ---------------------------------------------
Brand New Library     GENRES('FICTION', 'NON-FICTION', 'HISTORY',
'BUSINESS AND FINANCE')

Note how the insertion order of elements in book_genres is retained. When a table contains a Varray type, its data is included in-line, with the rest of the table's data. When a Varray datatype is selected from a database table, all elements are retrieved. The Varray is ideal for storing fixed values that will be processed collectively. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.

Nested Table

Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table. Let's rework our earlier example using a Nested Table. First, you declare your type:

CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
/

The 'IS TABLE OF' syntax was also used when declaring a PL/SQL Table. However, this declaration omits the 'INDEX BY BINARY_INTEGER' clause required by the former type. Note that we have not specified the size of the collection. This is because Nested Tables, unlike the Varray, require no size specification. In other words, they are unbound. Here is a definition for the book_library database table, which now contains a Nested Table column:

CREATE TABLE book_library (
library_id     NUMBER,
name           VARCHAR2(30),
book_genres_tab genres_tab)
NESTED TABLE book_genres_tab STORE AS genres_table;
/

As stated earlier, a Varray's contents are stored in the same table as the other columns' data (unless the collection is exceedingly large, then Oracle stores it in a BLOB, but still within the same tablespace). With Nested Tables, a seperate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.

--Insert a record into book_library, with a Nested Table of book genres.
INSERT INTO book_library (library_id, name, book_genres_tab)
VALUES (book_library_seq.NEXTVAL,'Brand New Library', 
genres_tab('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'));
/

--Declare a nested table type
DECLARE 
updated_genres_tab genres_tab;
BEGIN
updated_genres_tab := 
genres_tab('FICTION','NON-FICTION','HISTORY','BUSINESS AND FINANCE',
'SCIENCE','PERIODICALS','MULTIMEDIA');

--Update the existing record with a new genres Nested Table.
UPDATE book_library 
SET book_genres_tab = updated_genres_tab;

END;
/

These examples show an insert and an update to the book_table and are similar to what you might see if you were working with a Varray. Both Nested Tables and Varrays allow you to use SQL to select individual elements from a collection. However, Nested Tables have an advantage over Varrays in that they allow for inserts, updates, and deletes on individual elements. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.
To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.

--1.)Select all genres from library 'Brand New Library' that are like
'%FICTION%'.
SELECT column_value FROM TABLE(SELECT book_genres_tab
FROM book_library 
WHERE name = 'Brand New Library')
WHERE column_value LIKE '%FICTION%';
/

COLUMN_VALUE
------------------------------
FICTION
NON-FICTION

--2.)Update entry 'MULTIMEDIA' to a new value.  Only possible with
a nested table!!
UPDATE TABLE(SELECT book_genres_tab 
FROM book_library 
WHERE name = 'Brand New Library')
SET column_value   = 'MUSIC AND FILM'
WHERE column_value = 'MULTIMEDIA';


--3.)Select all book_genre_tab entries for this library.
SELECT column_value FROM TABLE(SELECT book_genres_tab
FROM book_library 
WHERE name = 'Brand New Library');

COLUMN_VALUE
------------------------------
FICTION
NON-FICTION
HISTORY
BUSINESS AND FINANCE
SCIENCE
PERIODICALS
MULTIMEDIA

The first of the preceding three statements simply selects an individual element from the Nested Table, book_genres_tab, in our book_library database table. The second statement performs an update on an individual element, something possible only with Nested Tables. The last query shown selects all Nested Table elements from the parent table. This demonstrates an important feature of the TABLE operator. An ealier query we performed on a database column of type Varray returned a single comma-delimited list of values ('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'). Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.

Associative Array

Earlier, we reviewed the definition of a PL/SQL Table (also know as an index-by table). The statement

TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY BINARY_INTEGER;
book_titles   book_title_tab;

defines a collection of book titles, accessible by a numeric index. Although it is feasible to locate an element by its numeric index, the limitation to this approach is that the value we have to search by is often not an integer.

SELECT title FROM book;

TITLE
------------------------------
A Farewell to Arms
For Whom the Bell Tolls
The Sun Also Rises

Above are values from the title column of the book table. If we needed to remove an entry, given only the book title, we would have to search the entire collection in a somewhat inefficient manner. The following is code illustrates this:

FOR cursor_column IN  book_titles.FIRST..book_titles.LAST LOOP
IF book_titles(cursor_column) = 'A Farewell to Arms' THEN
book_titles.DELETE(cursor_column);
END IF;
END LOOP;

With Associative Arrays, it is now possible to index by the title of the book. In fact, there are numerous different indexing options, including by VARCHAR2, using the %TYPE keyword, and more. This is a improvement over indexing everything by an integer then having to shuffle through entries to find what you're looking for. Now, if we want to remove the book A Farewell to Arms, we can use an Associative Array:

DECLARE
TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY book.title%TYPE;
book_titles book_title_tab;
BEGIN
book_titles.DELETE('A Farewell to Arms');
END;

By using an Associative Array of book.title%TYPE, we accomplish our mission in one line, without the need to loop through the set. The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database. They are strictly for internal use in PL/SQL applications. If this is all you require of a collection, the Associative Array's indexing flexibility make it a good choice.

When to Use What

If you're new to PL/SQL collections, you may have a fair understanding of their mechanics by this point, but are uncertain when to use a particular type. Table 2 summarizes each collection's capabilities.

Figure 2. Collection Capabilities
Has Ability To	Varray	Nested Table	Associative Array
be indexed by non-integer	No	No	Yes
preserve element order	Yes	No	No
be stored in database	Yes	Yes	No
have elements selected indidually in database	Yes	Yes	--
have elements updated indidually in database	Yes	No	--

In addition, the following bullet points can be referred to when deciding what collection best suits a particular solution.
Varray

• Use to preserve ordered list
• Use when working with a fixed set, with a known number of entries
• Use when you need to store in the database and operate on the Collection as a whole

Nested Table

• Use when working with an unbounded list that needs to increase dynamically
• Use when you need to store in the database and operate on elements individually

Associative Array

• Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.

Conclusion

Oracle PL/SQL is not a difficult language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Given PL/SQL's power to interact with the database, it can be tempting to simply to fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.
If you are a member of the Oracle Technology Network, you can read the following chapter in the PL/SQL User's Guide and Reference for additional information about PL/SQL Collections.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.
More Info: http://www.developer.com/db/article.php/10920_3379271_1/Oracle-Programming-with-PLSQL-Collections.htm