Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL, part 3

Using TABLE Without Interacting With Database - Oracle

Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.

  1. Database Interaction with PL/SQL, part 3
  2. Using TABLE Without Interacting With Database
  3. Combining TABLE and RECORD
  5. Using DML Commands on NESTED TABLES Within PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
June 07, 2005

print this article



First of all, consider my apologies for inserting this topic inside an article titled as "database interactions using PL/SQL". I thought that this would be necessary, as some of our logic depends on some PL/SQL tables which don't always have to be filled with database information. And of course, using this topic, I can also introduce few new collection methods on working with PL/SQL tables.

Let us consider the following example. Even though it is bit lengthy, I can introduce all issues at once.

type t_Numtbl is table of number;
v_numtbl t_numtbl := t_Numtbl(10,20,30,40,50,60,70);
    dbms_output.put_line('Displaying complete List:');
    for i in v_numtbl.first .. v_numtbl.last
dbms_output.put(v_numtbl(i) || ',');
    end loop;
    dbms_output.new_line; --just skipping to next line
dbms_output.put_line('Displaying complete List after deletion:');
for i in v_numtbl.first .. v_numtbl.last
if v_numtbl.exists(i) then
dbms_output.put(v_numtbl(i) || ',');
end if;
end loop;
dbms_output.put_line('No. of elements ' || v_numtbl.count);

Let us consider the above program line by line. I don't think I need to cover FOR loops in the above program, as they have been already covered in my previous article.

type t_Numtbl is table of number;

The above line defines 't_Numtbl' as a new data type which can hold only a set of values of type 'number'.

v_numtbl t_numtbl := t_Numtbl(10,20,30,40,50,60,70);

The above line declares and initializes the variable 'v_numtbl' with some sample values. Make a note that they are not from database! Within the body of the above program, I used FOR loops twice to display the contents of the variable 'v_numtbl'.


After displaying the values using the first FOR loop, I am deleting the third element (index starts from 1) from the table using the above statement.

if v_numtbl.exists(i) then
dbms_output.put(v_numtbl(i) || ',');
end if;

The above condition checks to see if the specified index exists within the table or not before trying to display it. If you don't include this, we would get an error.

dbms_output.put_line('No. of elements ' || v_numtbl.count);

You can use 'v_numtbl.count' to get the number of elements present within the PL/SQL table.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: