Collection In oracle

DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish')  := 62000;
   salary_list('Minakshi')  := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;

   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;

DECLARE
   CURSOR c_customers is
      select  name from customers;
 
   TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
   name_list c_list;
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
  END LOOP;
END;

DECLARE
   TYPE names_table IS TABLE OF VARCHAR2(10);
   TYPE grades IS TABLE OF INTEGER;

   names names_table;
   marks grades;
   total integer;
BEGIN
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i IN 1 .. total LOOP
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
   end loop;
END;

DECLARE

   CURSOR c_customers is 

      SELECT  name FROM customers;

   TYPE c_list IS TABLE of customers.name%type;

   name_list c_list := c_list();

   counter integer :=0;

BEGIN

   FOR n IN c_customers LOOP

      counter := counter +1;

      name_list.extend;

      name_list(counter)  := n.name;

      dbms_output.put_line('Customer('||counter||'):'||name_list(counter));

   END LOOP;

END;

package

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents.

First step package specification
Second step package body

/*table*/
Create table customers(id int,name varchar(60),age int,address varchar(60),salary number(6,2))
/* insert value*/
insert into customers(id,name,age,address,salary)values(200,'prakash',12,'lko',1200.90);
insert into customers(id,name,age,address,salary)values(210,'manoj',13,'kan',1435.90);
insert into customers(id,name,age,address,salary)values(220,'raj',14,'lko',12567.90);
insert into customers(id,name,age,address,salary)values(230,'rohan',15,'kan',15460.90);
insert into customers(id,name,age,address,salary)values(240,'rakesh',16,'unnao',120000.90);
insert into customers(id,name,age,address,salary)values(250,'puneet',17,'kan',1289.90);
select * from CUSTOMERS;
/*package specification*/
CREATE or replace PACKAGE cust_sal AS
   PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;

/*package body*/

CREATE OR REPLACE PACKAGE BODY cust_sal AS
   PROCEDURE find_sal(c_id customers.id%TYPE) IS
   c_sal customers.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM customers
      WHERE id = c_id;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END cust_sal;
/*working packge*/
DECLARE
   code customers.id%type := &cc_id;
BEGIN
   cust_sal.find_sal(code);
END;

Trigger

What is a Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed. Syntax of Triggers Syntax for Creating a Trigger CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) BEGIN --- sql statements END; We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table. 1) Create the 'product' table and 'product_price_history' table CREATE TABLE product_price_history (product_id number(5), product_name varchar2(32), supplier_name varchar2(32), unit_price number(7,2) ); CREATE TABLE product (product_id number(5), product_name varchar2(32), supplier_name varchar2(32), unit_price number(7,2) ); 2) Create the price_history_trigger and execute it. CREATE or REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES (:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; / 3) Lets update the price of a product. UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100 Once the above update query is executed, the trigger fires and updates the 'product_price_history' table. 4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back. Types of PL/SQL Triggers There are two types of triggers based on the which level it is triggered. 1) Row level trigger - An event is triggered for each row upated, inserted or deleted.  2) Statement level trigger - An event is triggered for each sql statement executed.  PL/SQL Trigger Execution Hierarchy The following hierarchy is followed when a trigger is fired. 1) BEFORE statement trigger fires first. 2) Next BEFORE row level trigger fires, once for each row affected.  3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers. 4) Finally the AFTER statement level trigger fires. For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired. CREATE TABLE product (Message varchar2(50), Current_Date number(32) ); Let's create a BEFORE and AFTER statement and row level triggers for the product table. 1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level. CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_check Values('Before update, statement level',sysdate); END; / 2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated. CREATE or REPLACE TRIGGER Before_Upddate_Row_product BEFORE UPDATE ON product FOR EACH ROW BEGIN INSERT INTO product_check Values('Before update row level',sysdate); END; / 3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' after a sql update statement is executed, at the statement level. CREATE or REPLACE TRIGGER After_Update_Stat_product AFTER UPDATE ON product BEGIN INSERT INTO product_check Values('After update, statement level', sysdate); End; / 4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'product_check' after each row is updated. CREATE or REPLACE TRIGGER After_Update_Row_product AFTER insert On product FOR EACH ROW BEGIN INSERT INTO product_check Values('After update, Row level',sysdate); END; / Now lets execute a update statement on table product. UPDATE PRODUCT SET unit_price = 800 WHERE product_id in (100,101); Lets check the data in 'product_check' table to see the order in which the trigger is fired. SELECT * FROM product_check; Output: Mesage                                             Current_Date ------------------------------------------------------------ Before update, statement level          26-Nov-2008 Before update, row level                    26-Nov-2008 After update, Row level                     26-Nov-2008 Before update, row level                    26-Nov-2008 After update, Row level                     26-Nov-2008 After update, statement level            26-Nov-2008 The above result shows 'before update' and 'after update' row level events have occured twice, since two records were updated. But 'before update' and 'after update' statement level events are fired only once per sql statement. The above rules apply similarly for INSERT and DELETE statements. How To know Information about Triggers. We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger. The below statement shows the structure of the view 'USER_TRIGGERS' DESC USER_TRIGGERS; NAME                              Type -------------------------------------------------------- TRIGGER_NAME                 VARCHAR2(30) TRIGGER_TYPE                  VARCHAR2(16) TRIGGER_EVENT                VARCHAR2(75) TABLE_OWNER                  VARCHAR2(30) BASE_OBJECT_TYPE           VARCHAR2(16) TABLE_NAME                     VARCHAR2(30) COLUMN_NAME                  VARCHAR2(4000) REFERENCING_NAMES        VARCHAR2(128) WHEN_CLAUSE                  VARCHAR2(4000) STATUS                            VARCHAR2(8) DESCRIPTION                    VARCHAR2(4000) ACTION_TYPE                   VARCHAR2(11) TRIGGER_BODY                 LONG This view stores information about header and body of the trigger. SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product'; The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'. You can drop a trigger using the following command. DROP TRIGGER trigger_name; CYCLIC CASCADING in a TRIGGER This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situtation does not exist. The below example shows how Trigger's can enter into cyclic cascading. Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created. 1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'. 2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.

Create PROCEDURE

CREATE TABLE CUSTOMER (
NAME VARCHAR2(20),  
GENDER VARCHAR2(7),  
ADDRESS VARCHAR2(100));

/*create a producter inert*/


CREATE OR REPLACE PROCEDURE INSERTValue (
p_name CUSTOMER.NAME%TYPE,
p_gender CUSTOMER.GENDER%TYPE,
p_address CUSTOMER.ADDRESS%TYPE)  
IS
BEGIN
INSERT INTO CUSTOMER (NAME, GENDER, ADDRESS)
VALUES (p_name, p_gender, p_address);
COMMIT;
END;

/* Excuted Producter*/
BEGIN
   INSERTValue('&p','&m','&l');
END;


select * from CUSTOMER



CREATE OR REPLACE PROCEDURE UPDATEcustomer (  
p_name IN CUSTOMER.NAME%TYPE,
p_gender IN CUSTOMER.GENDER%TYPE,
p_address IN CUSTOMER.ADDRESS%TYPE)
IS
BEGIN
UPDATE CUSTOMER SET NAME=p_name, GENDER=p_gender, ADDRESS=p_address WHERE NAME=p_name;
COMMIT;
END;

/*DELETE Customer procedure*/

CREATE OR REPLACE PROCEDURE DELETEcustomer (  
p_name IN CUSTOMER3.NAME%TYPE)
IS
BEGIN
DELETE FROM CUSTOMER WHERE NAME=p_name;
END;

interview QuesAnswer

Write a program to remove duplicates from sorted array. 

public class MyDuplicateElements {

    public static int[] removeDuplicates(int[] input){

         

        int j = 0;

        int i = 1;

               if(input.length < 2){

            return input;

        }

        while(i < input.length){

            if(input[i] == input[j]){

                i++;

            }else{

                input[++j] = input[i++];

            }   

        }

        int[] output = new int[j+1];

        for(int k=0; k<output.length; k++){

            output[k] = input[k];

        }

         

        return output;

    }

     

    public static void main(String a[]){

        int[] array = {2,6,6,6,8,9,10,10,3,11,11};

        int[] output = removeDuplicates(array);

        for(int i:output){

            System.out.print(i+" ");

        }

    }

}

Write a program to find out duplicate characters in a string.

import java.util.HashMap;

import java.util.Map;

import java.util.Set;

public class DuplicateCharsInString {

    public void findDuplicateChars(String str){

         

        Map<Character, Integer> dupMap = new HashMap<Character, Integer>();

        char[] chrs = str.toCharArray();

        for(Character ch:chrs){

            if(dupMap.containsKey(ch)){

                dupMap.put(ch, dupMap.get(ch)+1);

            } else {

                dupMap.put(ch, 1);

            }

        }

        Set<Character> keys = dupMap.keySet();

        for(Character ch:keys){

            if(dupMap.get(ch) > 1){

                System.out.println(ch+"--->"+dupMap.get(ch));

            }

        }

    }

     

    public static void main(String a[]){

        DuplicateCharsInString dcs = new DuplicateCharsInString();

        dcs.findDuplicateChars("Java2Novice");

    }

}

Microsoft SQL Server vs. Oracle

There are many different relational database management systems (RDBMS) out there. You have probably heard about Microsoft Access, Sybase, and MySQL, but the two most popular and widely used are Oracle and MS SQL Server. Although there are many similarities between the two platforms, there are also a number of key differences. In this blog, I will be taking a look at several in particular, in the areas of their command language, how they handle transaction control and their organization of database objects.

Language

Perhaps the most obvious difference between the two RDBMS is the language they use. Although both systems use a version of Structured Query Language, or SQL, MS SQL Server uses Transact SQL, or T-SQL, which is an extension of SQL originally developed by Sybase and used by Microsoft. Oracle, meanwhile, uses PL/SQL, or Procedural Language/SQL. Both are different “flavors” or dialects of SQL and both languages have different syntax and capabilities. The main difference between the two languages is how they handle variables, stored procedures, and built-in functions. PL/SQL in Oracle can also group procedures together into packages, which can’t be done in MS SQL Server. In my humble opinion, PL/SQL is complex and potentially more powerful, while T-SQL is much more simple and easier to use.

Transaction Control

Another one of the biggest differences between Oracle and MS SQL Server is transaction control. For the purposes of this article, a transaction can be defined as a group of operations or tasks that should be treated as a single unit. For instance, a collection of SQL queries modifying records that all must be updated at the same time, where (for instance) a failure to update any single records among the set should result in none of the records being updated. By default, MS SQL Server will execute and commit each command/task individually, and it will be difficult or impossible to roll back changes if any errors are encountered along the way. To properly group statements, the “BEGIN TRANSACTION” command is used to declare the beginning of a transaction, and either a COMMIT statement is used at the end. This COMMIT statement will write the changed data to disk, and end the transaction. Within a transaction, ROLLBACK will discard any changes made within the transaction block. When properly used with error handling, the ROLLBACK allows for some degree of protection against data corruption. After a COMMIT is issued, it is not possible to roll back any further than the COMMIT command.

Within Oracle, on the other hand, each new database connection is treated as new transaction. As queries are executed and commands are issued, changes are made only in memory and nothing is committed until an explicit COMMIT statement is given (with a few exceptions related to DDL commands, which include “implicit” commits, and are committed immediately). After the COMMIT, the next command issued essentially initiates a new transaction, and the process begins again. This provides greater flexibility and helps for error control as well, as no changes are committed to disk until the DBA explicitly issues the command to do so.

Organization of Database Objects

The last difference I want to discuss is how the RDBMS organizes database objects. MS SQL Server organizes all objects, such as tables, views, and procedures, by database names. Users are assigned to a login which is granted accesses to the specific database and its objects. Also, in SQL Server each database has a private, unshared disk file on the server. In Oracle, all the database objects are grouped by schemas, which are a subset collection of database objects and all the database objects are shared among all schemas and users. Even though it is all shared, each user can be limited to certain schemas and tables via roles and permissions.

In short, both Oracle and SQL Server are powerful RDBMS options. Although there are a number of differences in how they work “under the hood,” they can both be used in roughly equivalent ways. Neither is objectively better than the other, but some situations may be more favorable to a particular choice. Either way, Segue can support these systems and help to make recommendations on how to improve, upgrade, or maintain your key mission-critical infrastructure to make sure that you can keep your focus on doing business.

Platform dependency

SQL Server is only operable on the Windows platform, a major limitation for it to be an enterprise solution. Oracle is available on multiple platforms such as Windows, all flavors of Unix from vendors such as IBM, Sun, Digital, HP, Sequent, etc. and VAX-VMS, as well as MVS. The multi-platform nature of Oracle makes it a true enterprise solution

Performance and tuning

1. In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.
2. All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.
3. No range partioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.
4. There is no partitioning in SQL Server.
5. There are no bitmap indexes in SQL Server.
6. There are no reverse key indexes in SQL Server.
7. There are no function-based indexes in SQL Server.
8. There is no star query optimization in SQL Server.

Object types

Here are some object types missing in SQL Server that exist in Oracle.

1. You cannot declare public or private synonyms.
2. There is no such thing as independent sequence objects.
3. There are no packages; i.e., collections of procedures and functions.
4. No "before" event triggers (only "after" event triggers) and no row triggers (only statement).
5. No object types like in PL/SQL.

PL/SQL versus T-SQL

1. In T-SQL there are significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a code challenge re-write. The INNER JOIN, LEFT OUTER, RIGHT OUTER JOIN syntax differs from the classic JOIN.
2. Stored procedures in SQL Server are not compiled until executed (which means overhead, late binding and finding errors at execution time only!).
3. No ability to read/write from external files from a stored procedure.
4. PL/SQL has many DBMS system packages, where T-SQL relies only on a limited number of extended and system stored procedures in the master database.
5. PL/SQL is better in error exception handling, and there is no such thing as exceptions in T-SQL (T-SQL uses @@error -- not elegant!).
6. T-SQL does not have the MINUS operator, that makes finding schema differences more difficult in SQL Server.
7. In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes.
8. In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers.

Reliability

1. In SQL Server Standard Edition there is no ability to mirror the transaction log files. In Enterprise Edition there is a log shipping process that is not so reliable.
2. If the logs fill up the disk, the database will crash hard.
3. Sometimes this requires the server itself to be rebooted.

Hibernate

Hibernate framework simplifies the development of java application to interact with the database. Hibernate is an open source, lightweight, ORM (Object Relational Mapping) tool.

An ORM tool simplifies the data creation, data manipulation and data access. It is a programming technique that maps the object to the data stored in the database

1) Opensource and Lightweight

2) Fast performance

3) Database Independent query

4) Automatic table creation

5) Simplifies complex join

6) Provides query statistics and database status

The Hibernate architecture includes many objects persistent object, session factory, transaction factory, connection factory, session, transaction etc.

There are 4 layers in hibernate architecture java application layer, hibernate framework layer, backhand api layer and database layer

Hibernate framework uses many objects session factory, session, transaction etc. alongwith existing Java API such as JDBC (Java Database Connectivity), JTA (Java Transaction API) and JNDI (Java Naming Directory Interface).

Elements of Hibernate Architecture

For creating the first hibernate application, we must know the elements of Hibernate architecture. They are as follows:

SessionFactory

The SessionFactory is a factory of session and client of ConnectionProvider. It holds second level cache (optional) of data. The org.hibernate.SessionFactory interface provides factory method to get the object of Session.

Session

The session object provides an interface between the application and data stored in the database. It is a short-lived object and wraps the JDBC connection. It is factory of Transaction, Query and Criteria. It holds a first-level cache (mandatory) of data. The org.hibernate.Session interface provides methods to insert, update and delete the object. It also provides factory methods for Transaction, Query and Criteria.

Transaction

The transaction object specifies the atomic unit of work. It is optional. The org.hibernate.Transaction interface provides methods for transaction management.

ConnectionProvider

It is a factory of JDBC connections. It abstracts the application from DriverManager or DataSource. It is optional.

TransactionFactory

It is a factory of Transaction. It is optional.

maven

What is Maven?

Maven is a project management and comprehension tool. Maven provides developers a complete build lifecycle framework. Development team can automate the project's build infrastructure in almost no time as Maven uses a standard directory layout and a default build lifecycle.

In case of multiple development teams environment, Maven can set-up the way to work as per standards in a very short time. As most of the project setups are simple and reusable, Maven makes life of developer easy while creating reports, checks, build and testing automation setups.

Maven provides developers ways to manage following:

• Builds
• Documentation
• Reporting
• Dependencies
• SCMs
• Releases
• Distribution
• mailing list

Do You Know ?

• Difference between Ant and Maven
• How to install Maven ?
• Maven Repositories: Local, Server and Central
• How to create Maven Example?
• How to create Maven Web Application?
• What is Project Object Model?

then given answer

test

for basic  java
https://docs.google.com/forms/d/e/1FAIpQLSc16aUhnX1GaqV2JAl83NKFD0sdCI7UK4l-794baGIZSGbQag/viewform

for basuc sql

work on  link https://goo.gl/forms/V62XE6AacxEquCuq1

ques paper

QUES PAPER FOR JAVA

https://goo.gl/forms/bP8HvK8e6CIIUx3h1


QUES PAPER FOR SQL


https://goo.gl/forms/UxymlpeQ6mKja9rz2

matcher

Subexpression	Matches
^	Matches beginning of line.
$	Matches end of line.
.	Matches any single character except newline. Using m option allows it to match newline as well.
[...]	Matches any single character in brackets.
[^...]	Matches any single character not in brackets
\A	Beginning of entire string
\z	End of entire string
\Z	End of entire string except allowable final line terminator.
re*	Matches 0 or more occurrences of preceding expression.
re+	Matches 1 or more of the previous thing
re?	Matches 0 or 1 occurrence of preceding expression.
re{ n}	Matches exactly n number of occurrences of preceding expression.
re{ n,}	Matches n or more occurrences of preceding expression.
re{ n, m}	Matches at least n and at most m occurrences of preceding expression.
a| b	Matches either a or b.
(re)	Groups regular expressions and remembers matched text.
(?: re)	Groups regular expressions without remembering matched text.
(?> re)	Matches independent pattern without backtracking.
\w	Matches word characters.
\W	Matches nonword characters.
\s	Matches whitespace. Equivalent to [\t\n\r\f].
\S	Matches nonwhitespace.
\d	Matches digits. Equivalent to [0-9].
\D	Matches nondigits.
\A	Matches beginning of string.
\Z	Matches end of string. If a newline exists, it matches just before newline.
\z	Matches end of string.
\G	Matches point where last match finished.
\n	Back-reference to capture group number "n"
\b	Matches word boundaries when outside brackets. Matches backspace (0x08) when inside brackets.
\B	Matches nonword boundaries.
\n, \t, etc.	Matches newlines, carriage returns, tabs, etc.
\Q	Escape (quote) all characters up to \E
\E	Ends quoting begun with \Q
No.	Character Class	Description
1	[abc]	a, b, or c (simple class)
2	[^abc]	Any character except a, b, or c (negation)
3	[a-zA-Z]	a through z or A through Z, inclusive (range)
4	[a-d[m-p]]	a through d, or m through p: [a-dm-p] (union)
5	[a-z&&[def]]	d, e, or f (intersection)
6	[a-z&&[^bc]]	a through z, except for b and c: [ad-z] (subtraction)
7	[a-z&&[^m-p]]	a through z, and not m through p: [a-lq-z](subtraction)