Tuesday 28 February 2017

How to use VArray in PLSQL

--Example for VArray-------------
==========================================
DECLARE
  TYPE abc IS VARRAY(20) OF NUMBER;
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 1234;
  v1.extend();
  v1(2) := 5678;
  v1.extend();
  v1(3) := 7890;
  dbms_output.put_line('FIRST VALUE ' || v1(1));
  dbms_output.put_line('SECOND VALUE ' || v1(2));
  dbms_output.put_line('THIRD VALUE ' || v1(3));
END;
--------
Output
--------
FIRST VALUE 1234
SECOND VALUE 5678
THIRD VALUE 7890



===========================================
DECLARE
  TYPE abc IS VARRAY(20) OF VARCHAR2(100);
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 'DEMO FOR';
  v1.extend();
  v1(2) := 'VARRAY';
  v1.extend();
  v1(3) := 'EXAMPLE';
  dbms_output.put_line('FIRST VALUE: ' || v1(1));
  dbms_output.put_line('SECOND VALUE: ' || v1(2));
  dbms_output.put_line('THIRD VALUE: ' || v1(3));
END;
------
Output
------
FIRST VALUE: DEMO FOR
SECOND VALUE: VARRAY
THIRD VALUE: EXAMPLE



===========================================
-- USING LOOP--
DECLARE
  CURSOR c1 is
    select vendor_name from ap_suppliers where rownum <= 20;
  --
  TYPE abc IS VARRAY(20) of VARCHAR2(100);
  v1 abc;
  n  number := 1;
  --
BEGIN
  v1 := abc();
  FOR i in c1 loop
    v1.extend();
    v1(n) := i.vendor_name;
    dbms_output.put_line('Vendor Name ' || v1(n));
    n := n + 1;
  END LOOP;
END;
------
Output
------
Vendor Name: Paresh Consulting
Vendor Name: Price Service Co.
Vendor Name: Umesh Damle
Vendor Name: Vijay Kadam
Vendor Name: Kerala Spa
Vendor Name: The Great Punjab Hotel
Vendor Name: Metal Trading Company
Vendor Name: Satish Cartes
Vendor Name: George Ltd
Vendor Name: Hetal Group Ltd
Vendor Name: Human Resource Consultancy
Vendor Name: MRF Ltd
Vendor Name: Vertex Interactive
Vendor Name: Steve Follie
Vendor Name: Communications Services Inc
Vendor Name: J D Service Co.
Vendor Name: J M Consulting
Vendor Name: Vishwas Patil
Vendor Name: Shirish kadam
Vendor Name: Jivan anand


============================================
DECLARE
  TYPE abc is VARRAY(20) OF NUMBER(10);
  v1 abc;
  n  NUMBER := 1;
BEGIN
  v1 := abc();
  for i in 1 .. 10 loop
    v1.extend();
    v1(i) := i;
    dbms_output.put_line(v1(i));
    dbms_output.put_line('count: ' || v1.count);
    dbms_output.put_line('First: ' || v1.first);
    dbms_output.put_line('Last: ' || v1.last);
  END LOOP;
END;
------
Output
------
1
count: 1
First: 1
Last: 1
2
count: 2
First: 1
Last: 2
3
count: 3
First: 1
Last: 3
4
count: 4
First: 1
Last: 4
5
count: 5
First: 1
Last: 5
6
count: 6
First: 1
Last: 6
7
count: 7
First: 1
Last: 7
8
count: 8
First: 1
Last: 8
9
count: 9
First: 1
Last: 9
10
count: 10
First: 1
Last: 10


No comments:

Post a Comment

How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>

 How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>  Sometime these xml data tags automatically chang...