ORA-06575: Package or function IS_NUMBER is in an invalid state

From: David Macaskill <david.e.macaskill_at_honeywell.com>
Date: 4 Nov 2003 14:34:07 -0800
Message-ID: <55a4c938.0311041434.1414dd7e@posting.google.com>

Hi,

I'm linking Oracle to SQL Server (i.e. sp_addlinkserver). I have a script which I run from inside a VB6 app (via ADO) to create a function and some views in Oracle, but when I run the script, and in particular, create a function, the function is left in an invalid state. Most of my views get created - it's the ones that use the function that are a problem.

So what am I doing wrong? Here's a snippet of the script:

---

create or replace function is_number (text_in varchar2) return varchar2 as
 dummy number;
begin
 if text_in is null then

         return 'N';
 else

 	dummy := to_number(text_in);
 	return 'Y';

 end if;
exception
 when others then
   return 'N';
end;
/

create or replace view AEA_Parameter as

   select variable_id, prio_param_name, prio_param_value_id, tp_param_name, tp_param_value

   from vt_parameter_limit prm
   where (tp_param_name is null or is_number(tp_param_value) = 'Y') and variable_id in (select TagID from AEA_Tag);

---

I know its the function because when I run the script in VB I'm executing it command at a time - I've tried running the whole script through but that doesn't seem to help (actually I think I blow the buffer size).

There are other views created in the file; If I query them from QueryAnalyzer, I get rows. If I query AEA_Parameter - I get OLE/DB unspecified error.

Any ideas?

Cheers

DEM Received on Tue Nov 04 2003 - 16:34:07 CST