kishorebabukm

• Sep 20th, 2005

 

Pl/SQL tables are the row, column structure  as of the table but cannot perform any select query on the pl/sql tables because the data isfrom fetched  only by the indexes like

--- store empno, ename from emp table thru cursor to pl/sql tables. 

declare

type  ty_plsql is record ( empno number, ename varchar2(50));

ty_plsql1 ty_plsql;

cursor c is select empno,ename from emp;

begin

 for i in c loop

   ty_plsql(i).empno := i.empno;

   ty_plsql(i).ename := i.ename;

end loop;

end;

rkanth18

 Profile Answers by rkanth18 

• Nov 26th, 2005

 

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2³¹-1 to 2³¹-1, so you have an awful lot of rows with which to work

 

rkanth18

 Profile Answers by rkanth18 

• Nov 26th, 2005

 

Keep the following restrictions in mind when you work with PL/SQL tables:

• There is no concept of transaction integrity with PL/SQL tables. You cannot commit information to a PL/SQL table or roll back changes from the table.

• You cannot SELECT from PL/SQL tables. There is no way to perform set-at-a-time processing to retrieve data from a PL/SQL table. This is a programmatic construct in a programmatic language. Instead you can use PL/SQL loops to move through the contents of a PL/SQL table, one row at a time.

• You cannot issue DML statements (INSERTs, UPDATEs, and DELETEs) against PL/SQL tables (though PL/SQL Release 2.3 does offer a DELETE operator).

iasritesh

 Profile Answers by iasritesh Questions by iasritesh

• Jan 14th, 2007

 

An associative array (or INDEX-BY table) that can be indexed by NUMBER or VARCHAR2. Elements are retrieved using number or string subscript values. Unlike with data tables, PL/SQL tables are stored in memory. PL/SQL Tables are sparse and elements are unordered.

babu.sridharan

 Profile Answers by babu.sridharan 

• Jul 14th, 2008

 

Example for PL/SQL table:
==================

In the below block "typlsql" and "ty_plsql2" are PL/SQL tables

declare

type ty_plsql is record ( empno number, ename varchar2(50));

TYPE ty_plsql2 is table of ty_plsql;

ty_plsql1 ty_plsql2;

j number;

cursor c is select empno,ename from emp;

begin

ty_plsql1:=ty_plsql2(NULL,NULL);

for i in c loop

j:=1;

ty_plsql1(j).empno := i.empno;

ty_plsql1(j).ename := i.ename;

dbms_output.put_line('Print the value : '||' No '|| ty_plsql1(j).empno ||' Name '|| ty_plsql1(j).ename);

j:=j+1;

end loop;

end;

neerajalatha

 Profile Answers by neerajalatha 

• Jul 5th, 2010

 

A PL/SQL table is a database table having one column and rows are stored not in predefined order. Associative arrays and nested tables comes under category of PL/SQL.