Having Sums, Averages, and Other Grouped Data

When you strive to get an  average 

1. The business requirement is what is current average salary for all Employees.

select AVG(salary) from employee;

  The avg aggregate function sum up the salary values and then divides it by total number of employee

  records those doesn't have the NULL salary .

  Thus the avg function ignores the NULL values 

  To get the business required answer substitute the Non NULL value for NULL value.

   
select AVG(NVL(salary,0)) from employee;

  This will return the exact average salary of all employee.

The Difference between Count(*) and  Count(Coulmn_name) 

 The count (*) return the all the records which satisfy the  query condition and count(*)  does not ignore  the null value, however the count(Column_name)  ignores the null records from the count.


Categorization and aggregation  of data.

  The group by clause enables  you to collect the data from multiple records and tclub it by one or more columns.
 The Aggregate function and the group by clause used to tandem  to determine the aggregate value for every group.

 count of employees in each department

select COUNT(employee_id), department_id
    from employee
    GROUP BY department_id
    ORDER BY department_id;

When the group by is followed by the order by then the clumn listed in the order should be listed in the select  , otherwise it will flag an error message.
  similarly  if the column listed in the group by should be listed in the select.

--,ASC, DESC, NULLS FIRST, and NULLS LAST options behave and how null values are handled by default in an ORDER BY clause


HAVING the last word   
   
 Just like the select list can use the where clause to filter records from the result set those satisfying the condition 
  mentioned in the where clause , similarly to filter the result of group by clause (Categorized data)   the having function is used .
 

 

Purging the SOA Infra

This is very useful and straight forward process to clean up SOA database schema. In real world , server are receiving millions of requests in a day and keeping these all data as instances in SOA Suite database schema is very costly. It can affect a performance of the server up to some extent. After few days or month probably you will start receiving table space errors as allotted all the table sapce is already been used by the instances created within SOA database schema. For this reason you need to plan your tablesapce accordingly and generally it should be in between 50 GB - 80 GB in loaded server. And still it requires regular purging for data on the SOA database.

What data does Oracle SOA Suite 11g (PS6 11.1.1.7) store?

Composite instances utilising the SOA Suite Service Engines (BPEL, mediator, human task, rules, BPM, OSB, EDN etc.) will write data to tables residing within the SOAINFRA schema. Each of the engines will either write data to specific engine tables (e.g. the CUBE_INSTANCE table is used solely by the BPEL engine) or common tables that are shared by the SOA Suite engines such as the AUDIT_TRAIL table.

Which data will be purged by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?

The purge script will delete composite instances that are in the following states:

Completed

Faulted
Terminated by user
Stale
Unknown

The purge script will NOT delete composite instances that are in the following states:

Running (in-flight)
Suspended

Pending Recovery

1. First of all you will required Repository creation utility for 11.1.1.4. This installable contain the all required purging script provided by oracle to purge the database schema  You can find the purge script at location RCU_HOME/rcu/integration/soainfra/sql/soa_purge


2. In SQL*Plus, connect to the database AS SYSDBA:

3. Execute the following SQL commands:
                      GRANT EXECUTE ON DBMS_LOCK to dev_soainfra;
                      GRANT CREATE ANY JOB TO dev_soainfra;

4. RCU_HOME/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql


6.  execute below SQL block and description of each variable is given below

    min_creation_date : minimum date when instance was created
    max_creation_date : Maximum date when instance was created
    batch_size :Batch size used to loop the purge. The default value is 20000.
    max_runtime :Expiration at which the purge script exits the loop. The default value is 60. This value is specified in minutes.
    retention_period :Retention period is only used by the BPEL process service engine only (in addition to using the creation time parameter). The default value is null
    purge_partitioned_component  : Users can invoke the same purge to delete partitioned data. The default value is false


DECLARE

   MAX_CREATION_DATE timestamp;
   MIN_CREATION_DATE timestamp;
   batch_size integer;
   max_runtime integer;
   retention_period timestamp;

BEGIN

   MIN_CREATION_DATE := to_timestamp('2011-06-23','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2011-07-03','YYYY-MM-DD');
    max_runtime := 15;
    retention_period := to_timestamp('2011-07-04','YYYY-MM-DD');
   batch_size := 5000;
     soa.delete_instances(
     min_creation_date => MIN_CREATION_DATE,
     max_creation_date => MAX_CREATION_DATE,
     batch_size => batch_size,
     max_runtime => max_runtime,
     retention_period => retention_period,
     purge_partitioned_component => false);
  END;


 Here is very important to note that this script provided is able to delete instances from database schema however it will not free up the memory of that table / tablespace.

For freeing up the memory you can try this option below on tables.

alter table enable row movement.
alter table shrink space;

JBO-27024: Failed to validate a row with key oracle.jbo.Key

Seems that the key you use is a composite key and the first attribute in this key is null, which is why the row cannot be retrieved



JBO-27024: Failed to validate a row with key oracle.jbo.Key usually occurs
1. Not providing value for a mandatory attribute.
2. Incorrect value for an attribute of different data type
3. any of your other validation rules failed on EO.




the validation is failing because the id attribute may not be getting created properly. Because of this it could not validate the row as the id is null. row.validateEntity() will help to identify the error by calling it when you commit the record and check where exactly the error pops up.
4. when the primary key is not based on a sequence and depends on the composite. The solution is to create a surrogate key. If you want to override the db constraint you must have a surrogate key populated programmatic way every time a new row is created. So that the data is unique all the time and proceed with a customized error message for each attribute, make the surrogate key hidden and read only.
if you want to suppress the validation then use skipValidation=true in pageDef or have the immediate set to true.

use the thread.dumpstack(); which will let you know which attribute is updating with nulll value