Re: ORA-06573 ???

You nailed it Don, I saw the same thing.

You cannot use DBMS_OUTPUT in a function called by a SQL statement, regardless of pragma statements.

I've tried it before, couldn't do it. This was on pre 8i versions of Oracle however. The rules have changed on 8i, it may work.

Jared

On Tuesday 08 May 2001 08:00, Don Jerman wrote:
> 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,1
> >1,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).

Content-Type: text/x-vcard; charset="us-ascii"; name="djerman.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Don Jerman
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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).