C Chang wrote:
>
> 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
I rebooted the DB engine, it worked now. Must be some function crash.
C Chang
Received on Mon Feb 03 2003 - 21:59:50 CST
