From stefan.jahnke@d2vodafone.de Tue, 08 May 2001 06:31:36 -0700 From: Stefan Jahnke Date: Tue, 08 May 2001 06:31:36 -0700 Subject: Re: ORA-06573 ??? Message-ID: MIME-Version: 1.0 Content-Type: text/plain Tim Sawmiller schrieb: > > oerr ora 6573 > 06573, 00000, "Function %s modifies package state, cannot be used here" > // *Cause: There are two possible causes for this message: > // * A SQL statement references a packaged, PL/SQL function that > // does not contain a pragma containing the 'Write no Package > // State' (WNPS). > // * A SQL statement references a stand-alone, PL/SQL function > // that modifies a package state. A stand-alone, PL/SQL function > // referenced by a SQL statement cannot modify a package state. > // *Action: If the function is a packaged, PL/SQL function: Recreate > // the function and include a pragma containing the 'Write no > // Package State' (WNPS). > // If the function is a stand-alone, PL/SQL function: Delete > // the function from the SQL statement. > > >>> stefan.jahnke@d2vodafone.de 05/08/01 07:15AM >>> > 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@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@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: Tim Sawmiller > INET: sawmillert@state.mi.us > > 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@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). > > ----------------------------------------------------------- > This Mail has been checked for Viruses > Attention: Encrypted Mails can NOT be checked ! > > *** > > Diese Mail wurde auf Viren ueberprueft > Hinweis: Verschluesselte Mails koennen NICHT geprueft werden! > ------------------------------------------------------------ Hi, I already read the documentation ;). I just don't really get what the problem is and why it is caused. Any hints ? -- Regards, Stefan Jahnke BOV AG @:D2 Vodafone, Abt.: FIBM -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: stefan.jahnke@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@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).