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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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).
begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Database Management Service,Information Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Database Administrator adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard
