Archive for the ‘Oracle’

  • 0
    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 || ' HOURS...
    by Nauman at November 18th, 2009 at 09:11 am
  • 0
    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...
    by Nauman at November 5th, 2009 at 06:11 am
  • 0
    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 begin execute immediate...
    by Nauman at November 5th, 2009 at 12:11 am
  • 1
    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 oracle_username,object_id,session_id from v$locked_object; ORACLE_USERNAME...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 a custom function: CREATE [OR REPLACE] FUNCTION function_name [...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 function: LENGTH(TRANSLATE(...
    by Nauman at November 5th, 2009 at 12:11 am