PL/SQL :: What Kind Of Exception Can Raise A Select Statement Excluding NO_DATA_FOUND
Oct 23, 2013
what kind of exception can raise a select statement excluding NO_DATA_FOUND; For example i try to run the following: select * from departments where departments_id=11; In a situation like that what kind of error oracle can raise?I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.
View 25 Replies
ADVERTISEMENT
Feb 10, 2011
I was going through the link: URL....Here within the box under the title 'Exception Handling -- Quick Facts and Tips', it states, Once you have handled an exception, normal program execution continues. You are no longer in an "exception" situation.
I wanted to verify this and used the below:The DDL and DMLs:
CREATE TABLE emp(empno NUMBER(4), ename VARCHAR2(10));
INSERT INTO emp VALUES(7369,'SMITH');
The program:
DECLARE
x VARCHAR2(10);
BEGIN
SELECT ename INTO x FROM emp WHERE empno=4567;
SELECT ename INTO x FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(x);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR OCCURED');
END;
The first SELECT will raise NO_DATA_FOUND exception. Even though I have handled it, the second SELECT is not executing.But the link says, that normal program execution will continue. So, Iam expecting the second SELECT statement to work.
View 4 Replies
View Related
Aug 19, 2013
create or replace procedure set_update(p_name in varchar2,p_email in varchar2)is l_data user_details%rowtype; beginselect * into l_data from user_details ud where ud.name = p_name and ud.email = p_email; if l_data.name = p_name and l_data.email = p_email then update user_details set last_login_date = sysdate , date_updated = sysdate where name = p_name and email=p_email; end if; exception when no_data_found then insert into user_details(user_id,name,last_login_date,date_updated,date_created,email) values (l_data.user_id,p_name,l_data.last_login_date,l_data.date_updated,sysdate,p_email);end set_update;
This is my procedure will pass two parameters if it is available in table it will update other wise insert that data in table. My problem is while data is not available that data is inserted into table but that not inserted into table.
View 7 Replies
View Related
Mar 8, 2010
how can i track the exception for three select statement in one pl-sql block. here is synario.......
begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;
i want to track exception any one for ex no_data_found for all these three different statement.
I know if i put this three statement in three different pl-sql sublock then i can trap it....
how can i trap it in one single block?
View 4 Replies
View Related
May 2, 2009
I'm trying to figure out how to simply check and see if a given item is in a table, and if its not, then raise an exception.
for example i have a table employees and each employee has a primary key employID. I need to write a procedure where employID is given as a parameter and if the one given isnt in the table then raise an exception.
View 3 Replies
View Related
Nov 17, 2011
PROCEDURE test_data(
)
IS
TYPE test_tab IS TABLE OF NUMBER(15);
t1 test_tab;
BEGIN
I have missing elements in a collection that should be reported to the terminal while running the procedure
IF t1.COUNT > 0 THEN
RAISE_APPLICATION_ERROR('Missing Elements: ' || elments from collection t1);
How can I raise an exception with all the missing elements from collection t1 here?
END IF;
END;
View 8 Replies
View Related
Jul 5, 2011
How to raise a exception when inserting a record in a table using plsql?
View 11 Replies
View Related
Feb 25, 2011
We have Forms 10g for our application.
When i raise a exception in a procedure / trigger, i want to show the line number where the exception happened. is there any builtin to show that.
For eg
Exception when others
message('Error in this procedure');
raise;
I want the above exception to raise with line number also. How do i do it. I need some built in procedure in forms.
View 10 Replies
View Related
Jun 12, 2012
My requirement, if there is no record in emp table when validate the EMP_NO_CHK text field, i need to set the focus on that field (EMP_NO_CHK) itself. But while execute the following code, I got error.chieve the task.
--EMP_NO_CHK_WHEN_VALIDATE_ITEM Trigger
-----------------------------------
Declare
cursor c is Select * from emp where Emp_no = :header.empno;
c1 emp%rowtype;
n NUMBER;
[code]...
View 4 Replies
View Related
Sep 15, 2011
I have a form (StaffDetail) with three different blcoks. One of them is an Application Assignment block which consists of nothing but Assignment Type, Staff (Name), Last Updated By and Last Updated Date.. The Assignment Type and Staff has LOVs assigned to them individually.. Assignment type lets me pick all different types except for Project Manager. As soon as I select this option, i get the following error :
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception...
This does not even let me select any other Assignment type, then i have to go close all sessions and come back into the same screen.
Also, this form was first created and tested on the development environment and later moved to QA environment. All selections work in the development environment.. I checked for the roles in QA for individual Users..
View 2 Replies
View Related
Oct 15, 2011
I have Written this code in WHEN-VALIDATE-ITEM-TRIGGER on PO_DATE field
if not( :PO_HEADER.PO_DATE IS NOT NULL ) then
:po_header.po_date := trunc(sysdate);
end if;
if trunc(:PO_HEADER.PO_DATE)>trunc(sysdate) then
displayerror.errmsg('PO date cannot be future date!');
[code]...
when i am navigating through po_DATE field in the form its showing the error that
WHEN-VALIDATE-ITEM-TRIGGER raised unhandled exception.
View 2 Replies
View Related
Feb 17, 2010
why function does no raise error no_data found when call in select statement.
1) create one function.
CREATE OR REPLACE function fn_sal(v_id NUMBER) RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp where empno=0;
RETURN v_sal;
END;
2) call it in select statement.
SELECT fn_sal(e.sal),e.* FROM emp e
select satement cause no error , it displayes all the records but null for the function cloumn.
why it not gives no_data_found error.
View 4 Replies
View Related
Jul 17, 2011
I am using Exception when others then body end;
inside the body i can get SQLCODE and SQLERRM but I also need to get the SQL statement that caused the error and I dont know how.
View 39 Replies
View Related
Jul 17, 2012
Here's sample code :
declare
i number;
l_rec number;
cursor c1 is select i from t1;
begin
[code]....
After executing Data must be inserted into t2..If any error is there it should insert into t2 with status 'E' and move on T2
i fflag
---------
11 E
111 Y
1111 Y
The code above is failing after inserting 11 E into t2 table
View 5 Replies
View Related
Jul 18, 2011
I am trying to search a way to get the SQL statement that caused an exception withing an oracle function.
I tried:
SELECT sql_text
from v$session ses, v$sql sql
where sql.sql_id = ses.prev_sql_id
and ses.sid = sys_context('userenv','SID') AND ROWNUM = 1;
but this doesn't always return the last statement that the function has executed. if needed i can send the complete script for the function and its tables and stored procedures for testing.
View 3 Replies
View Related
Jul 19, 2012
How to handle the exception on below MERGE statement?
MERGE INTO COMM_EXSTS_COMIT_AGGR TARGET
USING
(
select * from ABC ) SRC
ON
(
SRC.COMMITMENT_ID = TARGET.COMMITMENT_ID
)
WHEN MATCHED THEN
UPDATE
WHEN NOT MATCHED THEN
INSERT ;
View 4 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Sep 1, 2011
My need is to build columns for each month in table and find changes between monthes. My first try works fine, but next step should be performed using wider period.
with t2 as (
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 840 as currencyid, 3649509 as dealid, 7.5 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 12 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 12 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 13.5 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 13 as avalue from dual union all
[code]....
As you can see, I've got virtual table named jan2008, feb2008, mar2008, apr2008 left-joined to tt1 table. But what should I do with much more wider period, for example 5 years? Can I do it without creating virtual tables for each month?
View 7 Replies
View Related
Feb 11, 2013
I am returning a refcursor as OUT parameter in my stored procedure. I would like to capture a no data found for the refcursor. Is there a way I can raise the exception without compromising the performance?
I have tried the below options that are not working.
1. If I run a SELECT query to check for records and then OPEN the refcursor for that SELECT, then it takes a performance hit as I am reading the table twice.
2. I can FETCH a refcursor into a table type and check the count in the table to raise exception.But once I fetch from a refcuror, the data is gone. So, this option does not work either.
View 2 Replies
View Related
May 2, 2010
I read that few privilegs can not be granted to roles like 'UNLIMITED TABLESPACE ' and they need to granted to user directly.
1. Is there any reason behind that few privileges cant be granted to roles or is it just oracle design.
2. what kind of privileges cant be assigned to roles.
View 7 Replies
View Related
Sep 9, 2012
i've a problem regarding my code at line: 76. URL....
If i put a RAISE_APPLICATION_ERROR just before:
SELECT ID_GIOCATORE INTO CONTR_GIOCATORE2
FROM COMP_CONTR_GIOCATORE_PARTITA GC JOIN CONTRATTO C ON GC.ID_CONTRATTO = C.ID_CONTRATTO
WHERE GC.ID_PARTITA = :NEW.ID_PARTITA AND (C.ID_CASELLA = 28 OR C.ID_CASELLA = 12) AND C.ID_CASELLA <> :NEW.ID_CASELLA AND
[code]....
But if i put the RAISE_APPLICATION_ERROR just after this statement and before IF(CONTR_GIOCATORE2 IS NOT NULL AND CONTR_GIOCATORE2 = CONTR_GIOCATORE) THEN, nothing happens after the insert(that goes well) and the trigger doesn't do his job(insert,update etc). if i do that select, i got the no data found, so i put the exception to set the variable CONTR_GIOCATORE2 to NULL.
View 1 Replies
View Related
Jun 30, 2013
It seems certain queries search by the number of days to ship(number of days between the order and shipping dates). What kind of index would improve the performance of these queries?
View 2 Replies
View Related
Oct 7, 2013
APEX 4.2Oracle 11g Database We are using the standard exception handler that was introduced in APEX 4.1, and we have code in packages & procedures in the database (following proper processes of keeping code in the database where possible). When an exception is found in the procedures/ packages/functions, should the APEX application level exception handler catch any errors that occur or should they be handled in the package/procedure/function they occurred in? Why I ask if, we right now have exception handling code in the pl/code bodies BUT they write their errors to the same table that Apex's Exception handler does, but the errors are NOT presented to the user using the APEX exception handling mechanism.
View 2 Replies
View Related
Oct 10, 2011
I have to implement exception handling in the exception block of a trigger, Quote:exception
when ora_java.java_error then
message( 'Unable to call out to java, ' || ora_java.last_error );
ORA_JAVA.CLEAR_EXCEPTION;
when ORA_JAVA.EXCEPTION_THROWN then
ex := ORA_JAVA.LAST_EXCEPTION;
message( Exception_.toString(ex));
-- lv_exception := Exception_.getMessage(ex);
I get an error for the line: 'message( Exception_.toString(ex));'I have imported the java classes FException et IObject with their methods.
I have to create a Web Service Client, so I wonder if the paragraph Quote:when ORA_JAVA.EXCEPTION_THROWN then ex := ORA_ JAVA. LAST_ EXCEPTION; is mandatory.
View 2 Replies
View Related
Jan 2, 2012
Is there a way to know what kind of Backup (table/tablespace/full/schema) by looking at export dump file ? If yes, can you tell me the command ?
View 1 Replies
View Related
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
