1403 Error Code Sql
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
Sql Error 1403 Mapping
Blog
oracle sql 1403 ORA-01403: no data found tips Oracle Error Tips by Burleson Consulting Oracle docs note this about ORA-01403:Ora 01403 Error In Oracle
ORA-01403 no data found 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 java.sql.sqlexception: ora-01403: no data found 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 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 ora-01403 no data found ora-06512 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 only one row can be retrieved would allow the code to appropriately handle the exception without receiving the ORA-01403 error. To create this SELECT INTO query that will avoid the ORA-01403 error, and handle the appropriate exceptions, here is an example: SQL> create or replace function auth_Name 2 ( v_auth_state IN author.author_state%type) 3 return varchar2 4 as 5 v_authName author.author_last_name%type; 6 begin 7 select 8 author_last_na
View next topic View previous topic Add To Favorites This topic has been marked "Resolved." This topic is not resolved, but there is a WORKAROUND. Post new topic Reply to topic DSXchange Forum Index » IBM® DataStage Enterprise Edition (Formerly Parallel http://www.dsxchange.com/viewtopic.php?p=225825&sid=62f07447775b0c6ebe90d7f83c693e92 Extender/PX) Author Message vinaymanchinila Group memberships:Premium Members Joined: 06 Apr 2005 Posts: 353 Points: 2391 Posted: Mon Mar 12, 2007 1:58 pm Reply with quote Back to top DataStage Release: http://searchoracle.techtarget.com/answer/Query-sometimes-returns-ORA-1403 7x Job Type: Parallel OS: Unix Hi, When I run a job which reads from SQL and loads into Oracle table using Oracle Ent Stage, I get the "sqlcode:1403 " error. When I no data take the Update stmt from the stage and execute it in Toad it works fine ! I did try to change the 'Update Else Insert' option to 'Insert else Update' but its the same error, did any one have this issue with Oracle Ent stage? There are no date columns in my target oracle table,all the columns are char or varchar or decimal. Thanks, _________________Thanks, Vinay View no data found user's profile Send private message DSguru2B since February 2006 Group memberships:Premium Members, Heartland Usergroup Joined: 09 Feb 2005 Posts: 6854 Location: Houston, TX Points: 35670 Posted: Mon Mar 12, 2007 2:09 pm Reply with quote Back to top Sql Code 1403 means that the update record was not found according to the list provided by Vincent. Is your key a char column ? What happens when you change that to varchar. There might be an issue of spaces with char field. _________________ Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep. View user's profile Send private message Rate this response: 0 1 2 3 4 5 Not yet rated vinaymanchinila Group memberships:Premium Members Joined: 06 Apr 2005 Posts: 353 Points: 2391 Posted: Mon Mar 12, 2007 2:36 pm Reply with quote Back to top I had Decimal and Char as key columns and I changed it to Decimal and VarChar, still the same 1403 error and all the source rows are rejected. Curious , if it does not find the key coulmn shouldnt the job insert the row ? Thanks _________________Thanks, Vin
Topic Database design Database Admin View All Cloud infrastructure Availability Backup and recovery Export, import and migration Installation, upgrades and patches Oracle performance problems and tuning Oracle security Oracle DBA tools Error messages MySQL database Real Application Clusters (RAC) Applications View All Enterprise and business performance management Implementing and upgrading Oracle apps E-Business Suite Fusion applications Hyperion JD Edwards (JDE) PeopleSoft Siebel and Oracle CRM Data Mgmt View All BI (business intelligence) Data quality Data warehousing Metadata Database Admin View All Cloud infrastructure Availability Backup and recovery Database design Export, import and migration Installation, upgrades and patches Oracle performance problems and tuning Oracle security Oracle DBA tools Error messages MySQL database Real Application Clusters (RAC) Development View All BPEL in Oracle Open Source SQL Java / J2EE Stored procedures XML PL/SQL Fusion View All Oracle and BEA Application Server Data and application integration SOA (service-oriented architecture) Infrastructure View All Cloud computing infrastructure Exadata and Exalogic Grid computing Oracle on Linux Operating system Oracle hardware decisions Virtual machine Oracle management View All certification Oracle acquisitions Business process management Market analysis DBA jobs training and certification Small businesses Regulatory compliance Outsourcing Oracle on demand and SaaS Oracle strategy and product roadmap Oracle support services Oracle vs. SAP Oracle Web 2.0 Sun-Oracle infrastructure View All Oracle cloud computing Oracle OS Oracle virtualization Topics Archive View All Oracle DBA jobs Oracle Resources Training and certification Tutorials, tips and FAQs Pleas