In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
RE: In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
PL/SQL manages these errors by raising standard exceptions which you can trap in the exception section of the block with the NO_DATA_FOUND and TOO_MANY_ROWS exceptions. Code SELECT statements to return a single row.
RE: In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
You may avoid too_many_rows exception using CURSORS.
In cursors if NO_DATA_FOUND then it will not raise any exception. In cursors you need to handle it using FOUND or NOTFOUND cursor attributes.
RE: In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
If you don't want an excpetion to be raised on a SELECT statement you can avoid executing the statement by having a count just before the SELECT as shown below
SELECT count(rowid) into v_count from tables where conditions.
if(v_count 1) then SELECT column into v_column from tables where conditions; else --Select statement not executed because it will throw exception v_column dummy_value; end if;
However it is a much better practice to handle these errors using exceptions
In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
First let us understand why these errors cropup.
1) NO_DATA_FOUND - is raised only for select into statements when the where clause of the query does not match any rows.
2)TOO_MANY_rows - is raised if a select into statement matches more than one row.
Solution for 1 - In the exception section use a handler like this when no_data_found then insert into log_error ('no matching data') .
for 2) - This is a result of poor design or programming. One need to use a cursor.
