RE: weird problem calling a fuction

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 1 Feb 2005 11:27:19 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A972BE@exchsen0a1ma>

Mike,

Just for grins, try creating this function in another schema - something *not* named "query". I'm wondering of this is a reserved word.

I tried the same function in my database (9,2.0.3) and it worked fine.

Tom

-----Original Message-----
From: Michael McMullen [mailto:ganstadba_at_hotmail.com] Sent: Tuesday, February 01, 2005 11:03 AM To: oracle-l_at_freelists.org
Subject: weird problem calling a fuction

9.2.0.3
Solaris

Users called me up saying their packages/procedures no longer work. Of course, they've been working for years. I've whittled it down to a test case below
Note I've logged in as the query user created the function and then called it sucessfully from dual. When I reference the function which the query user owns and I'm also logged in as then it fails.

CREATE OR REPLACE FUNCTION mike_test

         (
                sInputString IN varchar2
        ) return varchar2 IS
        BEGIN
                return replace(sInputString, '''', '^');
        END mike_test;

/
bash-2.03$ sqlplus

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Feb 1 11:18:51 2005

Enter user-name: query
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> drop function mike_test;

Function dropped.

SQL> @mike

Function created.

SQL> select mike_test('^MIKE^') from dual;

MIKE_TEST('^MIKE^')

^MIKE^ SQL> select query.mike_test('^MIKE^') from dual; select query.mike_test('^MIKE^') from dual

*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-302: component 'MIKE_TEST' must be declared

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 01 2005 - 11:39:47 CST