RE: PL/SQL changes (8.1.6 -> 8.1.7)

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 07 Sep 2001 10:07:54 -0700
Message-ID: <F001.00386EDE.20010907102036@fatcity.com>

Bill,

the problem is that your inline view is selecting '0' and returning a string, while the nvl function is trying to convert this string a number.

the result is that you are attempting to perform a to_char function on a char variable - doesn't work that way.

either remove the single quotes from the inline view, DECLARE

        CURSOR c1 IS
        SELECT TO_CHAR(NVL(a.foo,'0'),'999')
        FROM
        (SELECT 0 foo FROM dual) a;
BEGIN
        NULL;

END; or
convert a.foo to a number via the to_number function:

DECLARE

        CURSOR c1 IS
        SELECT TO_CHAR(NVL(TO_NUMBER(a.foo),'0'),'999')
        FROM
        (SELECT '0' foo FROM dual) a;
BEGIN
        NULL;

END; hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 07, 2001 1:13 PM To: Multiple recipients of list ORACLE-L

quotes around the 0? maybe an artifact of implicit function overloading?

-----Original Message-----
Sent: Friday, September 07, 2001 12:21 PM To: Multiple recipients of list ORACLE-L

Help! Can anyone throw any light on the following:

I run the following:

declare

        cursor c1 is
        select to_char(nvl(a.foo,0),'999')
        from
        (select '0' foo from dual) a;
begin
        null;

end;

On Oracle 8.1.6 (Solaris and Linux platforms): "Statement Processed"

On Oracle 8.1.7 (Solaris and Linux platforms): "ORA-06550: line 3, column 9:
PLS-00307: too many declarations of 'TO_CHAR' match this call ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored"

Additionally, if I remove the NVL(), then it also fails, as above, on 8.1.6. I'd be grateful for any pointers.

Thanks
- Bill.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bill Buchan
INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mohan, Ross
INET: MohanR_at_STARS-SMI.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------