SQL & PL/SQL :: SELECT Will Raise NO_DATA_FOUND Exception

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


ADVERTISEMENT

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 View Related

PL/SQL :: How To Insert Data While NO_DATA_FOUND Exception Raise

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

PL/SQL - See If Given Item In Table Or Not And Then Raise Exception?

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

SQL & PL/SQL :: Raise Exception With All Elements In Collection

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

SQL & PL/SQL :: How To Raise Exception When Inserting Record In Table

Jul 5, 2011

How to raise a exception when inserting a record in a table using plsql?

View 11 Replies View Related

Forms :: Display Line Number Where Exception Raise?

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

Forms :: Use RAISE FORM-FAILURE-TRIGGER In EXCEPTION?

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

Forms :: WHEN-VALIDATE-ITEM Trigger Raise Unhandled Exception ORA-01422

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

Forms :: Ora-04062 When-validate-item-trigger Raise Unhanded Exception?

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

SQL & PL/SQL :: Function Raise No Error When Call In Select

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

SQL & PL/SQL :: Tracking Exception In One Single Block For Three Different Select Statement?

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

SQL & PL/SQL :: Capture NO_DATA_FOUND From Ref Cursor?

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

PL/SQL :: - NO_DATA_FOUND But Triggers Doesn't Continue Job

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

Application Express :: Exception Handling Pl Packages And APEX Exception Handler

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

Forms :: Implement Exception Handling In Exception Block Of A Trigger

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

SQL & PL/SQL :: Use Of RAISE Command?

Aug 31, 2011

explain to me what the "RAISE" command does in PL/SQL for the following code.

EXCEPTION
WHEN OTHERS THEN
INSERT INTO TMP_ERRCHECK(
PP_CD,
IHS_CD,

[code]...

Tried searching for some explanations but I still do not have a clue as to what the RAISE actually does. Do I even need to put it in the code?

View 3 Replies View Related

Forms :: Raise Alert When Browser Closed In 11g

Feb 27, 2012

I am working on Forms [32 Bit] Version 11.1.1.4.0 (Production). OS is Win 7 (32 bit). Internet Explorer 8. My Requirement is I need to raise an alert when user close the browser window before closing the Form.

View 2 Replies View Related

SQL & PL/SQL :: Exceptions Does Not Raise In Cursors Declared By User?

Feb 17, 2012

Why exceptions does not raise in cursors declared by user like in the following program

create or replace function sal (p_id employees.department_id%Type) return number is
----sal1 employees.salary%type:=0;
cursor cx is
select * from employees where department_id=p_id;
begin
for i in cx

[code]...

View 13 Replies View Related

Forms :: Raise Frm-41352 When Password Expired

Jul 20, 2010

we are working to increase the security of our oracle application by adding profil and expiration to all our database username.

Expiration is actually set to 60 days.So if i understand it correctly, 60 days is counting from when i change my password. So if I change it at 10:30am it will expire at 10:30am in 60 days.

Our forms application is calling all forms using the command open_form with SESSION parameter to open all forms in new session.

My problem is when the user connect in the system let say at 9:00am. At 10h30am the password will expire and when the user will click to open a new form from the application he will get the message ORA-28001:the password has expired.The new module will open even if password is expired and will raise the error FRM-41352: Failed to create session. So the module will stay in the same session than the caller.

Is there a way to catch the ORA-28001 and force a new logon.Probably with some code in the ON-ERROR of the called module but this mean that i will have to change +400 forms.

View 1 Replies View Related

Reports & Discoverer :: Raise Report Failure

Jul 26, 2010

In forms when we need to make the form not to validate we will give the command, raise form_trigger_failure like that if i need to make a report not to validate further what is the command to give?

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Sybase Raise Error And Rasie_application_error In Oracle?

May 6, 2010

I try to convert sybase raiseerror into oracle raise application error.

sybase code :

raiserror 20100 "can't add a contact number that's in the contact"

oracle

raise_application_error (
-20002, ':can''t add a contact number that''s in the contact');

the sybase error number 20100 unable to use in oracle due to the limitation of error number.RAISE_APPLICATION_ERROR should be negative and between -20000 and -20999.

how to use sybase error number in oracle?

View 2 Replies View Related

Application Express :: Raise Warning When Password Due To Expire - Oracle Database Users

Aug 23, 2012

Using Apex 4.1 and custom authentication based on Oracle Database users.

I want to be able to show a warning immediately after a user logs in if their password is due to expire in xx days. Oracle raises a warning (ORA-28002) but I don't know how to handle that from the standard Apex login page.

View 1 Replies View Related

Application Express :: Raise Application Error On Dynamic Action

Jul 18, 2013

I am using Apex 4.2. On a click of button a Dynamic Action is written, which calls a DB Package. The DB package has exception handlers and also the package has two output parametersError Code and Error MsgSo in my code(Dynamic Code) i make a call to the package and checks the value for Erroc Code.

Begin Package call......  If v_error_code <> '0' Then    Raise SC_Error;  End if;Exception   When SC_Error Then    RAise_Application_error(v_err_code, v_err_msg);End;

But when i run the page and I click I get an Internal exception errorOutput from Debug -- I am not sure what the Cell in Red colour means, because the package is returning a correct message (before the red line).AJAX call returned server error ORA-20003:

Customer has already been matched for Execute PL/SQL Code.4......is_internal_error: true4......apex_error_code: APEX.AJAX_ SERVER_ ERROR4.. ora_ sqlcode: -200034......ora_sqlerrm: ORA-20003: Customer has already been matched4......error_backtrace: ORA-06512: at line 41 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926 ORA-06512: at "SYS.WWV_DBMS_SQL", line 973 ORA-06512: at "SYS.WWV_DBMS_SQL", line 999 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 830 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN_UTIL", line 2304 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_ACTION_NATIVE", line 442 4......component.type: APEX_APPLICATION_PAGE_DA_ACTS4......component.id: 231852037977562094......component.name: NATIVE_EXECUTE_PLSQL_CODE4.

Execute Error Handling Callout defined on Page or Application Level4...Execute Statement: begin declare l_error wwv_flow_error_api.t_error; begin l_error := wwv_flow_error_api.g_error; wwv_flow_error_api.g_error_result := apx_error_utils.handleerror ( p_error => l_error ); end; end;4......Values changed by callout:4..

An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference# for further investigation. 4..display_location: ON_ERROR_PAGE4Stop APEX Engine detected4Final commit4-1 - 59

View 4 Replies View Related

SQL & PL/SQL :: How To Get Statement That Causes Exception

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

SQL & PL/SQL :: How To Handle The Exception

May 19, 2011

The following code is working fine,But the thing is if column already exists in the table,then also the other statements should be executed instead of coming out of procedure.SO how can I handle that exception??

SQL> CREATE OR REPLACE PROCEDURE sp_execparameters(tname IN VARCHAR2,
colname IN VARCHAR2,datatype IN VARCHAR2)
2 AS
3 v_sqlstr1 VARCHAR2(1000);
4 BEGIN
5 v_sqlstr1 := 'alter table '||tname||' add '||colname ||' '|| datatype ;
[code].........

View 3 Replies View Related

SQL & PL/SQL :: Exception Is Not Raising?

Sep 16, 2010

1 declare
2 cursor c1 is select * from emp where deptno=&no;
3 begin
4 for i in c1 loop
5 dbms_output.put_line(i.empno||' ' || i.sal);

[code]...

PL/SQL procedure successfully completed.

SQL> /
Enter value for no: 120
old 2: cursor c1 is select * from emp where deptno=&no;
new 2: cursor c1 is select * from emp where deptno=120;

PL/SQL procedure successfully completed.

Even though deptno 120 is not there in emp table, the exception is not raising?

View 4 Replies View Related

SQL & PL/SQL :: Exception Handling?

Jul 16, 2012

Is it possible to take execution control back from exception handling section to Execution statement?.. If Yes then How?..

View 25 Replies View Related

How To Write Exception Handler For PL / SQL

Jan 22, 2007

How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"

View 2 Replies View Related

ORA-07445 - Exception Encountered?

May 9, 2011

Am getting the error ORA-03113: end-of-file on communication channel when am trying to run a query and when i checked the trace file the infromation i got is given below.....

CODEksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x363376E] [] [] [] []
Current SQL statement for this session:
select
count(av_sal)

[code]...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved