Apr 28, 2015 · SQL> SET serveroutput ON SQL> SQL> DECLARE 2 o_ename emp.ename%TYPE; 3 i_empno emp.empno%TYPE; 4 BEGIN 5 SELECT ename INTO o_ename FROM emp WHERE empno = i_empno; 6 -- Handle no_data_found 7 EXCEPTION 8 WHEN no_data_found THEN 9 -- do something 10 dbms_output.put_line('No records found for …

Feb 4, 2016 · If the standard exception handling described by Sandeep seems to much overhead (like in my case) and you're fine with a NULL or some individual <not found> value), you might just transform it like this:

Dec 18, 2013 · May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method: declare otherVar number; begin for i in 1 .. 5000 loop begin for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop otherVar := foo_rec.NEEDED_FIELD; end loop; otherVar := 0; end; end loop; end;

Dec 3, 2014 · I have a pl sql code that execute three queries sequentially to determine a match level and do some logic The issue is - when first query has no results (completely valid scenario) I get ORA-01403...

Dec 28, 2015 · I want when this happens to ask for new variables using declare, but when I run it asks me everything in the begining: Set serveroutput on; DECLARE v_aid ALUNO.AID%TYPE := &amp;vs_aid;

SOLVED strange behavior in SQL developer caused me to overlook potential whitespace: It looks as though SQL Developer when running the standalone select applies its own trimming comparator when doing the 'WHERE sms_username = p_BAS_user_name;' portion.. turns out when sitting in the package it does not.. bunch of white space was causing the issue.. still …

Feb 26, 2014 · DECLARE DATE_GIVEN DATE; RESULTROW DATE_REFERENCE%ROWTYPE; BEGIN -- Lots of code -- Lots of code -- Lots of code DATE_GIVEN := TO_DATE('2014-02-26 12:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- This one throws the ORA-01403 exception if no data was found SELECT * INTO RESULTROW FROM …

Jan 18, 2017 · create or replace package body pkg_mnt_departments is procedure p_get_data(ls_cursor out sys_refcursor) is begin begin open ls_cursor for select field1, field2 from mytable where 1 = 2; exception when others then dbms_output.put_line('Exception'); end; end p_get_data; end pkg_mnt_departments;

Mar 7, 2019 · You have to enclose offending part of the script into its own BEGIN-EXCEPTION-END block, e.g. BEGIN OPEN C_TABLE_PARTITON_LIST; LOOP FETCH C_TABLE_PARTITON_LIST INTO TABLE_PARTITION_LIST; EXIT WHEN C_TABLE_PARTITON_LIST%NOTFOUND; begin --> you need this ...

Nov 18, 2015 · SELECT <something...> IF SQLCODE = 100 THEN -- No data found <no-data handler> END IF it is likely IMHO that the check for SQLCODE = 100 would be skipped frequently. Having an exception raised rams it right up your nose that A) an important condition (no data found) occurred, and B) NO ALLOWANCE WAS MADE FOR THIS.