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.
>>> [EMAIL PROTECTED] 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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
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).
