Re: How to use the exception

From: C Chang <cschang_at_maxinter.net>
Date: Sun, 02 Feb 2003 22:24:04 -0500
Message-ID: <3E3DE0D4.54AE@maxinter.net>

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

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 Received on Sun Feb 02 2003 - 21:24:04 CST

Re: How to use the exception_init