Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01428: argument '0' is out of range

happy10319Nov 16 2010 — edited Nov 16 2010
Hi,
in 10g R2 , I created (code found in David Kurtz article) a function as follows but when use it I receive an error :
SQL> CREATE OR REPLACE FUNCTION h2i (p_hash_value NUMBER) RETURN VARCHAR2 IS
  2  l_output VARCHAR2(10) := '';
  3  BEGIN
  4  FOR i IN (
  5  SELECT substr('0123456789abcdfghjkmnpqrstuvwxyz',1+floor(mod(p_hash_value/(POWER(32,LEVEL-1)),32)),1) sqlidchar
  6  FROM dual CONNECT BY LEVEL <= LN(p_hash_value)/LN(32) ORDER BY LEVEL DESC
  7  ) LOOP
  8  l_output := l_output || i.sqlidchar;
  9  END LOOP;
 10  RETURN l_output;
 11  END;
 12  /

Function created.

SQL> select SQL_PLAN_HASH_VALUE, sql_id ,h2i(SQL_PLAN_HASH_VALUE) from v$active_session_history;
select SQL_PLAN_HASH_VALUE, sql_id ,h2i(SQL_PLAN_HASH_VALUE) from v$active_session_history
                                    *
ERROR at line 1:
ORA-01428: argument '0' is out of range
ORA-06512: at "SYS.H2I", line 4
ANy idea ? Any help ,
Thank you.
This post has been answered by MichaelS on Nov 16 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 14 2010
Added on Nov 16 2010
9 comments
7,579 views