Click here to Skip to main content
13,046,533 members (69,705 online)
Rate this:
Please Sign up or sign in to vote.
See more:
How to use array in oracle database...

for example...

we r giving many values for one attribute and save while runtime..

Front-end: ASP.NET

Warm Regards,

Jaison Joe...
Posted 22-Jan-13 22:03pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

An array (also called a collection) is a set of ordered data items that can be thought of as a numbered list, with each item being of the same type. Each item in an array is referred to as an element, and each element has an index which is a number corresponding to the element's position in the array. The index-to-element relationship can be thought of as a key/value pair.
Oracle provides three types of PL/SQL collections: associative arrays, nested tables, and varrays.
Associative arrays (also referred to as index-by tables) let you access elements using numbers or strings for the index values. Associative arrays are useful for small to medium sized lookup tables where the array can be constructed in memory each time a procedure is called. There is no fixed limit on their size and their index values are more flexible- associative array keys can be negative and/or nonsequential, and associative arrays can use string values instead of numbers if appropriate.
Nested tables hold an arbitrary number of elements and use sequential numbers as the index or key to the elements. Nested tables can be stored in database tables and manipulated through SQL. They are appropriate for data relationships that must be stored persistently. Nested tables are flexible in that arbitrary elements can be deleted, rather than just removing an element from the end. Note that the order and subscripts (keys) of nested tables are not preserved as the table is stored and retrieved in the database.
Varrays hold a fixed number of elements, although the number of elements can be changed at runtime. Like nested tables, varrays use sequential numbers as the index or key to the elements. You can define equivalent SQL types, allowing varrays to be stored in database tables. Varrays are a good choice when the number of elements is known in advance, and when the elements are likely to be accessed in sequence.
This is an example associative array named "People" containing 5 elements:
Index    Element
1       john
2       mary
3       frank
4       jane
5       william

In this array "People", the element "frank" would be accessed by referring to it as 

All Oracle PL/SQL collections have a variety of built-in methods or functions that can be used to navigate or manipulate them, as detailed in the table below.
Method 	Action Performed
COUNT	Returns number of elements in the array
EXISTS	Returns Boolean true if the element at the specified index exists; otherwise returns false
EXTEND	Increases size of array by 1 or by the number specified, ie. EXTEND(n)
Cannot be used with associative arrays
FIRST	Navigates to the first element in the array
NEXT	Navigates to the next element in the array
LAST	Navigates to the last element in the array
PRIOR	Navigates to the previous element in the array
TRIM	Removes the last element of the array, or the last n elements if a number is specified, ie. TRIM(n) 
Cannot be used with associative arrays
DELETE	Removes all elements of an array, or the nth element, if a parameter is specified

A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size (but not a minimum size) when you declare the VARRAY type. 
In general, the VARRAY type should be used when the number of items to be stored is small; it is not suitable for large numbers of items or elements. Note that you cannot index or constrain VARRAY values.

// create command and set properties
OracleCommand cmd = 
// the sql text used to insert the 
// rows in the arrays and this 
// necessarily uses bind variables
cmd.CommandText =  "insert into 
  jobs (job_id, " +
        "job_title, " +
        "min_salary, " +
        "max_salary) " +
        "values (:1, :2, :3, :4)";
// set the number of elements 
// in the arrays and all three 
// arrays are the same size
cmd.ArrayBindCount = job_id_vals.Length;
// add parameters to collection
// perform the array insert in 
// a single call
Jaison Joe 23-Jan-13 4:33am
How to create a table in oracle and ASP.NET as a front-end.

For example as we take the qualification of one person.

| Course | Duration | Year_of_pass | Percentage |

for this how can i create the table. Each empty value is an textbox. If i create
one table with these attributes how can i give multiple value for one attribute
while saving.

Help me for coding...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100