Re: PL/SQL: stored function called from SQL hangs... sometimes

I should add that after successfully returning a value from a select on dual, I then get:

SQLWKS> select app_format.app_soundex('TOUCHSTONE') from dual; APP_FORMAT.APP_SOUNDEX('TOUCHSTONE')

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "BMT.BMT_FORMAT", line 3
ORA-06512: at line 1

Then I select a different function:

SQLWKS> select substr('TOUCHSTONE',1,3) from dual; SUB

---
TOU

1 row selected.

And then, I try the soundex again:

SQLWKS> select app_format.app_soundex('TOUCHSTONE') from dual;
APP_FORMAT.APP_SOUNDEX('TOUCHSTONE')
----------------------------------------------------------------------------
----
T235

WHAT THE HECK IS GOING ON?!

-John



H. John C. Hopkins wrote in message <6doveo$56f_at_no-names.nerdc.ufl.edu>...


>Hello.

>

>I could use a tip here.  I'm porting a VB soundex function (yes, I know

>Oracle has one, but this one's a little better) to PL/SQL.  The function is

>called app_soundex, and I've put it in a package called app_format.

>

>The spec is:

>

>  FUNCTION app_soundex (str_in IN VARCHAR2) RETURN VARCHAR2

>

>I need to use this function in SQL.  So, I try a simple call:

>

>   SELECT app_format.app_soundex('TOUCHSTONE') FROM dual;

>

>and I get 'T235' returned.  Good.  Then I try

>

>   SELECT app_format.app_soundex(last_name) FROM customers;

>

>and Oracle hangs up.  I have to kill the session!  I must be missing

>something...

>

>Many thanks for your help.  Please post in newsgroup, or reply to

>john_at_hpe.ufl.edu.

>

>

>