Ajax Call Returned Server Error Ora-01403
Contents |
end users it is not always intuitive that they should navigate to the form page to ora-01403 no data found in oracle remove the record. A nicer solution is the one described by Anthony ora-01403 no data found in package Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a
Ora 01403 No Data Found Apex
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 FacebookShare to Pinterest Labels: APEX 19 comments: yavuzMay 28, 2012 at 9:10 PMGreat exa
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-01403: no data found tips Oracle Error Tips by Burleson Consulting Oracle docs note this about ORA-01403: ORA-01403 no data found http://nuijten.blogspot.com/2012/01/apex-friendlier-exception-message-from.html Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query. Action: Terminate processing for the SELECT statement Many people experience ORA-01403 in association with the SELECT INTO clause. SELECT INTO clauses http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm are standard SQL queries which pull a row or set of columns from a database, and put the retrieved data into variables which have been predefined. If the SELECT INTO statement doesn't return at least on e row, ORA-01403 is thrown. Here is an example: SQL> declare 2 v_authName author.author_last_name%type; 3 begin 4 select 5 author_last_name into v_authName 6 from 7 author 8 where 9 author_state = 'FL'; 10 dbms_output.put_line('Name: '||v_authName); 11 end; 12 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 Because the query is looking to retrieve authors in Florida and there are none, the ORA-01403 error is thrown. To avoid ORA-01403, the PL/SQL has to contain exceptions , otherwise the query will offer to values to the defined variable. If the exceptions are not defined properly, you will encounter ORA-01403, and your situation may worsen each time you attempt to acccase of ORA-01403 no data found exception Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.Even http://oraclequirks.blogspot.com/2010/12/strange-old-case-of-ora-01403-no-data.html if I found out later that i am only three years late in discovering the situation I am going to describe, when i saw the results i was really puzzled.I don't know if i was more puzzled by the curious handling of the NO_DATA_FOUND exception in SQL or by the fact that i stumbled no data on it only today.At any rate, it's something that can lead to unexpected results if you make the mistake to mix SQL and PL/SQL without taking the appropriate countermeasures.create or replacefunction strange(p in number)return numberasn number;beginif p = 0 thenselect pinto nfrom dual;elsif p = 1 thenselect pinto nfrom dualwhere 1=0;elsebeginselect pinto nfrom dualwhere 1=0;exceptionwhen no data found no_data_found thenraise_application_error(-20001, 'no_data_found');end;end if;return n;end;select strange(0) v from dual;V----0select strange(1) v from dual;V----(null)select strange(2) v from dual;ORA-20001: no_data_founddeclaren number;beginn := strange(1);end;/ORA-1403: no data foundIf you wonder why SQL handles differently the same function call to STRANGE(1), you may want to have a look at the following thread on AskTom.Until we fiddle with SELECT function FROM DUAL statements in SQLPlus for quick testing purposes we may be surprised to see that it returns NULL instead of an error, but the potential for more subtle problems comes when you invoke this function as a SQL cursor inside a PL/SQL program:set serveroutput ondeclaren integer;beginselect strange(1)into nfrom dual;dbms_output.put_line(nvl(n,-1));end;/-1The lesson here is to always trap the NO_DATA_FOUND error inside the function if we want to propagate the error to the caller, because we cannot predict if the caller will invoke the function from within a SQL cursor or as a PL/SQL function.As a last note of folklore, in the PL/SQL Reference Manual for 10gR2 there is a little