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

First off, you may have an easier time using the exec function in conjunction with dbms_output.put_line. If this is too much typing, put it into a SQL script and @ the file.

SQL> set serveroutput on
SQL> exec dbms_output.put_line(app_format.app_soundex('TOUCHSTONE'))

Next, try putting some dbms_output.put_line statements into your code to see what's going on. It's a crude approach, but it'll work. I've found PL/SQL very difficult to debug. I've heard of some commercial debuggers for it - if you can get a hold of one of them that may help.

Good luck,
Peter

H. John C. Hopkins wrote:
>
> 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.
> >
> >
> >
Received on Mon Mar 09 1998 - 00:00:00 CST