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


ADVERTISEMENT

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 :: 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

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

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

Networking And Gateways :: Exception Handling For DBlink In Triggers

Mar 27, 2013

am using easysoft odbc driver for connecting my oracle with sql server.I have written below triger on my oracle db table, to insert the newly inserted data in sql as well. Now what i want is, if my dblink fails due to any reason, it should not effect my insertion on source oracle table on which i have created the triger.

create or replace
trigger trig_msg
after INSERT OR UPDATE OR DELETE ON msg
FOR each ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
[code]...

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

Handling NULL Value In XML Using GetStringVal?

Dec 13, 2012

I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for

select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t

How to handle rows with NULL values in the column 'XML_COL'.

View 3 Replies View Related

Handling NULL Values?

Jan 23, 2013

I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME

Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal

and COUNTY column in AFF_TEMP can contain a NULL value

County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway

I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)

[code]...

View 3 Replies View Related

SQL & PL/SQL :: OPEN FOR Cursor Handling?

Jan 13, 2011

I have a snippet of code

EXCEPTION
WHEN OTHERS THEN
H_Report_Error('sql',Sqlerrm);
end;

And I'm trying to figure out how to catch a error from a loop such as:

OPEN My_cursor FOR SQLCODEBLOCK
LOOP
FETCH a_variable INTO ResultCount;
EXIT
WHEN My_cursor % NOTFOUND ;
BEGIN
--Stuff done
End;
End Loop;

So right now I have a error in the SQLCODEBLOCK, but I can't catch that error. I've tried putting the exception in various places but it wont compile.

View 6 Replies View Related

Flexible Handling Of Duplicates Using A Trigger

Feb 12, 2007

I'd like to achieve the following (and YES, I do know that this is not multi-user safe, but that's not the point here):

Before inserting a record, the trigger shall check if there's already a duplicate one. Duplicate means in this case when there is an intersection of the time frame, defined by two numeric timestamps. That's also the cause why I cannot use a simple UNIQUE constraint here (in my opinion).

Okay, that already works (see code below). But now I need colliding records to be written to a temporary table so that those records can be returned and presented to the user for selection.

create or replace
TRIGGER TRIGGER1
BEFORE INSERT ON FLIGHT_TABLE
FOR EACH ROW
BEGIN
FOR fs_entry IN (SELECT * FROM FLIGHT_TABLE)
LOOP

[Code] .........

View 1 Replies View Related

SQL & PL/SQL :: Handling Exceptions Without Halting Execution

Sep 14, 2010

I have a set of 500 insert queries which i need to execute. I have included exception handler to catch any errors encountered. I don't want the execution to halt when an exception is encountered, i want the next insert statement to be executed

I am pasting the sample code below

DECLARE
error_code NUMBER := SQLCODE;
error_msg VARCHAR2 (300) := SQLERRM;
BEGIN
INSERT statement 1
INSERT statement 2.....
....................
INSERT statement 500.....

commit;
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE != 0) THEN
DBMS_OUTPUT.put_line(SQLERRM);
END IF;
END;

For example if insert statement 100 fails the exception should be caught and execution should pass to 101 statement.

View 13 Replies View Related

SQL & PL/SQL :: Handling Exceptions In Bulk Collect

Aug 17, 2012

For the following procedure if I send the existed employee number of emp table as input. The procedure is executing successfully. But if I send the employee number as input which does not exist in the emp table . The execution block does not handling the exception.

I am getting the following error.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RAKULA.SP_TEST_EXCEPTION_BULK", line 8
ORA-06512: at line 7

If I use WHEN OTHERS exception then I am able to handle that exception. Why it's happening like this.

CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception_bulk(i_empno NUMBER)
IS
t type_test1;
BEGIN
SELECT deptno BULK COLLECT INTO t
FROM emp
WHERE empno=i_empno;
[code].......

how to handle that exception.

If I create the procedure without using

BULK COLLECT then I am able to handle that exception using WHEN NO_DATA_FOUND

In the following procedure I am able to handle the exception.

CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception(i_empno NUMBER,v_dept_no OUT NUMBER)
IS
BEGIN
SELECT deptno INTO v_dept_no FROM emp
WHERE empno=i_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('employee number' ||i_empno|| 'does not exist');
END sp_test_exception;
/

View 2 Replies View Related

SQL & PL/SQL :: Error Handling Inside Cursor

Dec 31, 2012

I am writing a cursor and inside that cursor I am checking record exists or not and based on that I am doing my operation.But I am getting error that i can not use exception inside cursor see the below sample code (syntax may not be correct)

sample code------------------
cur c1
is select * from T1;
open c1
loop
fetch c1 into cur_id;

select name into var_name from t2 where id = cur_id;

exception
when no_data_found then
continue with next cursor value
end

update t3 set name = var_name where t3.id = cur_id;
commit;
end loop;
end;

View 6 Replies View Related

SQL & PL/SQL :: Handling No Of Rows Selected In Loops?

Feb 25, 2012

I'm attempting to write a plsql for finding missing archived logs for streams.

requirement is to run a select statement and print
1. 'NOT FOUND' if name column is null
2. '<name of the files>' if rows are returned
3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)

code i developed so for:
for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES'
and status!='A')
loop
if (cr.name = 'NOT FOUND')

[code]...

View 6 Replies View Related

XML DB :: Handling Multiple Namespace In XML While Using Table

Feb 7, 2013

I have the following data stored as a CLOB in my table

<financialTransaction xmlns="http://lombardrisk.com/reform/dfa/2012/02" xmlns:cfpml="http://www.fpml.org/FpML-5/reform/2012/02" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://lombardrisk.com/reform/dfa/2012/02 StandardisedTransactionInterface.xsd ">
<header>
[code]........                                 
               
I am trying to convert this into a relational DB view.

--CREATE OR REPLACE VIEW V_TRADES AS
SELECT
x.*
FROM ENGINE_ENTITIES,
XMLTABLE(xmlnamespaces('http://lombardrisk.com/reform/dfa/2012/02' as "n1",
'http://www.fpml.org/FpML-5/reform/2012/02' as "n2",
'http://www.matchingservice.com/coding-scheme/messageId' as "n3",
'http://www.matchingservice.com/partyId' as "n4",
[code].......  

I have multiple namespaces used in that CLOB. how to use it in the x Query?

View 3 Replies View Related

Globalization :: Handling Multibyte Characters

Aug 8, 2013

I have created a procedure which sends e-mail using UTL_SMTP. The procedure has a part in which we add the attachments to e-mail. Now , the issue is when i am adding an attachment which contains multibyte characters , these characters are replaced with '?'.

View 6 Replies View Related

Forms :: Third Level Block Handling In Oracle 6i?

Jun 24, 2010

I have created Master Detail form of Sales offer. I would like to enter item detail under Sales offer detail.

Example :

Sales Offer Header -> Sales Offer Detail -> Sales Offer Detail Detail.

I have tried to make a form in form 6i to fulfill above mention scenario. But when two or multiple users tried to enter data from this form all users entered into deadlock or hang stage.

View 10 Replies View Related

SQL & PL/SQL :: Handling Special Character (&) In Oracle Stored Procedure?

Apr 23, 2013

How to handle special charater '&' in stored procedure.

Following is a test code I want to Implement

CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary

[code]....

The code above resuts into INVALID state of stored procedure and cannot be executed.

View 8 Replies View Related

Performance Tuning :: Handling NULL Values In The Database?

Feb 6, 2012

We have database with multiple fields containing NULL values and in many queries we have NVL function which in turn is suppressing the index usage when in fact it is really essential (selectying very few rows from massive data) instead of creating lot of Function based indexes (NVL) or composite indexes with (nullable_column, constant) I am thinking of settting a default value for most of the fields In that regard I have some queries :

Which approach is better - setting default value for the fields or updating the fields with default value and modyfing inserts to take care of future data? Though altering table and modifing column to set default value looks better considering it will take care of data inserted in the future, it will invalidate the subroutines.I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column) How to take care of all the queries which are using the criteria 'where column1 IS NULL' or 'where column1 IS NOT NULL'. It will be really difficult task to manually change each and every occurrence of such condition even using user_source.

Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected.

Is there such precaution for varchar2 field purely from performance point of view?

View 3 Replies View Related

Server Utilities :: Handling Errors In Batch File?

Aug 21, 2010

i get flat file and i have set up a control M job so that it runs at a particular time.

initially my control file was as below:

LOAD DATA
INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50) char)

this was working fine. control M did not send FAIL message.but later i had to change the ctl file due to requirement. i had to add a when clause.

my code after modification is:

INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
when A<>'10'
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50)

now the control M is sending an erro message after it runs the job. error is Return 5. thats all it gives.

i think it is due to errorlevel 1. in log file it says zero records inserted due to data error. then what is causing control M to send fail message??

sqlloader is loading all the required records correctly.

View 10 Replies View Related

Reports & Discoverer :: Error Messages Handling In Report

Jan 18, 2011

I am generating a report with parameter form.

I do not want users to leave a parameter value null . so I applied after parameter form trigger

function AfterPForm return boolean is
begin
IF ( :my_no IS NULL ) THEN
srw.message(101, 'Please enter proper No.');
return (FALSE);
END IF;
return (TRUE);
end;

it is working fine, it displays proper message but after my message, a default message is displayed

REP-0771: After Form Trigger Failed.

I want to avoid this message. if i comment out return(false) then it displays message and runs the report. I want to avoid default oracle message and stop running report also.

View 7 Replies View Related

SQL & PL/SQL :: Handling Trademark Special Character During Data Loading In Oracle 11g

Aug 6, 2012

we have a Oracle 11g database, but it does not support UTF8 character set. Now we have received a file which contains records with Trade Mark symbol (special character). Now when we are trying to load the record, it is getting loaded with a sign "?". As a result when we try to display record in web application, the application is not able to show the records properly.

View 5 Replies View Related

Precompilers, OCI & OCCI :: Handling Runtime Errors For Host Arrays?

Aug 4, 2008

I am trying to update a table with a MERGE command using a Host Array and I am getting a unique constraint violation. According to the Oracle documentation "sqlerrd[2]holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of sqlca.sqlerrd[2] is undefined, with one exception. If the error occurred during an array operation, processing stops at the row that caused the error, so sqlca.sqlerrd[2] gives the number of rows processed successfully."

So when I catch the error and try to print out the offending row in the host array, it points to the incorrect row. If I change the MERGE command to just an INSERT, sqlerrd[2] now points correctly to the row in error. Is there an issue with MERGE in this respect? How can I get the correct row with MERGE?

View 2 Replies View Related

Server Utilities :: SQL Loader Handling Different Date Formats In Same CSV File?

Jul 11, 2011

I have another interesting SQL Loader issue. I have created 2 prior topics RE: "Trapping SQL Loader summary counts" and "Using Sql Loader issue". This new issue is along the same lines, but unfortunately the same input csv file has a date field that is has a different input format..

The 10G Oracle Table looks like...
---------------------------
CREATE TABLE PTLIVE.MODULE_CSV_LOADS
( MODULE_ID NUMBER (20),
MODULE_TAGNUMBER VARCHAR2(100),
MODULE_SERIAL_NUMBER NUMBER (20,0),

[code]...

My initial control file looks like...
-----------------
OPTIONS (SKIP=1)
load data
infile 'J:GrowerRound_ModulesCrop2011ProcessedBatch_2011Jul12_081326_746563.csv'
BADFILE 'J:GrowerRound_ModulesCrop2011LogsBatch_2011Jul12_081326_746563.bad'

[code]...

The input csv data file (for this example) contains 3 records. The first is a heading record that is skipped. The 2nd record
is correct and the field entitled "GMT Date" contains a value of
"16/05/2011". The 3rd record, however, has a date of "2011/5/18", which get rejected by the above control file, as the output SQL Loader log indicates...

Record 2: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS, column DATEPICKED.
ORA-01861: literal does not match format string

Now we will be receiving literally hundreds of these csv files and in testing I have found that when I open the csv file (the default is Excel), and Excel must alter its display, as all date appear 100% okay. However, upon opening the csv file with Wordpad, I discovered the dates in the same file had these 2 different formats. So the control file was attempting to concat the input csv file "GMT Date" in one format with the input "GMT_TIME" to load the output value into the Oracle table column
"DATEPICKED" ... but different date formats cause some records to be rejected.

It would be super if SQl Loader could use a IF clause or an OR clause to execute loading the date in one or more input formats. We only expect the 2 foramts DD/MM/YYYY or YYYY/MM/DD....however, there could be 6 different combinations in theory.

I thought about writing a Function or Procedure to analyst the input date and output a standard one to load into the Oracle column

View 11 Replies View Related

Client Tools :: Handling DATETIME Format Using SQLPLUS COPY Command

Jul 29, 2012

I am using the SQL*PLUS COPY command to move the data from my database to another remote database. The data in my database also contains DATETIME format. But since COPY command cannot handle DATETIME format, I am wondering is there any workaround for this.

Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.

View 12 Replies View Related

How To Use Member Procedures

Dec 13, 2009

create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)

[Code]....

Now how to use the change_name procedure to change the name of the employees in the table?

View 1 Replies View Related

PL/SQL :: Drop All Procedures At One Go?

Aug 29, 2012

I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.

ORACLE 11G
I am using sql developer 3.2 version tool

View 6 Replies View Related







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