Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newshosting.com!news-xfer2.atl.newshosting.com!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: C Chang <cschang@maxinter.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to use the exception_init
Date: Sun, 02 Feb 2003 22:24:04 -0500
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <3E3DE0D4.54AE@maxinter.net>
X-Mailer: Mozilla 3.01Gold (Win95; I)
MIME-Version: 1.0
References: <3E3B052E.7A11@maxinter.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@supernews.com
Lines: 124
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:92623
X-Received-Date: Sun, 02 Feb 2003 20:12:29 MST (news.easynews.com)

C Chang wrote:
> 
> I tried out this in a check number function as that of Tom's book (
> one-on-one) first time. I had testesd it in the SQL mode with both
> number and non-number like string (as '360', 'ABC'), it return 1 and 0
> separately.   However, When I call it from a procedure and entered a
> non-number string, which return 0, the calling procedure return an error
> message as "1, User-Defined Exception". Does anyone what it means?
> Anywhere has more detail about such subject.  Thanks.
> 
> C Chang
Hi DA
The error I had came from the following function of a package and the
procedure 

-- check the first character of the string; this belongs to a Package
util
  FUNCTION isFirstCharNumber( p_string   IN   VARCHAR2)
     RETURN NUMBER IS
        x                  NUMBER;
      --  InString_not_a_number   EXCEPTION;
      --  pragma                  EXCEPTION_INIT(InString_not_a_number,
-6502);
     BEGIN
        x := TO_NUMBER(SUBSTR(p_string,1,1));
        RETURN 1;
     EXCEPTION
      --  WHEN InString_not_a_number THEN
      --      RETURN 0;
         WHEN OTHERS THEN
            RETURN 0;
            
   END isFirstCharNumber;

-- separated procedure
CREATE OR REPLACE PROCEDURE send850
               (p_site_id IN sites.site_id%TYPE,
                p_po_id IN po_heads.po_id%TYPE,
	        p_vendor_id IN vendor_accounts.vendor_id%TYPE,
	        p_errFlag OUT NUMBER,
	        p_errMsg  OUT VARCHAR2
				    ) AS
   ... other variables

  v_vendor_file_head   VARCHAR2(50);

BEGIN
   v_errFlag:=0;
   v_errMsg:=NULL;
   v_index:=1;
   v_line_count:=0;
   v_NSN:=NULL;
   
   v_edi.ISA.POPULATE(p_vendor_id,v_errFlag,v_errMsg);
   
   IF v_errFlag <> 0 THEN
      p_errFlag:=1;
      RAISE EXIT_850;
   END IF;
   
   p_errFlag:=0;
   DBMS_OUTPUT.ENABLE(1000);

   SELECT TRANSLATE(TRIM(vendor_id),' .''&','____')
   INTO v_vendor_file_head
   FROM vendor_accounts
   WHERE vendor_id = p_vendor_id;
   
   IF util.isFirstCharNumber(v_vendor_file_head) = 1 THEN
      v_vendor_file_head := 'VENDOR_'|| v_vendor_file_head;
   END IF;
   
   v_fileName:= p_po_id||'.EDI';
   DBMS_OUTPUT.PUT_LINE('Vendor EDI File: ' || v_vendor_file_head || '_'
|| p_po_id||'.EDI' );
   
   v_fileID:= UTL_FILE.FOPEN('E:\IPV_EDI\', v_fileName, 'w',5000);
   ..
   UTL_FILE.FCLOSE(v_fileID);   

EXCEPTION
	WHEN EXIT_850 THEN
		p_errFlag:=v_errFlag;
		p_errMsg:=v_errMsg;
	WHEN OTHERS THEN
	    v_errCode:= SQLCODE;
        v_errText:= SUBSTR(SQLERRM,1,200);
	    p_errFlag:=2;
		p_errMsg:='EXECUTION REJECTED:' || v_errCode || ' ' || v_errText ;
END send_850;

and I run the send850 through the SQL and found out probably from the
UTL_FOPEN function.

SQL> set serveroutput on
SQL> set linesize 200
SQL> var p_flag number;
SQL> var p_msg varchar2(200);
SQL> exec send850('CP','CP17318','PAC', :p_flag,:p_msg);
Vendor EDI File: PAC_CP17318.EDI

PL/SQL procedure successfully completed.

SQL> print p_flag

   P_FLAG
---------
        2

SQL> print p_msg

P_MSG
--------------------------------------------------------------------------
EXECUTION REJECTED:1 User-Defined Exception

Even I removed the block from the "SELECT TRANSLATE .." to the 
   "v_vendor_file_head := 'VENDOR_'|| v_vendor_file_head;
   END IF;",  I still got the same error.  The rest of the Procedure
below the 
   "v_fileID:= UTL_FILE.FOPEN(.." were old codes and it still work. 
That's why I posted another question yesterday.  Anyone can help?
Thanks.

C Chang