My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • December 2009
    M T W T F S S
        May »
     123456
    78910111213
    14151617181920
    21222324252627
    28293031  
  • Blogs I Follow

Archive for December 9th, 2009

Not null constraint :–ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Posted by Sriram Sanka on December 9, 2009


SQL> sho user
USER is "SCOTT"
SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2
2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type;
5 begin
6 select search_condition into l_search_condition
7 from user_constraints
8 where constraint_name = p_cons_name;
9
10 return l_search_condition;
11 end;
12 /

Function created.

SQL> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';

CONSTRAINT_NAME
------------------------------
SYS_C006082
SYS_C006081
SYS_C006080
SYS_C005558
SYS_C005696
SYS_C005695

6 rows selected.

SQL>

Source:- Asktom`s get_search_condition function.

Posted in Uncategorized | 1 Comment »

Reverse function in oracle

Posted by Sriram Sanka on December 9, 2009


SQL> select reverse('sriram oracle DBA') from dual;

REVERSE('SRIRAMOR
-----------------
ABD elcaro marirs

As this reverse funcction is undocumented we can achive the same using a procedure........

SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

Procedure created.

SQL> exec rev('sriram oracle DBA');
ABD elcaro marirs

PL/SQL procedure successfully completed.

SQL>

sriram.

Source : http://www.oracle.com/code tips

Posted in Functions In oracle | Leave a Comment »

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

%d bloggers like this: