Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

RAISE_APPLICATION_ERROR

Rajan SwMar 4 2014 — edited Mar 4 2014

Hi Guys,

Can anyone tell me the values of keep_errors in RAISE_APPLICATION_ERROR like as per the documentation the syntax of the raise_application_error is RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE,KEEP_ERRORS) where the error no is the number between -20000 and -20999 and the error message is the user defined message and keep_errors is the boolean value if it is true then the error will be added to the error stack other wise it will purge the stack and add the recent error.

however when I am using this i am getting the following error,Please suggest if any body has any better solutions to use this

Declare

 

 

  begin

 

    If 200 > 100 Then

      Raise_Application_Error (-20001,'The if condtion is fine','TRUE');

    End If;

END ;

Below is the error I am getting

Error starting at line 1 in command:

Declare

 

 

  begin

 

    If 200 > 100 Then

      Raise_Application_Error (-20001,'The if condtion is fine','TRUE');

    End If;

END ;

Error report:

ORA-06550: line 7, column 7:

PLS-00306: wrong number or types of arguments in call to 'RAISE_APPLICATION_ERROR'

ORA-06550: line 7, column 7:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

Regards,

Ranjan

This post has been answered by Hoek on Mar 4 2014
Jump to Answer

Comments

Chanchal Wankhade
Hi,


Let us assume the database character set is UTF-8, which I believe is the default in recent version of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters, no matter how many bytes it takes to store each one. I believe that in Oracle 10g, 3 bytes per character were used.


Also find good links.

http://stackoverflow.com/questions/81448/difference-between-byte-and-char-in-column-datatypes
2379822
theoa
Declare your variables as table.column%TYPE.
Even if the type (length) of the database column changes, it will still fit in the variable.
BluShadow
Chanchal Wankhade wrote:
Hi,


Let us assume the database character set is UTF-8, which I believe is the default in recent version of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters, no matter how many bytes it takes to store each one. I believe that in Oracle 10g, 3 bytes per character were used.
It could be up to 4 bytes depending on the character set and the character being stored.

Also need to consider that if using a multi byte character set, then the limit on varchar2 columns on the database is still 4000 bytes and not 4000 characters, so if multi-byte characters get stored, the number of characters that one can store in that column can be as few as 1000. It can cause confusion when some people think the limit is 4000 characters and then find they have trouble storing that many because of multi-byte characters. (Same principle applies to the 32767 byte limit in PL/SQL varchar2 variables)
Umesh P
All the three options
1) varchar2(100 char)
2) varchar2(100 bytes)
3) varchar2(100)

are equally supported and recommended. Using any of them will result in insertion of 100 characters only.
BluShadow
992981 wrote:
All the three options
1) varchar2(100 char)
2) varchar2(100 bytes)
3) varchar2(100)

are equally supported and recommended. Using any of them will result in insertion of 100 characters only.
No it won't. Clearly you haven't read previous replies.

varchar2(100 bytes) will support at most 100 characters, but could be as few as 25 characters if it is populated with multi byte characters (each could take up to 4 bytes).
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 1 2014
Added on Mar 4 2014
12 comments
1,422 views