Oracle Useful Queries

SQL: UNION Query

The SQL UNION query allows you to combine the result sets of 2 or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL UNION query is:

select field1, field2, . field_n from tables

UNION

select field1, field2, . field_n from tables;

SQL UNION Query - Returns single field example

The following is an example of the SQL UNION query that returns one field from multiple SELECT statements (and both fields have the same data type):

select supplier_id from suppliers

UNION

select supplier_id from orders;

In this SQL UNION query example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The SQL UNION query removes duplicates. If you do notwish to remove duplicates, try using the SQL UNION ALL query.

SQL: UNION ALL Query

The SQL UNION ALL query allows you to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

Each SQL SELECT statement within the SQL UNION ALL query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL UNION ALL query is:

select field1, field2, ... field_n from tables

UNION ALL

select field1, field2, ... field_n from tables;

SQL UNION ALL Query - Returns single field example

The following is an example of the SQL UNION ALL query that returns one field from multiple SELECT statements (and both fields have the same data type):

select supplier_id from suppliers

UNION ALL

select supplier_id from orders;

This SQL UNION ALL query would return a supplier_id multiple times in your result set if the supplier_id appeared in both the suppliers and orders table. The SQL UNION ALL query does not remove duplicates. If you wish to remove duplicates, try using the SQL UNION query.

SQL: INTERSECT Query

The SQL INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SQL statement within the SQL INTERSECT query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL INTERSECT query is:

select field1, field2, . field_n from tables

INTERSECT

select field1, field2, . field_n from tables;

SQL INTERSECT Query - Single field example

The following is an example of an SQL INTERSECT query that has one field with the same data type:

select supplier_id from suppliers

INTERSECT

select supplier_id from orders;

In this SQL INTERSECT query example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

SQL: MINUS Query

The SQL MINUS query returns all rows in the first SQL SELECT statement that are not returned in the second SQL SELECT statement.

Each SQL SELECT statement within the SQL MINUS query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL MINUS query is:

select field1, field2, ... field_n from tables

MINUS

select field1, field2, ... field_n from tables;

SQL MINUS Query - Single field example

The following is an example of an SQL MINUS query that has one field with the same data type:

select supplier_id from suppliers

MINUS

select supplier_id from orders;

This SQL Minus query example returns all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

Oracle: DUAL Table

The Oracle DUAL table

dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

SQL> desc dual

 Name                    Null?    Type

 ----------------------- -------- ----------------

 DUMMY                            VARCHAR2(1)

SQL> select * from dual;

D

-

X

The owner of dual is SYS but dual can be accessed by every user.

As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefor, dual is the prefered table to select a pseudo column (such as sysdate

select sysdate from dual

Although it is possible to delete the one record, or insert additional records, one really should not do that!.

SOAP UI and Groovy Script

SoapUI is an open source web service testing application for service-oriented architectures (SOA). Its functionality covers web serviceinspection, invoking, development, simulation and mocking, functional testing, load and compliance testing.

Groovy is an object-oriented programming language for the Java platform. It is a dynamic language with features similar to those of Python, Ruby,Perl, and Smalltalk.It can be used as a scripting language for the Java Platform, is dynamically compiled to Java Virtual Machine (JVM) bytecode, and interoperates with other Java code and libraries. 

RightNow WSDL Format

                            Connect Web Services should be used to extend the functionality offered by RightNow CX and the ability to access data stored in the RightNow CX platform.

WSDL Creation:

There are two WSDL files that provide access to Connect Web Services:

• Standard WSDL – This WSDL should be used by application developers building integrations specific to their Oracle RightNow CX instance. The typed WSDL is a strongly typed representation of the Connect Common Object Model. The strongly typed WSDL can be accessed from the following URL:

http://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=typed

• Partner WSDL – This WSDL should be used by application developers building integrations that function across multiple instances of Oracle RightNow CX. It provides the ability to work with Generic Objects that can be used across multiple instances of Oracle RightNow CX. The generic WSDL can be accessed from the following URL:

http://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=generic

Difference between Bio Data, Resume and CV

Oracle: Foreign Key(Error) : ORA-02298: cannot validate (tablespace.FK_table1) - parent keys not found

                     Adding a foreign key to an existing table, some times it show the error like "ORA-02298: cannot validate (tablespace.FK_table1) - parent keys not found"

Way to solve:

Sample Query to know about the parent and child tables.,

select xxxxx from child_table a where not exists (select 'x' from parent_table where primary_key = a.key_values);

Description.,


This will show you the row (or rows) that exists in the child table that
don't exist in the parent table. Once you have either (added rows
to the parent table to match the child rows) or (removed the child
rows that don't have parents), then you can enable your RI constraints

Oracle: Creation of Foreign Key

Three ways to create a foreign key..

1.Foreign Keys with default
2.Foreign Keys with cascade delete
3.Foreign Keys with "set null on delete"


By Default.,

              Blocks the delete operation in parent table when the data in child table.

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);


By Cascade delete.,
                       
                       A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE CASCADE
);


Set "null" on delete.,

                     A foreign key with a "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE SET NULL
);