Ora 01403 Error No Data Found
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! ora-01403 no data found in package Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson
Ora-01403 No Data Found Ora-06512
Blog
ORA-01403: no ora-01403 no data found select into data found tips Oracle Error Tips by Burleson Consulting Oracle docs note this about ORA-01403: ORA-01403 no data found Cause: In a ora-01403 no data found in oracle apps 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 are standard SQL queries whichOra 01403 No Data Found Apex
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 access the particular variable. Creating an exception in which on
here for a quick overview of the site Help Center Detailed answers to any questions you might have ora-01403 no data found exception handling Meta Discuss the workings and policies of this site About Us ora 01403 no data found in forms Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with
Ora-01403 No Data Found In Procedure
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm just like you, helping each other. Join them; it only takes a minute: Sign up 01403. 00000 - “no data found” up vote 1 down vote favorite I'm updating my docmeta table but i get the ORA-01403: no data found ORA-06512: at line 25 01403. 00000 - "no data found" error Below is the query. How http://stackoverflow.com/questions/22531260/01403-00000-no-data-found can i get rid of this error? DECLARE varPayTerm varchar2(300); BEGIN FOR X IN( SELECT d.did, d.xproject_id FROM revisions r, (SELECT DDOCNAME, MAX(DID) mDID FROM REVISIONS REV WHERE dcreatedate >='01-Jan-14' GROUP BY DDOCNAME ) RevLatestID, docmeta d --temp_project p WHERE RevLatestID.mdid = r.did and d.did = r.did ) loop select paymentterm into varPayTerm from project where pid = X.xproject_id and paymentterm is not null; update docmeta set xpaymentterm= varPayTerm where did=X.dID and xproject_id = X.xproject_id; END LOOP; END ; sql oracle-sqldeveloper share|improve this question asked Mar 20 '14 at 11:16 Mani 39212 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote accepted The SELECT...INTO statements is returning no rows If it is valid for no rows to be returned, and you want to continue you can catch and ignore the NO_DATA_FOUND exception like this: ... BEGIN SELECT ... INTO...; EXCEPTION WHEN NO_DATA_FOUND THEN paymentterm := 0; -- or ant msg you want then return 'no data here'; END; ... share|improve
easiest fix would be is to handle the error in the PL/SQL block, When a SQL statement is written within http://www.orafaq.com/wiki/ORA-01403 a PL/SQL block, enclose the SQL with a BEGIN and END statement. Handle the exception and raise a user-friendly message or handle the rest of the processing. Eg: CREATE http://dba.stackexchange.com/questions/105315/oracle-trigger-after-insert-error-ora-01403-no-data-found OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS l_empname VARCHAR2(50); BEGIN SELECT empname INTO l_empname FROM emp WHERE empno = p_empno; IF l_empname = 'Sarah Jones' THEN INSERT INTO no data empresult values ('105', 'Found Sarah Jones'); END IF; END; / The above procedure has not handled the error that would be raised if the select statement did not find the specified empno. See the following for the error raised: SQL> exec test_proc (1) begin test_proc (1); end; * ERROR at line 1: ORA-01403: no data found ORA-06512: at no data found "TAROT.TEST_PROC", line 4 ORA-06512: at line 1 If you want the procedure to execute properly without errors on the screen, then you would have to handle the error. The Exception needs to be controlled by adding an exception handler to the code. The above code has been modified to handle an exception NO_DATA_FOUND. This is the name of the exception that the error relates to. CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS l_empname VARCHAR2(50); BEGIN SELECT empname INTO l_empname FROM emp WHERE empno = p_empno; IF l_empname = 'Sarah Jones' THEN INSERT INTO empresult values ('105', 'Found Sarah Jones'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones'); END; / If you execute the above procedure now, this is what you would see: SQL> exec test_proc (1) PL/SQL procedure successfully completed. SQL> select * 2 from empresult; EMPNO EMPNAME --------- -------------------------------------------------- 1 Did not find Sarah Jones As the exception was handled, a row was inserted into the empresult table with the error message
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Oracle trigger after insert error ORA-01403: no data found up vote 0 down vote favorite I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table that's my code CREATE TABLE "SEX" ( "ID" NUMBER, "CS" VARCHAR2(255), "SEX" VARCHAR2(255), CONSTRAINT "SEX_PK" PRIMARY KEY ("ID") ENABLE, CONSTRAINT "SEX_UK" UNIQUE ("CS", "SEX") ENABLE ) / CREATE TABLE "NAC" ( "ID" NUMBER, "CN" VARCHAR2(255), "NAC" VARCHAR2(255), "SEX" NUMBER, CONSTRAINT "NAC_PK" PRIMARY KEY ("ID") ENABLE, CONSTRAINT "NAC_UK" UNIQUE ("CN", "SEX") ENABLE ) / ALTER TABLE "NAC" ADD CONSTRAINT "NAC_FK" FOREIGN KEY ("SEX") REFERENCES "SEX" ("ID") ON DELETE CASCADE ENABLE / CREATE INDEX "NAC_IDX" ON "NAC" ("SEX") / CREATE OR REPLACE TRIGGER "BI_NAC" before insert on "NAC" for each row begin if :NEW."ID" is null then select "NAC_SEQ".nextval into :NEW."ID" from sys.dual; end if; end; / ALTER TRIGGER "BI_NAC" ENABLE / CREATE OR REPLACE TRIGGER "BI_SEX" before insert on "SEX" for each row begin if :NEW."ID" is null then select "SEX_SEQ".nextval into :NEW."ID" from sys.dual; end if; end; / ALTER TRIGGER "BI_SEX" ENABLE / CREATE TABLE "PRUEBA1" ( "ID" NUMBER NOT NULL ENABLE, "SEXO" NUMBER(1,0) NOT NULL ENABLE, "NACIONALIDAD" NUMBER(1,0) NOT NULL ENABLE, "CEDULA" NUMBER(8,0) NOT NULL ENABLE, CONSTRAINT "PRUEBA1_PK" PRIMARY KEY ("ID") ENABLE, CONSTRAINT "PRUEBA1_UK" UNIQUE ("NACIONALIDAD", "CEDULA") ENABLE ) / ALTER TABLE "PRUEBA1" ADD CONSTRAINT "PRUEBA1_FK01" FOREIGN KEY ("SEXO") REFERENCES "SEX" ("ID") ENABLE / ALTER TABLE "PRUEBA1" ADD CONSTRAINT "PRUEBA1_FK02" FOREIGN KEY ("NACIONALIDAD") REFERENCES "NAC" ("ID") ENABLE / CREATE INDEX "PRUEBA1_IDX" ON "PRUEBA1" ("SEXO", "NACIONALIDAD") / CREATE OR REPLACE TRIGGER "BI_PRUEBA1" before insert on "PRUEBA1" for each row