Re: ORA-06573 ???

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Tue, 08 May 2001 06:13:41 -0700
Message-ID: <F001.002FBEC6.20010508060527@fatcity.com>

Here is what Oracle says:
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.

HTH,
Ruth
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 08, 2001 7:15 AM

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: Ruth Gramolini
  INET: rgramolini_at_tax.state.vt.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).

Received on Tue May 08 2001 - 08:13:41 CDT