Re: ORA-06573 ???

May be naive, but it seems to me that

DBMS_OUTPUT.ENABLE(64000); must be altering the state of the DBMS_OUTPUT package -- and it doesn't seem to be needed by the funciton -- why not try without it?

Stefan Jahnke wrote:

> Hi,
>
> I get this error if I give a (standalone) function in a where clause:
>
> ORA-06573: Function WSNADDR modifies package state, cannot be used here
>
> What exactly does it mean and how can I workaround it ? The statement I
> tried to issue looks like this:
>
> select rtrim(substr(wsnaddr(env_wsnaddr),45,3)) IP, db_instance_node
> MACHINE
> from environment e, dbinstancenode dn
> where
> rtrim(substr(wsnaddr(env_wsnaddr),45,3))=rtrim(substr(db_inst_node_desc,11,3));
> * -> here's the problem.
>
> WSNADDR is a self-defined function that looks like this:
>
> CREATE OR REPLACE FUNCTION WSNADDR (v_WSNAddress IN VARCHAR2) RETURN
> VARCHAR2 IS
> v_Port VARCHAR2(8);
> v_IPAddr VARCHAR2(8);
> v_IPDecode VARCHAR2(16);
> v_IPPart VARCHAR2(3);
> v_DecodeWSN VARCHAR2(60);
>
> BEGIN
> DBMS_OUTPUT.ENABLE(64000);
> v_Port := SUBSTR(v_WSNAddress,3,8);
> v_IPAddr := SUBSTR(v_WSNAddress,11,8);
> v_IPDecode := '';
> FOR v_Index IN 0 .. 3 LOOP
> v_IPPart := TO_CHAR(HEXTONUMBER(SUBSTR(v_IPAddr,2*v_Index+1,2)));
> IF (v_Index < 3) THEN
> v_IPDecode := v_IPDecode || v_IPPart || '.';
> ELSE
> v_IPDecode := v_IPDecode || v_IPPart;
> END IF;
> END LOOP;
> v_DecodeWSN := 'Port: ' || v_Port || ' 0xIP: ' || v_IPAddr || ' IP: '
> || v_IPDecode;
> RETURN v_DecodeWSN;
> END WSNADDR;
> /
>
> The above used function HEXTONUMBER just converts a hex into a number
> (what a surprise ;).
> The purpose of this function is to turn Tuxedo WSN addresses into a
> readable format. WSN addresses look like this:
>
> 0x000285ca8b07ca49
>
> They contain the IP and the port where the service runs. The function's
> output looks like this:
>
> Port: 000285ca 0xIP: 8b07ca49 IP: 139.7.202.73
>
> Any ideas ?
>
> --
> Regards,
> Stefan Jahnke
> BOV AG
> @:D2 Vodafone, Abt.: FIBM
> AMS-Gebäude: E6 R08
> Tel.: 0211/533-4893
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stefan Jahnke
> INET: stefan.jahnke_at_d2vodafone.de
>
> 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).
Received on Tue May 08 2001 - 09:28:38 CDT