Difference Between Primitive data type and Reference data type in Java

Data type in java

 Can be classified as :

1) Primitive
2) Reference Type (Non-Primitive)

Primitive: There are only 8 primitive types of data type
1) byte
2) short
3) int
4) long
5) float
6) double
7) char
8) boolean

Reference: Strings, Array & interfaces.

Difference Between them:
Regarding storing the data.
Example:
------------
int age=35;
Here the variable age store the actual value 35.

String greet="Hello";
Here the reference type does not store the actual data. It stores a reference to the data.
the compiler will not get the value of the data, it tells the compiler where to find the actual data.

The string Hello is created and stored in the computer's memory. The variable greet stores the address of the memory location.

Brief Introduction of Oracle Collections and Bulk Collect.

Collection and Bulk  Collect

A collection is same as an array in other programing language like,c,c++,java ect. Its an ordered group of elements of a particular types. It can hold simple as well as complex data types. DOWNLOAD

Name	No of Elements	Subscript type	Dense/Sparse	Where to create
Associative array or index by table or plsql table	Unbounded/No limit	String or integer	Either	Only in plsql
Nested table	Unbounded/No limit	Integer only	Starts Dense can become sparse	Plsql and schema level (sql)
Variable Array or Varray	Bounded/ limit	Integer only	Always Dense	Plsql and schema level (sql)

Index by table:-

1)       TYPE <TYPENAME> IS TABLE OF <DATA TYPE> INDEX BY VARCHAR2(20);

2)      Appropriate to use for relatively smaller collective values in which the collection can be initialized and used within the same subprograms.

3)      No need to initialize

4)      Bulk collect cannot be used.

5)      Can have negative subscript

 Nested Table:-

1)      TYPE <TYPENAME> IS TABLE OF <DATA TYPE>;

Since the upper size limit is not fixed, memory needs to be extended each time before we use .

2)      Use EXTEND Method

3)      4) Need to be initialized.

Varray:-

1)      TYPE <TYPENAME> IS VARRAY(<SIZE>) OF  <DATA TYPE>;

2)      Appropriates to use when the array size is known and to perform similar activities on all the array elements.

3)      Need to be initialized.

Bulk collect

Reduce loop overhead for DML statement and queries

Bulk Collect:- Select Statements that retrieve multiple rows with a single fetch, improves the data retrieval speed. (Avoid Context Switch).

FORALL:- Insert, Update, Delete collection to change multiple rows of data very quickly.

Limit:- %BULK_ROWCOUNT(n):- It Returns the number of rows affected in the nth DML statement of the FORALL statement.

%BULK_EXCEPTIONS(i).ERROR_INDEX

%BULK_EXCEPTIONS(i).ERROR_CODE

%BULK_EXCEPTIONS(i).COUNT

Returning Clause:- It specified the values return from delete,execute immediate,insert,update statements. We can retrieve the column values into individual variable or into collections

Example:- Returning id,description BULK COLLECT into l_tab;

Index by Table Example:-

DECLARE
TYPE ABC IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE  PQR IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
TYPE KLM IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
V1 ABC;
V2 PQR;
V3 KLM;
BEGIN
V1(1):=1;
V1(10):=23;
V2('A'):=10;
V2('M'):=12;
V3(18):='NEW YORK';
V3(-83):='LONDON';
END;
/

--Methods in Index by Table


DECLARE
TYPE ABC IS TABLE OF DATE INDEX BY PLS_INTEGER;
V ABC;
BEGIN
      FOR I IN 1..10 LOOP
V(I):=SYSDATE+I;
   DBMS_OUTPUT.PUT_LINE('DATE VALUE FOR V('||I||')='||V(I));
END LOOP;
IF(V.EXISTS(11)) THEN
DBMS_OUTPUT.PUT_LINE('ELEMENT EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('ELEMENT DOES NOT EXISTS');
END IF;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS '||V.COUNT);
V.DELETE(7);
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS AFTER DELETE '||V.COUNT);
V.DELETE(8,10);
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS AFTER DELETE '||V.COUNT);
--SHOWING FIRST AND LAST METHOD
FOR I IN V.FIRST..V.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NEXT INDEX FOR 4 IS '||V.NEXT(4));
DBMS_OUTPUT.PUT_LINE('THE PRIOR INDEX FOR  4 IS '||V.PRIOR(4));
END;
/

--Varchar2 as Index

DECLARE
TYPE ABC IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(1);
V ABC;
X VARCHAR2(1);
BEGIN
V('A'):='APPLE';
V('B'):='BOY';
V('C'):='CAT';
V('D'):='DOG';
X:='A';
LOOP
DBMS_OUTPUT.PUT_LINE(V(X));
X:=V.NEXT(X);
EXIT WHEN X IS NULL;
END LOOP;
END;
/

--Bulk Collect Example

DECLARE
TYPE ABC IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;
V ABC;
X NUMBER;
BEGIN
SELECT * BULK COLLECT INTO V FROM EMP;
FOR I IN 1..V.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V(I).ENAME);
END LOOP;
END;
/
Nested Table Example:-

--Table of Numbers

DECLARE
TYPE ABC IS TABLE OF NUMBER;
V ABC;
CURSOR C1 IS SELECT  EMPNO FROM EMP;
V_EMP ABC;
COUNTER NUMBER:=1;
BEGIN
V_EMP:=ABC();
FOR REC IN C1 LOOP
V_EMP.EXTEND();
V_EMP(COUNTER):=REC.EMPNO;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER VALUE AT '||'V_EMP('||COUNTER||')='||V_EMP(COUNTER));
COUNTER:=COUNTER+1;
END LOOP;
END;
/
--Table of Varchar2 Data type

DECLARE
TYPE ABC IS TABLE OF NUMBER;
TYPE PQR IS TABLE OF VARCHAR2(20);
V1 ABC;
V2 PQR;
BEGIN
V1:=ABC(10,20,30,40);
V2:=PQR('A','B','C','D','E');
FOR I IN 1..V1.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('THE VALUE AT INDEX '||I||' IS '||V1(I));
END LOOP;
FOR I IN V2.FIRST..V2.LAST LOOP
DBMS_OUTPUT.PUT_LINE('THE VALUE AT INDEX '||I||' IS '||V2(I));
END LOOP;
END;
/

Example of Varray:-

declare
type abc is varray(10) of number;
v abc:=abc(1,2,3,4,5,6,7,8,9,10);
begin
for i in 1..10
loop
dbms_output.put_line(v(i));
end loop;
end;
/

--Example with cursor

declare
type abc is varray(10) of number;
v1 abc;
cursor c1 is select empno from emp where rownum<11;
v2 abc;
counter number:=1;
begin
v1:=abc();
for i in 1..10
loop
v1.extend();
v1(i):=i*10;
dbms_output.put_line('Value of v1'||'('||i||')'||' is '||v1(i));
end loop;
v2:=abc();
for rec in c1
     loop
 v2.extend();
 v2(counter):=rec.empno;
dbms_output.put_line('Value of empno at v2'||'('||counter||')'||' is '||v2(counter));
counter:=counter+1;
end loop;
end;
/

--Example with Collection Method

DECLARE
TYPE ABC IS VARRAY(10) OF NUMBER;
V ABC:=ABC(1,2,3,4,5,6);
BEGIN
--Using collection methods
DBMS_OUTPUT.PUT_LINE('V.COUNT='||V.COUNT);
DBMS_OUTPUT.PUT_LINE('V.LIMIT='||V.LIMIT);
DBMS_OUTPUT.PUT_LINE('V.FIRST='||V.FIRST);
DBMS_OUTPUT.PUT_LINE('V.LAST='||V.LAST);
--Ttrimming last two elements
V.TRIM(2);
FOR I IN V.FIRST..V.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
DBMS_OUTPUT.PUT_LINE('V.LAST='||V.LAST);
END;
/

Classes of Oracle Wait Events

Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of the wait classes.

Administrative

Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

Application

Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

Cluster

Waits related to Real Application Cluster resources (for example, global cache resources such as 'gc cr block busy'

Commit

This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

Concurrency

Waits for internal database resources (for example, latches)

Configuration

Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

Idle

Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')

Network

Waits related to network messaging (for example, 'SQL*Net more data to dblink')

Other

Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

Scheduler

Resource Manager related waits (for example, 'resmgr: become active')

System I/O

Waits for background process IO (for example, DBWR wait for 'db file parallel write')

User I/O

Waits for user IO (for example 'db file sequential read')

SQL TRACE EXAMPLE

                            EXAMPLE FOR SQL TRACE

--1) conn sys as sysdba
CON / AS SYSDBA

--2) CREATE A NEW USER OR YOU CAN USE THE EXISTING FOR THE EXAMPLE ONLY I CREATED NEW USER

DROP USER TUSER CASCADE --DROPING IF  ALREADY EXIST
/
create  user tuser identified by tuser
 /

grant connect,resource to  tuser
/

grant alter session to tuser
/

--3) CONNECT TO THE USER AND SET SQL_TRACE TO TRUE

conn tuser/tuser

alter session set sql_Trace=true
/

ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION"
/

--4) CREATE SOME TABLE FOR THE PRACTICAL

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10)    ,
    JOB VARCHAR2(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;


--5) RUN SOME QUERY

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
/

SELECT S1.ENAME,S1.DEPTNO FROM EMP S1 WHERE  SAL=(SELECT MAX(S2.SAL)
FROM EMP S2 WHERE S1.DEPTNO=S2.DEPTNO)
/

--6) CONN AS SYSDBA (OR  USER  OTHER  SESSION FOR SYSDBA) TO FIND THE SQL ID AND SQL TEXT

conn sys as  sysdba

select sql_id, sql_text from v$sql where sql_text like 'SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY

DEPTNO'
/

/* YOU WILL GET THE SQL_ID FOR THE  GIVEN SQL_TEXT

SQL_ID                         SQL_TEXT
--------------------------------------------------------------------------------
5z1tfx0xdds3t               SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
5z1tfx0xdds3t

*/

--7) CONNECT WITH THE USER

conn tuser/tuser

SET LINESIZE 100
COLUMN value FORMAT A60

SELECT value FROM   v$diag_info WHERE  name = 'MY_TEST_SESSION'
/

/* YOU WILL BE ABLE TO SEE THE LOCATION OF  YOUR TRACE FILE WITH THE ABOVE QUERY

VALUE
------------------------------------------------------------
D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_3656_MY_TEST_SESSION.trc
*/


--8)  NOW AFTER TRACING THE QUERY  SET TRACING  OFF

alter session set sql_Trace=true
/

--9) NOW GO TO COMMAND PROMPT TO RUN THE TKPROF UTILITY TO  SEE THE  RESULT. IT  WILL GENERATE A TRACE

FILE  WHICH
--WE  HUMAN CAN NOT UNDERSTAND BUT TKPROF UTILITY  FORMAT IT TO  HUMAN READABLE FORMAT


EXIT

--10) IN CMD GIVE THE BELOW COMMAND TKPROF

C:\Users\pushpjeet>TKPROF 'D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_2588_MY_TEST_SESSION'

'D:\MYTRACEFILE.TXT' TABLE=TUSER.EMP SYS=NO

TKPROF: Release 11.2.0.4.0 - Development on Mon Jun 5 06:36:08 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

--11) AFTER IT GENERATES THE OUTPUT  GO TO THE LOCATION YOU SPECIFIED  AND OPEN TO READ IT. BELOW IS MY

FILE  OUTPUT

/*

TKPROF: Release 11.2.0.4.0 - Development on Mon Jun 5 06:36:08 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: D:\APP\PUSHPJEET\diag\rdbms\orcl\orcl\trace\orcl_ora_2588_MY_TEST_SESSION.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

CREATE TABLE DEPT
     

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
********************************************************************************

declare
 error boolean;
  st_syn_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(st_syn_detected, -995);
 BEGIN
   if((sys.dbms_standard.dictionary_obj_type!='SYNONYM')or(sys.dbms_standard.dictionary_obj_owner!

='PUBLIC'))
   then
     return;
   end if;
   error :=
      CASE sys.dbms_standard.dictionary_obj_name
         WHEN 'ST_GEOMETRY' THEN TRUE
         WHEN 'ST_SURFACE' THEN TRUE
         WHEN 'ST_POLYGON' THEN TRUE
         WHEN 'ST_POINT' THEN TRUE
         WHEN 'ST_MULTISURFACE' THEN TRUE
         WHEN 'ST_MULTIPOINT' THEN TRUE
         WHEN 'ST_MULTILINESTRING' THEN TRUE
         WHEN 'ST_MULTICURVE' THEN TRUE
         WHEN 'ST_LINESTRING' THEN TRUE
         WHEN 'ST_GEOMCOLLECTION' THEN TRUE
         WHEN 'ST_CURVE' THEN TRUE
         WHEN 'ST_CURVEPOLYGON' THEN TRUE
         WHEN 'ST_COMPOUNDCURVE' THEN TRUE
         WHEN 'ST_CIRCULARSTRING' THEN TRUE
         WHEN 'ST_INTERSECTS' THEN TRUE
         WHEN 'ST_RELATE' THEN TRUE
         WHEN 'ST_TOUCH' THEN TRUE
         WHEN 'ST_CONTAINS' THEN TRUE
         WHEN 'ST_COVERS' THEN TRUE
         WHEN 'ST_COVEREDBY' THEN TRUE
         WHEN 'ST_INSIDE' THEN TRUE
         WHEN 'ST_OVERLAP' THEN TRUE
         WHEN 'ST_OVERLAPS' THEN TRUE
         WHEN 'ST_EQUAL' THEN TRUE
         WHEN 'ST_OVERLAPBDYDISJOINT' THEN TRUE
         WHEN 'ST_OVERLAPBDYINTERSECT' THEN TRUE
         WHEN 'ST_GEOMETRY_ARRAY' THEN TRUE
         WHEN 'ST_POINT_ARRAY' THEN TRUE
         WHEN 'ST_CURVE_ARRAY' THEN TRUE
         WHEN 'ST_SURFACE_ARRAY' THEN TRUE
         WHEN 'ST_LINESTRING_ARRAY' THEN TRUE
         WHEN 'ST_POLYGON_ARRAY' THEN TRUE
         ELSE FALSE
      END;
   if(error) then
     raise st_syn_detected;
   end if;
 END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.01       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.01       0.00          0          0          0           4

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)
********************************************************************************
       0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=3 us)

********************************************************************************


SQL ID: 3bwc66pu7gkp7 Plan Hash: 15469362

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
  THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4"), COUNT(DISTINCT C4),
  NVL(SUM(CASE WHEN C4 IS NULL THEN :"SYS_B_5" ELSE :"SYS_B_6" END),
  :"SYS_B_7")
FROM
 (SELECT /*+ NO_PARALLEL("S1") FULL("S1") NO_PARALLEL_INDEX("S1") */
  :"SYS_B_8" AS C1, :"SYS_B_9" AS C2, "S1"."DEPTNO" AS C3, "S1"."SAL" AS C4
  FROM "TUSER"."EMP" "S1") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)
Number of plan statistics captured: 1

DOWNLOAD FROM HERE