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


ADVERTISEMENT

PL/SQL :: Exception Handling In Insert Statement

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

How To Get SQL Statement That Caused Exception In Oracle Function

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

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

PL/SQL :: How To Implement Exception Handling On MERGE Statement In Oracle10g

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

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

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

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

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

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

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

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

SQL & PL/SQL :: Getting Column Which Caused The Exception?

Jan 14, 2008

I am using Oracle 10g Rel 2 and currenly working on a project which creates a repository and bulk inserts data into it using FORALL statment. I am using SAVE Exceptions to save the errors in a table and then report to the user about these errors.

My question is ,can i somehow know the column which caused the exception ? Currently ,we can save only SQLCODE and SQLERRM . Is there any possibility that I get to know the column also which raised the exception ? For example ,during a insert ,if column raises exception

ORA-01438: value larger than specified precision allows for this column

Is it possible for me know using some programming technique that which column raised this exception ?

View 12 Replies View Related

Forms :: How To Add Exception In Given Query

Nov 3, 2011

when no data found this query generate error ora-01403 but if i count these transaction and then apply this trigger it works very well i am not interested to count because it takes a time i want that when no data found exception clause took control but i am not understand how to add exception clause in this trigger

select sum(nvl(gl_quantity,0)),sum(nvl(gl_amount,0)) into cr_qty,cr_amt from account.glhis where gl_drcr='CR' and gl_account=:prod_consume_auto.store_code and gl_date<=:prod_master_auto.voc_date and gl_voc_no<>:prod_master_auto.voc_no group by gl_account;

View 3 Replies View Related

NL Exception Trying To Connect To 10g RAC With JDBC

Jan 11, 2005

I'm trying to connect to multiple Oracle 10g databases (failover) over the JDBC thin client, and when I run the following, I get: java.sql. SQLException: Io exception: NL Exception was generated

connection = DriverManager.getConnection("jdbc:oracle:thin:@DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = dbdev) (PORT = 1521))) (CONNECT_DATA = (INSTANCE_NAME = orcldev)))", "scott", "tiger");

I eventually want to put multiple ADDRESSes in that string, but right now I'm trying to get it to work with just one! I've tried multiple variations of this, like changing INSTANCE_NAME to SID or SERVICE_NAME, and using the internal IP address for the HOST dbdev (see below). Here's the tnsnames.ora file used by SQL*Plus on the same client machine:

ORCLDEV.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(CONNECT_DATA =
(SID = orcldev)
)
)

View 7 Replies View Related

SQL & PL/SQL :: 1843 Non-ORACLE Exception?

Sep 22, 2010

i cant trace out why this exception is for Below given is my scripts create or replace package body pkg_rwc_migration as
procedure pro_rwc_mig as

cursor cur_mig_job is select substr(job_id,1,4) "JOB_ID",5 "JOB_STAGE_ID",gii_app_id "GII_APP_ID",
gai_app_id "GAI_APP_ID",status_id "STATUS_ID",job_type "JOB_TYPE",
'jobdesc' "JOB_DESC",current_assignee "CURRENT_ASSIGNEE",CREATED_DATE "CREATED_DATE",
CREATED_BY "CREATED_BY",MODIFIED_DATE "MODIFIED_DATE",MODIFIED_BY "MODIFIED_BY",
IS_COMPLETE "IS_COMPLETE",OVERWRITTEN_BY "OVERWRITTEN_BY",OVERWRITTEN_DATE
[code]...

when others then

v_error_code := SUBSTR(v_error_code||':'||SQLCODE,1,30);
For i in 1 ..sql%bulk_exceptions.count LOOP
v_error_desc := SUBSTR( SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE),1,400);
dbms_output.put_line('The value of Error is '|| v_error_code ||' '||v_error_desc);
End loop;

[code]...

By executing the above i am getting Error
SQL> @pkg_rwc_migration_bdy.sql
130 /

Package body created.

SQL> Begin
2 pkg_rwc_migration.pro_rwc_mig;
3 End;
4 /
The value of Error is :-1843 -1843: non-ORACLE exception

View 2 Replies View Related

SQL & PL/SQL :: Have Exception In Declare Block

Feb 20, 2012

Can we have any exception in DECLARE block (shown are just for example)?

Declare
exception occured
begin
code
end;

declare
variables;
begin
declare
variables;
begin
code;
end;
end;

View 6 Replies View Related

SQL & PL/SQL :: How To Handle Exception For ORA-06550

Nov 17, 2011

I trying to Assign XML content to the clob variable inside the pl/sql block, But i am getting the Below Error:

declare
t clob;
begin
t := 'xml content exceeds 32000 characters'

update test
clob_cloumn = t;
where id =2;

exception
when others then
null;
End;

ORA-06550: line 5, column 4:
PLS-00172: string literal too long

I need to handle this exception, i know it length exceeds 32000 characters, but even though i need to handle the exception and to perform other operation after handling the exception.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Exception Name And Error Text

Aug 21, 2012

I'm inserting to a table through a procedure, I want to log the rejected records to a log table with the execption / reason.How can I get the exception name and error text in PL/SQL ?

View 20 Replies View Related

SQL & PL/SQL :: Exception At Update Trigger

Aug 14, 2012

I am writing a trigger TR_EMP on a table EMP which has columns EMP_ID, EMP_NAME, ALT_EMP_ID.

Now I am updating ALT_EMP_ID for an EMP_ID(PK) which is unique.

If ALT_EMP_ID is null
then
:new.ALT_EMP_ID = l_alt_emp_id;
end if;

As this ALT_EMP_ID is unique, same ID shouldn't be inserted again here. When data being inserted with 2 different sessions for 2 different EMP_ID there is a possible chance of inserting same ALT_EMP_ID for both which results in Unique error. I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

View 4 Replies View Related

PL/SQL :: Exception Handling In Procedures

Oct 8, 2012

I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.

CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP

IF i = 18 THEN
tmp_no := temp;
END IF;

IF i = 21 THEN
tmp_name := temp;
END IF;

END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));

how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..

View 4 Replies View Related

Oracle 11g - Exception While Importing Statistics?

Jul 14, 2010

I am using Oracle 11g R2 version.I want to import the DB statistics. But i am getting an exception when i execute the command DBMS_STATS.IMPORT_SCHEMA_STATS ('user1','STATS_INFO', '','', TRUE, FALSE).

The error is ORA-20000: no statistics are imported ORA-06512: at "SYS.DBMS_STATS", line 10603 ORA-06512: at line 1.

The privileges 'ANALYZE ANY' and 'ANALYZE ANY DICTIONARY' is already given to the user.Also i executed this command as sys. But still error occurs.

Same command is successfully executed in Oracle 10g. Is there any difference in importing the statistics in Oracle 10g and 11g ?

View 2 Replies View Related

Exception Error ORA - 7445 On Solaris X64

Jul 22, 2010

I am getting following error in my alert log file of my database

ORA-07445: exception encountered [kduget () +1032][sigbus]

as i can perform some of my database activity but from some of the users i cannot login.

i tried to search out and found that some Memory needs to be flushed out so restarting of database is only action to be taken?

View 6 Replies View Related

SQL & PL/SQL :: Trigger Raised Unhandled Exception

Apr 27, 2013

I created my login as you have first to fill the form then login, unfortunately i am getting when i press the button as error frm-40735: when-button-pressed trigger raised unhandled exception ora-04063.

these are the code after fill the form

declare
sex varchar2(10);
emid varchar2(30);
begin
if :signup.gender = 1 then
sex := 'Male';
elsif :signup.gender =2 then
sex := 'Female';
[code]........

View 10 Replies View Related

SQL & PL/SQL :: Exception Handling In A Created Function

Dec 8, 2010

Basically I've created a function, when I run it there is a user input. Mine is a customer number between 1-10.

I was wondering is there a way to add in error check so if I typed in an invalid number it would give me a message saying "Wrong customer_number" or something along the lines of that?

I was told I wasn't able to use "DBMS_OUTPUT.PUT_LINE" in the function I need to tamper with the function header?

Here is my header -

CREATE OR REPLACE FUNCTION hours (custid customer.cust_id%TYPE)

RETURN NUMBER IS

multiply NUMBER;
returnanswer NUMBER;

View 2 Replies View Related

SQL & PL/SQL :: Oracle Trigger Exception Handling

Jun 12, 2012

I am writing a after trigger for one of my tables on every insert update and delete for my dataware house staging area. The process here is when ever there is a change in the production database we need to capture this change in our changing area through triggers.

I am able to create the triggers but i am stuck with the exception handling portion of the trigger. I want to write an exception in the trigger where when the staging area is locked or for any other matter the data needs to be able to go to a error table when the staging area is not able to accept the data for some reason.

how i can write this excepyion in the trigger or anyother method i can follow to be able to handle this scenerio.

View 2 Replies View Related







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