Home » Server Options » Streams & AQ » ORA-21000: error number argument to raise_application_error of -1722 is out of range (Oracle 10.2.0)
ORA-21000: error number argument to raise_application_error of -1722 is out of range [message #309551] Thu, 27 March 2008 17:59 Go to next message
basirana
Messages: 25
Registered: July 2006
Junior Member
I am trying to write conflict resolution procedure. When I try to raise a conflict it is not handle properly. I went o dba_apply_error and see below message.

ORA-21000: error number argument to raise_application_error of -1722 is out of range
ORA-06512: at "TBL_COUNTRIES_PKG", line 106
ORA-06512: at line 1
ORA-01403: no data found

CREATE OR REPLACE PACKAGE tbl_countries_pkg 
AS
 TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 PROCEDURE  update_countries( 
   message            IN ANYDATA, error_stack_depth  IN NUMBER,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY, error_messages     IN EMSG_ARRAY);
 END tbl_countries_pkg ;
/

CREATE OR REPLACE PACKAGE BODY tbl_countries_pkg AS

 PROCEDURE update_countries( 
  message       IN ANYDATA, error_stack_depth  IN NUMBER,
  error_numbers IN DBMS_UTILITY.NUMBER_ARRAY, error_messages IN EMSG_ARRAY )
  IS

  ad              ANYDATA;
  lcr             SYS.LCR$_ROW_RECORD;
  ret             PLS_INTEGER;
  key             COUNTRIES.COUNTRY%TYPE;
  new_time_stamp  TIMESTAMP;
  db_row          COUNTRIES%ROWTYPE;
  v_update        varchar2(2) := 'Y';
  key_column_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(key_column_missing, -20101);


 begin
 ----print old and new values of tbl_prder---
 --- util_pkg.print_anydata(message);
 ----print old and new values of tbl_prder---

  -- access the LCR.
  ret := message.getobject(lcr);

/*
  ad := lcr.get_value('new','last_updated','n');
  if( ad is not null) then -- error if null
    new_time_stamp := ad.accesstimestamp();
  else  
    RAISE_APPLICATION_ERROR(-20101, 'missing last_updated value');
  end if;
*/

    ad := lcr.get_value('old','modified_by');

  if( ad is not null) then
    key := ad.accessVarchar2();
    begin
        SELECT * into db_row from COUNTRIES where COUNTRY = key;
        -- error if row not found
        EXCEPTION
        WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
            RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
    end;

  if( v_update = 'Y' ) then

    --COUNTRY
    ad := lcr.get_value('old','country');
    if (ad is not null) then
            lcr.set_value('old','country',ANYDATA.ConvertVarChar2(db_row.country));
    end if;    

    --DT_CREATED
    ad := lcr.get_value('old','dt_created');
    if (ad is not null) then
            lcr.set_value('old','dt_created',ANYDATA.ConvertDate(db_row.dt_created));
    end if;    

    --DT_MODIFIED
    ad := lcr.get_value('old','dt_modified');
    if (ad is not null) then
            lcr.set_value('old','dt_modified',ANYDATA.ConvertDate(db_row.dt_modified));
    end if;

    --MODIFIED_BY
    ad := lcr.get_value('old','modified_by');
    if (ad is not null) then
            lcr.set_value('old','modified_by',ANYDATA.ConvertVarchar2(db_row.modified_by));
    end if;

    --DIAL_COUNTRY_CODE
    ad := lcr.get_value('old','dial_country_code');
    if (ad is not null) then
       lcr.set_value('old','dial_country_code',ANYDATA.ConvertVarchar2(db_row.dial_country_code));
    end if;
    
    --SHOW_DISQUALIFIED_INV_YN
    ad := lcr.get_value('old','show_disqualified_inv_yn');
    if (ad is not null) then
            lcr.set_value('old','show_disqualified_inv_yn',ANYDATA.ConvertVarchar2(db_row.show_disqualified_inv_yn));
    end if;

    --CP_COUNTRY_CODE
    ad := lcr.get_value('old','cp_country_code');
    if (ad is not null) then
            lcr.set_value('old','cp_country_code',ANYDATA.ConvertVarchar2(db_row.cp_country_code));
    end if;

    --NLS_SORT
    ad := lcr.get_value('old','nls_sort');
    if (ad is not null) then
            lcr.set_value('old','nls_sort',ANYDATA.ConvertVarchar2(db_row.nls_sort));
    end if;

    --ISO_COUNTRY_CODE
    ad := lcr.get_value('old','iso_country_code');
    if (ad is not null) then
            lcr.set_value('old','iso_country_code',ANYDATA.ConvertVarchar2(db_row.iso_country_code));
    end if;

      lcr.execute(true);
    end if;

  end if;
  EXCEPTION
    WHEN OTHERS THEN  -- catches all error
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
 end update_countries;
end tbl_countries_pkg;
/

Re: ORA-21000: error number argument to raise_application_error of -1722 is out of range [message #309561 is a reply to message #309551] Thu, 27 March 2008 21:51 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
EXCEPTION
    WHEN OTHERS THEN  
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);


First thing handling Exception with OTHERS is not a good practice.

Second Thing is that RAISE_APPLICATION_ERROR Syntax is not correct.

RAISE_APPLICATION_ERROR allows the application to raise application errors rather than just Oracle errors.Error numbers are defined between -20,000 and -20,999.

But You Are Using SQLCODE in It.

And Getting
ORA-21000: error number argument to raise_application_error of -1722 is out of range


Third Thing is that whenever you write RAISE_APPLICATION_ERROR
it gives control back to calling environment and you will see that your procedure is not successfully executed.

Re: ORA-21000: error number argument to raise_application_error of -1722 is out of range [message #309567 is a reply to message #309561] Thu, 27 March 2008 22:59 Go to previous messageGo to next message
basirana
Messages: 25
Registered: July 2006
Junior Member
How can I change above code to handle conflicts in streams. because I am getting error in dba_apply_error. And package compiles without any error.
Re: ORA-21000: error number argument to raise_application_error of -1722 is out of range [message #309573 is a reply to message #309567] Thu, 27 March 2008 23:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
It's A Run Time Error.It has nothing to do with compilation.

It has nothing to do wih DBA_APPLY_ERROR.

DBA_APPLY_ERROR displays information about error transactions generated by all apply processes in the database.

When u use RAISE_APPLICATION_ERROR and error occurs procedure immediately terminates and return control to calling environment.

Instead of:-

RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);


Try This:-

  RAISE_APPLICATION_ERROR(<-20,000 To -20,999>, SQLERRM);
  Select any number in Between this range Like
  RAISE_APPLICATION_ERROR(-20,000,SQLERRM);


But you will see the error in DBA_APPLY_ERROR becuase if again some runtime error occurs program terminates.

Quote:

If you don't wan't to Raise Error To calling environment and don't wan't to see it in DBA_APPLY_ERROR then remove RAISE_APPLICATION_ERROR and Make some error routine to handle
the error that store error information in database.

Re: ORA-21000: error number argument to raise_application_error of -1722 is out of range [message #309577 is a reply to message #309551] Fri, 28 March 2008 00:16 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
I agree that this is run-time error. However I would not take this approach. This will lead just to hiding the actual error code to the application, so harder analyzing what really is wrong.
  EXCEPTION
    WHEN OTHERS THEN  -- catches all error
    RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);

If you do not want to do anything useful in this EXCEPTION block, just get rid of it.

If you want to do some processing (eg. error logging), do it there; re-raising the error can be done by RAISE Statement without parameters. However you will lose the line number where the error occured, so, you will again need extra effort to analyze the error (eg. some kind of logging in the procedure).

In your case: would not it be better removing the EXCEPTION block and get ORA-01722 with the line where it happened?
Previous Topic: Advanced Queueing: Asynchronous Notification callback not working for type AQ$_JMS_TEXT_MESSAGE.
Next Topic: Best Queue Building
Goto Forum:
  


Current Time: Mon Sep 22 15:30:28 CDT 2014

Total time taken to generate the page: 0.06018 seconds