Oracle Sql Error 100
Contents |
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 sql error code 100
Oracle sqlcode = 100 tipsSqlcode 100 Ora-01403
Oracle Database Tips by Burleson Consulting April 1, 2015 Question: What is the sqlcode 100 in PL/SQL and what Oracle ora 100 error is associated with sqlcode=100. Also, do I have to test for sqlcode=100 directly? Answer: The PL/SQL exception handler will about a PL/SQL program with a ORA-01403 error, regardless of whether you test for ora-00100 sqlcode=100. The sqlcode=100 is the most common trapped error in PL/SQL, the "no data found" Boolean expression. For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE. Get the Complete Oracle SQL Tuning Information The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts
Sql Server Error 100
and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of Oracle Corporation. ��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 sqlca.sqlcode powerbuilder Stack Overflow the company Business Learn more about hiring developers or posting ads with oracle exception sqlcode 100 us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for
Sqlcode 0
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 http://www.dba-oracle.com/t_sqlcode_100.htm can answer The best answers are voted up and rise to the top Why Sqlcode 100 does not come with exception up vote 3 down vote favorite 1 I have following query in a PL\SQL procedure on Oracle 10.2: This is the code LOOP BEGIN SELECT a.poid_id0 into v_acc_account_poidid0 FROM account_t a WHERE a.poid_id0=i_acct_id0 FOR UPDATE OF a.poid_id0 NOWAIT; EXIT WHEN sqlcode = 0; EXCEPTION WHEN resource_busy THEN BEGIN v_max_retry_times_counter := v_max_retry_times_counter http://dba.stackexchange.com/questions/10508/why-sqlcode-100-does-not-come-with-exception + 1 ; IF (v_max_retry_times_counter>v_max_retry_limit) THEN RAISE_APPLICATION_ERROR (ERROR_SELECTING,'Resource Busy with Nowait Option.',TRUE); EXIT; END IF; DBMS_LOCK.sleep(2); END; WHEN OTHERS THEN BEGIN RAISE_APPLICATION_ERROR (ERROR_SELECTING,'ORACLE ERROR DESCRIPTION'||sqlerrm ,TRUE); EXIT; END; END; END LOOP; The statement returns sqlcode 100 and does not throw an exception. From the Oracle documentation I understand that the error code is accompanied by an exception. What may be the reason behind this behaviour? The documentation says the error code 100 is DATA NOT FOUND; we have data for the select query. In this context does DATA NOT FOUND mean the select is failing or the lock is not available? Any help much appreciated. sql oracle exception locking share|improve this question edited Jan 13 '12 at 2:59 Andrew Russell 22837 asked Jan 11 '12 at 10:48 Nandish A migrated from stackoverflow.com Jan 11 '12 at 13:12 This question came from our site for professional and enthusiast programmers. Please specify which database. SQL Server? Oracle? MySQL? PostgreSQL? –Animesh Jan 11 '12 at 10:56 Oracle database. Updated the query –Nandish A Jan 11 '12 at 10:58 1 HOW did the statement return "sqlcode of 100"? –René Nyffenegger Jan 11 '12 at 11:20 Copied the sqlcode into temporary variable after executing this and then printed it –Nand
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 http://stackoverflow.com/questions/3940522/why-is-no-data-found-ora-01403-an-exception-in-oracle hiring developers or posting ads with 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, just like you, helping each other. Join them; it only takes a minute: Sign up Why is no_data_found ORA-01403 an exception in Oracle? up vote 12 down vote favorite 2 If the SELECT INTO statement doesn't return at least one row, ORA-01403 is thrown. For every code 100 other DBMS I know this is normal on a SELECT. Only Oracle treats a SELECT INTO like this. CREATE OR REPLACE PROCEDURE no_data_proc IS dummy dual.dummy%TYPE; BEGIN BEGIN SELECT dummy INTO dummy FROM dual WHERE dummy = 'Y'; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Why is this needed?'); END; END no_data_proc; Why? In my opinion you don't need this exception really. It is too much overhead. Sometimes it is handy but you have to write a whole BEGIN, EXCEPTION, WHEN, oracle sql error END Block. Are there any essential reasons I don't see? oracle exception exception-handling plsql ora-01403 share|improve this question edited Feb 12 '11 at 1:33 OMG Ponies 199k37361417 asked Oct 15 '10 at 8:12 Stephan Schielke 1,11051735 4 Don't forget to catch TOO_MANY_ROWS when the select returns more than one row. –Rene Oct 15 '10 at 13:19 add a comment| 6 Answers 6 active oldest votes up vote 15 down vote accepted The exception block is not needed, you might use it or not, depending on the context. Here you are actively ignoring the exception (the procedure will return successfully) but most of the time if you're doing a SELECT INTO you want it to fail if it doesn't return a row, consider: PROCEDURE update_employee_salary (p_empno) IS l_salary NUMBER; BEGIN SELECT sal INTO l_salary FROM emp WHERE empno = p_empno FOR UPDATE; /* do something with emp data */ END; Here I want my function to fail if it is called with an empno that doesn't exist in the EMP table. I might catch the exception to raise a meaningful error message (with raise_application_error) but most of the time I'm happy with the ORA-01403. In general, the only exceptions you should catch are the expected exceptions (i.e. this should not be the standard to catch all ORA-01403, or all exceptions for that matter). share|improve this answer answered Oct 15 '10 at 8:36 Vincent Malgr