Raise Application Error In Oracle Apex
Contents |
Topics: Data Types and Constants Example of
Raise_application_error In Oracle
an Error Handling Function ADD_ERROR Procedure Signature 1 raise_application_error vs raise ADD_ERROR Procedure Signature 2 ADD_ERROR Procedure Signature 3 ADD_ERROR Procedure Signature 4 ADD_ERROR
Raise_application_error(-20001
Procedure Signature 5 AUTO_SET_ASSOCIATED_ITEM Procedure EXTRACT_CONSTRAINT_NAME Function GET_ARIA_ERROR_ATTRIBUTES Function GET_FIRST_ORA_ERROR_TEXT Function INIT_ERROR_RESULT Function Data Types and Constants This section describes the oracle raise data types and constants used by the APEX_ERROR package. Constants used for Result Types t_error t_error_result Constants used for Result Types The following constants are used for the API parameter p_display_location and the attribute display_location in the t_error and t_error_result types. c_inline_with_field raise_application_error parameters constant varchar2(40):='INLINE_WITH_FIELD'; c_inline_with_field_and_notif constant varchar2(40):='INLINE_WITH_FIELD_AND_NOTIFICATION'; c_inline_in_notification constant varchar2(40):='INLINE_IN_NOTIFICATION'; c_on_error_page constant varchar2(40):='ON_ERROR_PAGE'; The following constants are used for the API parameter associated_type in the t_error type. c_ass_type_page_item constant varchar2(30):='PAGE_ITEM'; c_ass_type_region constant varchar2(30):='REGION'; c_ass_type_region_column constant varchar2(30):='REGION_COLUMN'; t_error The following type is passed into an error handling function and contains all of the relevant error information. type t_error is record ( message varchar2(32767), /* Displayed error message */ additional_info varchar2(32767), /* Only used for display_location ON_ERROR_PAGE to display additional error information */ display_location varchar2(40), /* Use constants "used for display_location" below */ association_type varchar2(40), /* Use constants "used for asociation_type" below */ page_item_name varchar2(255), /* Associated page item name */ region_id number, /* Associated tabular form region id of the primary application */ column_alias varchar2(255), /* Associat
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem
Raise Application Error In Oracle Triggers
PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeA complete example using RAISE_APPLICATION_ERROR raise application error in sql server : raise_application_error«PL SQL Programming«Oracle PL/SQL TutorialOracle PL/SQL TutorialPL SQL Programmingraise_application_errorSQL> SQL> SQL> create
Raise_application_error Error Number Range
table company( 2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) https://docs.oracle.com/database/121/AEAPI/apex_error.htm 6 ); Table created. SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.'); 1 row created. SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.'); 1 row created. SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.'); 1 row created. http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/AcompleteexampleusingRAISEAPPLICATIONERROR.htm SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.'); 1 row created. SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.'); 1 row created. SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.'); 1 row created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE org_proc 2 (p_flag_in VARCHAR2, 3 p_product_id NUMBER, 4 p_company_id NUMBER, 5 p_company_short_name VARCHAR2, 6 p_company_long_name VARCHAR2) 7 IS 8 v_error_code NUMBER; 9 BEGIN 10 IF (p_flag_in ='I')THEN 11 BEGIN 12 INSERT INTO company VALUES(p_product_id,p_company_id,p_company_short_name,p_company_long_name); 13 EXCEPTION WHEN OTHERS THEN 14 v_error_code :=SQLCODE; 15 IF v_error_code =-1 THEN 16 RAISE_APPLICATION_ERROR(-20000,'Organization '||TO_CHAR(p_company_id)||' already exists.'); 17 ELSIF v_error_code =-2291 THEN 18 RAISE_APPLICATION_ERROR(-20001,'Invalid Hierarchy Code '||TO_CHAR(p_product_id)||' specified.'); 19 END IF; 20 END; 21 ELSIF (p_flag_in ='C')THEN 22 BEGIN
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting http://www.dba-oracle.com/t_raise_application_error.htm PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB http://nuijten.blogspot.com/2012/01/apex-friendlier-exception-message-from.html Don Burleson Blog
RAISE_APPLICATION_ERROR tips Oracle Database Tips by Burleson Consulting March 3, 2015 Question: What does the PL/SQL raise_application_error code do? Answer: The raise_application_error is actually error in a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. Oracle provides the raise_application_error procedure to raise application error allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Below we illustrate the use of the raise_application_error procedure. Using the raise_application_error procedure: DECLARE Balance integer := 24; BEGIN IF (nBalance <= 100) THEN Raise_Application_Error (-20343, 'The balance is too low.');END IF;END; In this example, error number -20343 is raised if the value of nBalance isn't greater than 100, yielding a message that looks like this: ORA-20343: The balance is too low. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000). The final parameter passed to the procedure is a Boolean(true/false) that tells the procedure to add this error to the error stack or replace all errors in the staend users it is not always intuitive that they should navigate to the form page to remove the record. A nicer solution is the one described by Anthony Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a little trashcan on the report page. In this blogpost I will not describe how to create the trashcan functionality in the report, this is already done by Anthony, but about how to handle this: You might not be scared by this message, but your users might be. Probably most of them have no idea what is meant by AJAX call returned server error ORA-02292: integrity constraint (NUIJTEN.SYS_C006303602) violated - child record found for Execute PL/SQL Code. This exception occurs when you create the PL/SQL Code to delete the department like this Instead of handling the exception in the application, I prefer to have actions done in stored procedures - preferably packages. So to remove a department I will write a procedure like this: Here is the code so you don't have to type it in, but can copy and paste it. create or replace procedure remove_dept (p_deptno IN NUMBER) is e_constraint exception; pragma exception_init (e_constraint, -2292); begin delete from dept d where d.deptno = p_deptno; exception when e_constraint then htp.p ('{"error":"Department has Employees"}'); end remove_dept; The key thing in the code is the use of HTP in the exception handler. This will return a JSON object with a user friendly error message. Just replace the original delete statement with a call to the procedure: and when the user tries to remove a department which still has employees, he will see a friendlier message You can try out an example on my demo pages. Geplaatst door Alex Nuijten op 4:43 PM Email ThisBlogThis!Share to TwitterShare to