Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 26 March 2017

Difference among Index by table, Nested table and Varray

0 comments


Index by table
Nested table
Varray
1. This is unconstraint table             
This is an unconstraint table
This is an constraint table
2. Not to store permanently In database table
To store permanently into database using SQL
To store permanently into database using SQL
3. We cannot add or remove Indexes                  
we can add or remove indexes using extend, trim collection Method
we can add or remove indexes using extend trim collection method
4.Exists, first, last, prior,Count, next, delete,Delete (element1, Element n)
Extend, trim, exists, first, last,prior, next, delete, count,delete (element1, element n)
Limit, extend, trim, exists,first, last, prior, next, count,delete
5. Indexes range from -ve to +ve
Indexes start with 1
Indexes starts with 1
Continue reading >>

Wednesday, 22 March 2017

FRM-92101 There was a failure in the Forms Server during startup. this could happen due to invalid configuration

0 comments


When you are working with Oracle application if you are suddenly unable to open oracle from by following error.

FRM-92101
There was a failure in the Forms Server during startup. this could happen due to invalid configuration. Please look into the web-server for the details.

FRM-92101 There was a failure in the Forms Server during startup

Solution:

Clear you browser cache history then you can close browser all opened windows and freshly open browser, you can open your oracle application now it works fine.

How to Clear cache history Internet explorer:

 Go to settings ->Internet Options

How to Clear cache history Internet explorer




Continue reading >>

Tuesday, 14 March 2017

PL/SQL Varray

0 comments

     Varray is an user defined data type, which is used to store same datatype in a single unit, it is also same as nested table i.e., before we are storing values. We must initialize the values through constructor, and also using varrays we are storing up to 2 GB data. Varrays also supports all collection methods as same a nested table and also supports limit collection method. This collection method returns size of the array. This is a user defined type, so we are creating in two step process.
    
         Syntax:-  type typename is varray(maxsize) of data type(size);
                           Variablename typename:=typename();

DECLARE
   TYPE t1 IS VARRAY (10) OF VARCHAR2 (10);

   V_t   t1
            := t1 ('a',
                   'b',
                   'c',
                   'd');
   Z     BOOLEAN;
BEGIN
   DBMS_OUTPUT.put_line (v_t.LIMIT);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   DBMS_OUTPUT.put_line (v_t.FIRST);
   DBMS_OUTPUT.put_line (v_t.LAST);
   DBMS_OUTPUT.put_line (v_t.PRIOR (3));
   DBMS_OUTPUT.put_line (v_t.NEXT (3));
   DBMS_OUTPUT.put_line (v_t.LAST);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   z := v_t.EXISTS (3);

   IF z = TRUE
   THEN
      DBMS_OUTPUT.
       put_line ('inde z exists with an element' || ' ' || v_t (3));
   ELSE
      DBMS_OUTPUT.put_line ('index z does not exists');
   END IF;

   v_t.EXTEND;
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t (5) := 'e';
   v_t.EXTEND (2);
   v_t (6) := 'f';
   v_t (7) := 'g';

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.EXTEND (3, 4);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.TRIM (4);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t.delete;
   DBMS_OUTPUT.put_line (v_t.COUNT);
END;


Note:- in varrays we are not allowed to delete range of elements, but we can delete all the elements using delete collection method


You May Like:

PLSQL Record


Continue reading >>

Tuesday, 7 March 2017

PL/SQL Nested tables

0 comments
       
         Nested tables are user defined data types, which are used to store multiple data items into single unit. Basically nested table is an unconstrained table. Generally, we are not allowed to store Index by table permanently into Database. To overcome them problem oracle 8.0 introduced nested tables, arrays to store permanently in Database using SQL. Nested table is an extension of index by table. Generally in index table, we are not allowed to add or remove indexes, but in nested table we are adding or removing indexes using extend from collection methods.

   By default in nested table indexes start with 1 and also these indexes are conceptions. This is a user defined type.so, we are creating two-step process. Before we are storing data into nested table, array we must initialize through constructor. Here constructor name is same as type name.


Syntax:-  Type typename  is table of data type (size);
               Variable name typename:=typename();

Example:-

DECLARE
   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1 := t1 ();
BEGIN
   V_t.EXTEND (300);
   V_t (300) := 60;
   DBMS_OUTPUT.put_line (v_t (300));
END;
          
          
          
Note: - we can also store actual through the constructor itself, in this case oracle server start indexes 1 onwards.

Example:-

DECLARE
   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1
            := t1 (10,
                   20,
                   30,
                   40);
BEGIN
   DBMS_OUTPUT.put_line (v_t.FIRST);
   DBMS_OUTPUT.put_line (v_t.LAST);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   DBMS_OUTPUT.put_line (v_t.COUNT);
END;

Using bulk collect:-


Example:-

DECLARE
   TYPE t1 IS TABLE OF VARCHAR2 (20);

   V_t   t1 := t1 ();
BEGIN
   SELECT ename
     BULK COLLECT INTO v_t
     FROM emp;

   FOR I IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;
END;
        

Continue reading >>