Tags: database, instances, len, machines, mysql, oracle, recognize, sql

len() and length()

On Database » Oracle

1,693 words with 4 Comments; publish: Mon, 11 Feb 2008 11:43:00 GMT; (25046.88, « »)

I have two instances on two machines. I found one of them support both len() and length() while another one only recognize length(). Both instances are 9.2.0.1.0.

Any one knows how this happened? I can't see any difference between two instances.

Thanks

All Comments

Leave a comment...

  • 4 Comments
    • Some one could have created a function 'len' on one of your servers.

      SQL> select len('123'),length('123') from dual;
      select len('123'),length('123') from dual *ERROR at line 1:ORA-00904: "LEN": invalid identifier  SQL> create function len(p_input_in varchar2) 2 return number as 3 v_number number;
       4 begin 5 select length(p_input_in) into v_number 6 from dual;
       7 return v_number;
       8 end;
       9 /  Function created. SQL> select len('123'), length('123') from dual;
       LEN('123') LENGTH('123')-- -- 3 3
      #1; Fri, 22 Feb 2008 16:25:00 GMT
    • I just checked, no such function exists on that instance. Actually, one instance was just imported from another one.

      Any other factors can make it happen?

      Thanks

      #2; Fri, 22 Feb 2008 16:26:00 GMT
    • Could you run the following query and post the output?

      select * from dba_objectswhere object_name = 'LEN';
      #3; Fri, 22 Feb 2008 16:27:00 GMT
    • thank you for the help, I found out the issue, another instance is using a Synonym pointing to a len() function under a different schema.

      Sorry for any confusion.

      #4; Fri, 22 Feb 2008 16:28:00 GMT