Parameter in Where clause may be Null or has Value
SELECT Person_Name FROM tblPersons WHERE AREA_CODE = pAreaCode AND (pRationCardNo IS NULL OR RATION_ID = pRationCardNo)
Parameter in Where clause may be Null or has Value
SELECT Person_Name FROM tblPersons WHERE AREA_CODE = pAreaCode AND (pRationCardNo IS NULL OR RATION_ID = pRationCardNo)
The data you are trying to insert already exists and its a primary key with unique value
unique constraint (DATABASENAME.PK_COLUMN_NAME) violated
When you try to insert some value which are not allowed for the column this error is thrown.Similar to ENUM of Java.
In the below example CATEGORY Column may have MALE and FEMALE But when you try to insert BOTH this is going to throw error since it is not allowed value.
CATEGORY IN ('MALE','FEMALE')
check constraint (DATABASENAME.CHK_CATEGORY) violated
If the Parent table referred by Foreign Key does not have the value then this is thrown
integrity constraint (DATABASENAME.FK_CATEGORY) violated - parent key not found
Whenever a table is created in Oracle its shld be done by Four step process
Table Creation
create table TABLE_NAME_1 ( COLUMN_NAME_1 varchar2(50), COLUMN_NAME_2 NUMBER(1), COLUMN_NAME_3 DATE not null, COLUMN_NAME_4 NUMBER(38) );
Constraint
Query 1
alter table TABLE_NAME_2 add constraint FK_COLUMN_NAME_1 foreign key (COLUMN_NAME_1) references TABLE_NAME_1 (COLUMN_NAME_1);
Query 2
alter table TABLE_NAME_3 add constraint fk_column_1 foreign key (COLUMN_1, COLUMN_2) references TABLE_NAME_2 (COLUMN_1, COLUMN_2);
Synonym
CREATE PUBLIC SYNONYM TABLE_NAME_1 FOR OWNER_NAME.TABLE_NAME_1; CREATE PUBLIC SYNONYM SEQUENCE_NAME_SEQ FOR OWNER_NAME.SEQUENCE_NAME_SEQ; GRANT ALL ON SEQUENCE_NAME_SEQ TO ADMIN,PART,ALL_DEVELOPERS;
Grants
GRANT ALL ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS; GRANT INSERT,UPDATE,DELETE,SELECT ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS;
Other Queries
ALTER TABLE TABLE_NAME_1 COLUMN_NAME_1 NOT NULL; ALTER TABLE TABLE_NAME_1 MODIFY COLUMN_NAME_1 varchar2(50); ALTER TABLE TABLE_NAME_1 DROP COLUMN COLUMN_NAME_1; ALTER TABLE TABLE_NAME_1 DROP CONSTRAINT COLUMN_NAME_1;
Things to Consider
While creating Primary key(No Null Values allowed like unique key) as a combination of two or more columns you should take in to consideration only the non null columns as part of unique key.
ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_TABLE_NAME PRIMARY KEY (COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3) USING INDEX TABLESPACE INDX;
COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3 should be a Non Null column.
When deleting a Table to run new script the following should be done.
DROP TABLE TABLE_NAME; DROP SEQUENCE SEQUENCE_NAME; DROP PUBLIC SYNONYM SYNONYM_NAME;
In case you are deleting multiple Table the same thing should be done with tables grouped together as one below.
DROP TABLE TABLE_NAME; . . . DROP SEQUENCE SEQUENCE_NAME; . . . DROP PUBLIC SYNONYM SYNONYM_NAME; . . . .
The Child Table referencing the Parent table should be deleted first.
DROP TABLE CHILD_TABLE_NAME; DROP TABLE PARENT_TABLE_NAME;
Checking if the table column is referenced some where by Child Table in form of Primary Key
SELECT TABLE_NAME AS "CHILD_TABLE" ,CONSTRAINT_NAME FROM ALL_CONSTRAINTS T WHERE R_OWNER = 'OWNER_NAME' AND CONSTRAINT_TYPE = 'R' AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE IN ('P', 'U') AND TABLE_NAME = 'TABLE_NAME' AND OWNER = 'OWNER_NAME') ORDER BY TABLE_NAME ,CONSTRAINT_NAME;
Sequence
CREATE SEQUENCE TEST_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;
Audit Trigger
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE INSERT OR UPDATE ON TABLE_NAME REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF INSERTING THEN :NEW.CRE_USER_UID := USERUID; :NEW.CRE_TIMESTAMP := SYSDATE; ELSIF UPDATING THEN :NEW.UPD_USER_UID := USERUID; :NEW.UPD_TIMESTAMP := SYSDATE; END IF; / END TRIGGER_NAME;
Note the / in the End of Trigger
Input
A B C D E
1.Find (in regular expression mode): (.+) Replace with ‘\1’
'A' 'B' 'C' 'D' 'E'
2.Find (in extended mode): \r\n Replace with ,
Output
'A','B','C','D','E'
UpStream and DownStream
In term of “flow of data”, your repo is at the bottom (“downstream”) of a flow coming from upstream repos (“pull from”) and going back to (the same or other) upstream repos (“push to”).
In terms of source control, you’re downstream when you copy (clone, checkout, etc) from a repository. Information flowed “downstream” to you.
When you make changes, you usually want to send them back “upstream” so they make it into that repository so that everyone pulling from the same source is working with all the same changes.
You cannot always make a branch or pull an existing branch and push back to it, because you are not registered as a collaborator for that specific project.
Forking
Forking is nothing more than a clone on the GitHub server side:
without the possibility to directly push back, with fork queue feature added to manage the merge request
You keep a fork in sync with the original project by:
Only contributor can approve the changes you pushed into fork for merge with original code
Clone
When you are cloning a GitHub repo on your local workstation, you cannot contribute back to the upstream repo unless you are explicitly declared as “contributor”.
So that clone (to your local workstation) isn’t a “fork”. It is just a clone.
upstream generally refers to the original repo that you have forked
origin is your fork: your own repo on GitHub, clone of the original repo of GitHub
The general pattern is as follows:
With respect to the String class:
The equals() method compares the “value” inside String instances (on the heap) irrespective if the two object references refer to the same String instance or not. If any two object references of type String refer to the same String instance then great! If the two object references refer to two different String instances .. it doesn’t make a difference. Its the “value” (that is: the contents of the character array) inside each String instance that is being compared.
On the other hand, the “==” operator compares the value of two object references to see whether they refer to the same String instance. If the value of both object references “refer to” the same String instance then the result of the boolean expression would be “true”..duh. If, on the other hand, the value of both object references “refer to” different String instances (even though both String instances have identical “values”, that is, the contents of the character arrays of each String instance are the same) the result of the boolean expression would be “false”.
You will have to override the equals function (along with others) to use this with custom classes.
The equals method compares the objects.
“==” is an operator and “equals” is a method. operators are used for primitive type comparisons and so “==” is used for memory address comparison.”equals” method is used for comparing objects.
The Behavior of equals on class which is final is different.So it is on ENUM.
final class A { // static public static String s; A() { this.s = new String( "aTest" ); } } final class B { private String s; B() { this.s = new String( "aTest" ); } public String getS() { return s; } }
First is the Normal working of equals over a String
public final class MyEqualityTest { public static void main( String args[] ) { String s1 = new String( "Test" ); String s2 = new String( "Test" ); System.out.println( "\n1 - PRIMITIVES "); System.out.println( s1 == s2 ); // false System.out.println( s1.equals( s2 )); // true } }
Now lets see how equals work in final class
A a1 = new A(); A a2 = new A(); System.out.println( "\n2 - OBJECT TYPES / STATIC VARIABLE" ); System.out.println( a1 == a2 ); // false System.out.println( a1.s == a2.s ); // true System.out.println( a1.s.equals( a2.s ) ); // true
In the above you can see that a1.s == a2.s is true.This is because s is static variable and its is possible to have only one instance.(Investigate Further)
Third case is which is well know.
B b1 = new B(); B b2 = new B(); System.out.println( "\n3 - OBJECT TYPES / NON-STATIC VARIABLE" ); System.out.println( b1 == b2 ); // false System.out.println( b1.getS() == b2.getS() ); // false System.out.println( b1.getS().equals( b2.getS() ) ); // true
How to override equals method
Now I have a Person class which has Name and Age as class variables.I want to override equals method so that I can check between 2 People objects.
public class Person { private String name; private int age; public Person(String name, int age){ this.name = name; this.age = age; } @Override public boolean equals(Object obj) { if (obj == null) { return false; } if (!Person.class.isAssignableFrom(obj.getClass())) { return false; } final Person other = (Person) obj; if ((this.name == null) ? (other.name != null) : !this.name.equals(other.name)) { return false; } if (this.age != other.age) { return false; } return true; } @Override public int hashCode() { int hash = 3; hash = 53 * hash + (this.name != null ? this.name.hashCode() : 0); hash = 53 * hash + this.age; return hash; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
TestEquals.java
public class TestEquals { public static void main(String[] args) { ArrayList<Person> people = new ArrayList<Person>(); people.add(new Person("Mugil",30)); people.add(new Person("Susan",23)); people.add(new Person("Madhu",32)); people.add(new Person("Monolisa",25)); Person checkPerson = new Person(); for (int i=0;i<people.size()-1;i++) { System.out.println("-- " + checkPerson.getName() + " - VS - " + people.get(i).getName()); boolean check = people.get(i).equals(checkPerson); System.out.println(check); } } }
You can get Eclipse to generate the two methods for you: Source > Generate hashCode() and equals()
The Software development can be grouped in to three phase.
1.Meeting Customer Requirement
2.Applying OOAD Principles
3.Design Patterns
We need to create an app which does search of cars in garage.The search is going to take specification of cars as input and display the matching cars.
Phase 1:
Car.java
public class Car { private String serialNumber; private String model; private String builder; private String price; private String type; Car(String pserialNumber, String pmodel, String pbuilder, String pprice, String ptype) { serialNumber=pserialNumber; model=pmodel; builder=pbuilder; price=pprice; type=ptype; } public String getSerialNumber() { return serialNumber; } public void setSerialNumber(String serialNumber) { this.serialNumber = serialNumber; } public String getModel() { return model; } public void setModel(String model) { this.model = model; } public String getBuilder() { return builder; } public void setBuilder(String builder) { this.builder = builder; } public String getPrice() { return price; } public void setPrice(String price) { this.price = price; } public String getType() { return type; } public void setType(String type) { this.type = type; } public enum Type { PETROL, DIESEL; public String toString() { switch (this) { case PETROL: return "petrol"; case DIESEL: return "diesel"; } return null; } } public enum Builder { FORD, HONDA, TOYOTA; public String toString() { switch (this) { case FORD: return "ford"; case HONDA: return "honda"; case TOYOTA: return "toyota"; } return null; } } }
Garage.java
import java.util.ArrayList; import java.util.Iterator; import java.util.List; public class Garage { private List<Car> carList = new ArrayList<Car>(); public void addCar(String pserialNumber, String pmodel, String pbuilder, String pprice, String ptype) { Car objCar = new Car(pserialNumber, pmodel, pbuilder, pprice, ptype); carList.add(objCar); } //The guitar will be returned only when all Search Criteria Match public List<Car> searchCar(Car searchCar) { List<Car> resultCarList = new ArrayList<Car>(); for (Iterator iterator = carList.iterator(); iterator.hasNext();) { Car Car = (Car) iterator.next(); if(!searchCar.getType().equals(Car.getType())) continue; if(!searchCar.getBuilder().equals(Car.getBuilder())) continue; resultCarList.add(Car); } return resultCarList; } public Car getCar(String SerialNo) { return null; } }
SearchCar.java
import java.io.IOException; import java.util.Iterator; import java.util.List; public class SearchCar { public static void main(String[] args) throws IOException { Garage objGarage = new Garage(); initializeCar(objGarage); Car searchCar = new Car("", "A1", Car.Builder.FORD.toString(), "", Car.Type.PETROL.toString()); List<Car> searchResult = objGarage.searchCar(searchCar); if(searchResult != null) { System.out.println("Search Result"); System.out.println("-------------------------------"); for (Iterator iterator = searchResult.iterator(); iterator.hasNext();) { Car Car = (Car) iterator.next(); System.out.println("Model : "+ Car.getModel()); System.out.println("Builder : "+ Car.getBuilder()); System.out.println("Type : "+ Car.getType()); System.out.println("-------------------------------"); } }else { System.out.println("Sorry! We are unable to Find Car..."); } } public static void initializeCar(Garage pobjGarage) { //pserialNumber, pmodel, pbuilder, pprice, ptype pobjGarage.addCar("", "Mustang", Car.Builder.FORD.toString(), "", Car.Type.PETROL.toString()); pobjGarage.addCar("", "Corolla", Car.Builder.TOYOTA.toString(), "", Car.Type.DIESEL.toString()); pobjGarage.addCar("", "Endevadour", Car.Builder.FORD.toString(), "", Car.Type.PETROL.toString()); pobjGarage.addCar("", "Civic", Car.Builder.HONDA.toString(), "", Car.Type.PETROL.toString()); } }
Points to Note:
Phase 2
Object Orientation
In the above code the Search criteria used can be split separately.
This includes Type, Model and Builder.
The Price and Serial No are not moved to new class since they are unique.
CarSpec.java
public class CarSpec { private String model; private String builder; private String type; public CarSpec(String pbuilder, String pmodel,String ptype){ model=pmodel; builder=pbuilder; type=ptype; } public String getModel() { return model; } public void setModel(String model) { this.model = model; } public String getBuilder() { return builder; } public void setBuilder(String builder) { this.builder = builder; } public String getType() { return type; } public void setType(String type) { this.type = type; } }
Now the car class is going to be replaced with CarSpec variable as property.
public class Car { private String serialNumber; private String price; private CarSpec carSpecification; Car(String pserialNumber, String pprice, CarSpec pcarSpec) { serialNumber=pserialNumber; price=pprice; carSpecification = pcarSpec; } public String getSerialNumber() { return serialNumber; } public void setSerialNumber(String serialNumber) { this.serialNumber = serialNumber; } public String getPrice() { return price; } public void setPrice(String price) { this.price = price; } public CarSpec getCarSpecification() { return carSpecification; } public void setCarSpecification(CarSpec carSpecification) { this.carSpecification = carSpecification; } public CarSpec getCarSpec(){ return carSpecification; } }
Since the Specification of the car are moved separately they can be used for both searching and storing car details.
Now the searchCar() method in Garage.java should be allowed to take car Specification as argument instead of whole car object which contains redundant Price and Serial No which are unique.
Garage.java
import java.util.ArrayList; import java.util.Iterator; import java.util.List; public class Garage { private List<Car> carList = new ArrayList<Car>(); public void addCar(String pserialNumber, String pprice, CarSpec pcarSpec) { Car objCar = new Car(pserialNumber, pprice, pcarSpec); carList.add(objCar); } //The guitar will be returned only when all Search Criteria Match public List<Car> searchCar(CarSpec searchCarSpec) { List<Car> resultCarList = new ArrayList<Car>(); for (Iterator iterator = carList.iterator(); iterator.hasNext();) { Car objCar = (Car) iterator.next(); CarSpec objCarSpec = objCar.getCarSpec(); if(!objCarSpec.getBuilder().equals(searchCarSpec.getBuilder())) continue; if(!objCarSpec.getType().equals(searchCarSpec.getType())) continue; resultCarList.add(objCar); } return resultCarList; } }
Searchcar.java
public class SearchCar { public static void main(String[] args) throws IOException { Garage objGarage = new Garage(); initializeCar(objGarage); CarSpec searchCar = new CarSpec(Builder.FORD.toString(), "", Type.PETROL.toString()); List<Car> searchResult = objGarage.searchCar(searchCar); if(searchResult != null) { System.out.println("Search Result"); System.out.println("-------------------------------"); for (Iterator iterator = searchResult.iterator(); iterator.hasNext();) { Car objCar = (Car) iterator.next(); CarSpec objSpec = objCar.getCarSpec(); System.out.println("Car Name : "+ objCar.getSerialNumber()); System.out.println("Car Name : "+ objCar.getPrice()); System.out.println("Model : "+ objSpec.getModel()); System.out.println("Builder : "+ objSpec.getBuilder()); System.out.println("Type : "+ objSpec.getType()); System.out.println("-------------------------------"); } }else { System.out.println("Sorry! We are unable to Find Car..."); } } public static void initializeCar(Garage pobjGarage) { //pserialNumber, pmodel, pbuilder, pprice, ptype pobjGarage.addCar("101", "Mustang", new CarSpec(Builder.FORD.toString(), "3200", Type.PETROL.toString())); pobjGarage.addCar("201", "Corolla", new CarSpec(Builder.TOYOTA.toString(), "3500", Type.DIESEL.toString())); pobjGarage.addCar("102", "Endevadour", new CarSpec(Builder.FORD.toString(), "5200", Type.PETROL.toString())); pobjGarage.addCar("301", "Civic", new CarSpec(Builder.HONDA.toString(), "3000", Type.PETROL.toString())); } }
Now we are going to move the Search Car Code from Garage.java to CarSpec.java.By doing this we are delegating the job to highly coherent class.
In case any code to be added in future the changes to be done are only confined within this class.We are also going to perform object to object comparison.
Garage.java
. . . public List<Car> searchCar(CarSpec searchCarSpec) { List<Car> resultCarList = new ArrayList<Car>(); for (Iterator iterator = carList.iterator(); iterator.hasNext();) { Car objCar = (Car) iterator.next(); CarSpec objCarSpec = objCar.getCarSpec(); if(objCarSpec.findMatchingCar(searchCarSpec)) resultCarList.add(objCar); } return resultCarList; } . . .
we are calling findMatchingCar method of CarSpec.java instead of doing the comparison in the same class.
CarSpec.java
. . . public boolean findMatchingCar(CarSpec objCarsPEC) { if(builder != objCarsPEC.getBuilder()) return false; if(type != objCarsPEC.getType()) return false; return true; } . . .
————————————————————————————————
Things Learned
SELECT A.PinCode ,A.Location ,A.Year ,SUM(CASE WHEN A.Area IN ('North Chennai', 'South Chennai') THEN A.Amount ELSE 0 END) AS total_amount_north_south ,SUM(CASE WHEN A.Area IN ('East Chennai', 'West Chennai') THEN A.Amount ELSE 0 END) AS total_amount_east_west ,SUM(A.Amount) AS Total_amount_for_Chennai FROM Tamilnadu GROUP BY A.PinCode ,A.Location ,A.Year
How can I add Key/Value pair to Object
var obj = { key1: value1, key2: value2 };
There are two ways to add new properties to an object
Method 1 – Using dot notation
obj.key3 = "value3";
Method 2 – Using square bracket notation
obj["key3"] = "value3";
The first form is used when you know the name of the property. The second form is used when the name of the property is dynamically determined. Like in this example:
var getProperty = function (propertyName) { return obj[propertyName]; }; getProperty("key1"); getProperty("key2"); getProperty("key3");
A real JavaScript array can be constructed using either:
The Array literal notation:
var arr = [];
The Array constructor notation:
var arr = new Array();