Re: ORA-06573 ???

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_at_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_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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Sawmiller
> INET: sawmillert_at_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_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).
>
> -----------------------------------------------------------
> 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_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).