All Entries in the "Oracle" Category

Calculating difference of dates in Oracle

How to calculate difference between two dates in oracle, as subtraction of dates returns numeric value as difference. With some slight changes/enhancements we can track back the subtraction values to some readable date formats:
Here are some examples, these can also be amended for user convenience:
SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 [...]

ORA-38029 : object statistics are locked Recently

This error comes when analyzing tables in oracle. Basic issue in this error is  when you import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:
select table_name, stattype_locked
from dba_tab_statistics
where owner = ‘MBS’ and stattype_locked is not null;

Then how to [...]

Creating Table dynamically in Oracle Procedure

How can we create a table inside an Oracle procedure.
"CREATE TABLE tableName AS". We cannot execute this statement in procedures because upon creation of procedure it would give syntax error.
We can implement this with the help of EXECUTE IMMEDIATE command and then dynamically create table.
create or replace procedure Proc1 as [...]

Killing Locked Sessions in Oracle

Many a times user sessions are locked in some DML/DDL operation, than how to unlock those sessions because a new step or operation could not be done on the specific object that is locked.
Some really easy steps to unlock an object or kill a session in oracle are as follows:
To view locked sessions in oracle:

select [...]

Creating Custom Functions in Oracle

Here it is a simple procedure to create and call a custom function in Oracle. A custom function is a simple PL/SQL subprogram that is used to calculate a value. It creation syntax is almost same as of procedure except it has a RETURN clause that is used to return the computed value.
Syntax of creating [...]

How to check Numeric value in Oracle

Currently i could not find any suitable function for checking numeric value on some column. There are many workarounds to achieve this functionality, either cast the column value in Numeric and check, but this can create an exception if not properly coded.
Another workaround for this problem is by using the combination of LENGTH and TRANSLATE [...]