From time to time Oracle seems to be limited in aggregate functions. However Oracle delivers possibility to create own aggregate function using built-in framework ODCIAggregate routines

Each of the four ODCIAggregate routines required to define a user-defined aggregate function codifies one of the internal operations that any aggregate function performs, namely:

• Initialize – ODCIAggregateInitialize – initialize the computation of the user-defined aggregate
• Iterate – ODCIAggregateIterate – calculates aggregations
• Merge – ODCIAggregateMerge – combines aggregation iterations
• Terminate – ODCIAggregateTerminate – retuns aggregation value

Continue reading →

If you are developer no doubt you have situation that you need to run your code in parallel. I’m not talking about running DML, DDL in parallel – read following article to understand how to do it effectively – Effective parallel execution for DML, DDL in Oracle.

I’m talking about running many independent pieces of your code – SQL, PL/SQL. Everybody knows it can be done using DBMS_SCHEDULER package, however it requires a lot of steps to be done. Define programs, jobs, chains, schedules.

To simplify this process Oracle has introduced very nice package that can help you do it very quickly DBMS_PARALLEL_EXECUTE.

Following picture describes general concept of this package

Continue reading →

This short article will focus on simple hints/list to help you make decision how to manipulate large data in Oracle for both DML and DDL operations using parallel option.

Continue reading →

If you want to load data in direct mode into a table you should set all dependent indexes in UNUSABLE state. Session parameter SKIP_UNUSABLE_INDEXES set to TRUE enables to ignore indexes with status UNUSABLE during direct load.

However If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

Continue reading →

In case you want to collect trace statistics you can do it in many ways in Oracle. One of the most popular method is by setting parameter event with value sql_trace on session level.

alter session set events 'sql_trace level <trace_level>'

Continue reading →

This article shows how to load text or binary files into Oracle database table column using sqlldr tool. It’s very useful and common practices.

Continue reading →

If you are working as developer/DBA probably you are quite often asked to migrate some rows from one database to another – usually people reference to the rows as “metadata” rows. Rows that driving your business !

If you need to prepare such rows for migrations there are a lot of methods to do it

• just copy the rows using database link from one database to another
• export/import the rows using tools like expdp/impdp
• prepare manually scripts with INSERT statements

I would like to focus on the last method. It’s very popular to keep metadata in some files as number of INSERTs for backup/migrations/versioning etc.

I decided to write my own Oracle PL/SQL function to do that. It generates PL/SQL code which can be used to load exported rows later. This function should be used only for small number of rows.

Continue reading →

This article presents how to execute DML operations on complex views using INSTEAD OF triggers that otherwise could not be done.

Update/Insert/Delete via view is possible in Oracle.

create table test_tbl
(
  id  number,
  id1 number,
  id2 number
);

create view test_vw
as
select * from test_tbl;

INSERT, UPDATE, DELETE

insert into test_vw values(1,1,1);
insert into test_vw values(2,2,2);
insert into test_vw values(3,3,3);

commit;

update test_vw
set id2=4
where id1=3;

commit;

delete from test_vw
where id=2;

commit;

select * from test_vw;

        ID        ID1        ID2
---------- ---------- ----------
         1          1          1 
         3          3          4

but with many exceptions

The view must not contain any of the following constructs:

• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• A collection expression in a SELECT list
• A subquery in a SELECT list
• A subquery designated WITH READ ONLY

and many many others – more you can read in Oracle documentation. If view violates one of above rule then it’s called complex view.

Continue reading →

Sometimes creation of an index can take a lot of time. It’s especially true for warehouses. In Oracle there is nice old trick to create definition of index in dictionary without allocating physical space. It can help you to quickly determine what columns should be indexed.

Virtual index – it’s dictionary definition of index which is not allocating physical space .

Continue reading →

Very often developers try to add primary constraints on a table and they can’t do it because of duplicates in data. So question is how to quickly find the duplicates without effort.

Let’s prepare test data

create table test_tbl
(id number not null);

begin
  for i in 1..10
  loop
    insert into test_tbl values(i);
  end loop;

  insert into test_tbl values(10);

  commit;
end;
/

select * from test_tbl order by id;

        ID
----------
         1 
         2 
         3 
         4 
         5 
         6 
         7 
         8 
         9 
        10 
        10

so adding primary key raises exception

alter table test_tbl add constraint test_tbl_pk primary key(id);

SQL Error: ORA-02437: cannot validate (TOMASZ.TEST_TBL_PK) 
           - primary key violated
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate 
           values or null values.
*Action:   remove the duplicates and null values 
           before enabling a primary key.

Continue reading →