PL/SQL :: Exceptions In WHILE Loop

Jul 20, 2012

I want to do something like this

-------------------------------------------------------------------------------------------------------
While Condition
LOOP
BEGIN
Insert into table1 values(......);

[Code]...

EXCEPTION
When OTHERS THEN <capture the error while inserting into an Error table>
END;
END LOOP;
-------------------------------------------------------------------------------------------------------

Now I want that If one of the insert statement within the loop fails (say table2) the exception should be captured and next insert statement (table3) should be executed.

How can I do this ? I guess I wont even need a loop

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Handle Multiple Exceptions

Apr 11, 2012

I am having a pl/sql code with two blocks namely one inner block and outer block

declare
v_v1 varchar2(10);
v_v2 varchar2(10);
v_v3 varchar2(10);

[Code].....

from the above code I want to execute both the inner block exception and outer block exception and is there any way to pl/sql engine that execute the outer exception first and inner next

View 7 Replies View Related

Intermittent Exceptions In The Logs?

Jul 14, 2010

java.sql.SQLException: Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_RMERR start() failed on resource 'weblogic.jdbc.jta.DataSource': XAER_RMERR : A resource manager error has occured in the transaction branch
javax.transaction.xa.XAException: Unexpected error during start for XAResource 'EOD': null
at weblogic.jdbc.wrapper.XA.createException(XA.java:103)
at weblogic.jdbc.jta.DataSource.start(DataSource.java:765)
at weblogic.transaction.internal.XAServerResourceInfo.start(XAServerResourceInfo.java:1182)
at weblogic.transaction.internal.XAServerResourceInfo.xaStart(XAServerResourceInfo.java:1115)

View 3 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 :: Can Insert A Table In Case Of Exceptions

Aug 14, 2009

Is it possible to insert a table in case of exceptions. For example, i have a function like this:

Create or replace Function test_function(p_Parameter_ID number) Return number is

l_Variable number;
Begin
Select output into l_Variable from test_table where id=p_Parameter_ID;
Exception when others then
Insert into exception_Table values('No Data Found', p_Parameter_ID);
return l_Variable;
End test_function;

In this function, if you notice, i wanted to populate an exception table for further analysis in case data is not found. The function gets compiled without any error in this case.However when i execute this and the Select query does not return any output for a particular parameter_ID, then the exception part fails. It gives me an error "cannot perform a DML operation inside a query".

Can't i write insert statements inside the exception section?

View 39 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 :: How To Put Exceptions While Defining Summary Item

Sep 20, 2013

how to put exceptions while defining the summary item ? In one of my datablock there are certain lines whose "inactive_flag" gets a "Y" value on certain events. I want my summary item to exclude such amounts from summing up.

View 3 Replies View Related

SQL & PL/SQL :: Declaring Variables In Exceptions Block Globally

Aug 23, 2011

I have a PL SQL program block as shown below,

create or replace
procedure except_handle
is
v_errorcode VARCHAR2(10);
v_errormsg varchar2(200);
constr_violation Exception;
PRAGMA Exception_Init(constr_violation,-2292);
[code]..........

Here, I have assigned values to the variables 'v_errorcode' and 'v_errormsg' directly inside the exception block so that I can use them in both exception types 'constr_violation' and 'others'. I am getting an error message like,

Error(22,1): PLS-00103: Encountered the symbol "V_ERRORCODE" when expecting one of the following: pragma when

View 6 Replies View Related

SQL & PL/SQL :: User Defined Exceptions For Insertion Update Queries

Sep 7, 2012

how to define user defined exceptions for cases like, ==> when anyone tries to insert string values without using single quotation marks " '...' "? ==> update the column which is not present in table.

how can I define user defined exceptions for such cases?

View 3 Replies View Related

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

SQL & PL/SQL :: Best Way To Loop Against CSV

May 25, 2010

I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do

DECLARE
cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ;
BEGIN
for i in c
loop
.......
end loop.
END;

But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?

View 4 Replies View Related

SQL Concatenate Using A Loop

Feb 17, 2010

I have a table which I will call 'fruit basket' which contains the following data:

basket_ref, fruit, fruit_serial
1, apple, 1
1, banana, 2
1, pear, 3
2, apple, 1
2, lemon, 2

What I want to produce is a table in the following format

basket_ref, all_fruit
1, apple banana pear
2, apple lemon

There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.

View 2 Replies View Related

SQL & PL/SQL :: Going To Next Record In Loop

May 25, 2011

I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.

you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.

If they are NOT, the value of v_mismatch is set to 1

What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?

OPEN c_distMatrix;
LOOP
FETCH c_distMatrix INTO r_dist;
EXIT
WHEN c_distMatrix%NOTFOUND;
--compare each field and update the counter
[code].....

View 5 Replies View Related

SQL & PL/SQL :: How Does Loop Works

Jun 27, 2011

[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;

Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.

View 9 Replies View Related

SQL & PL/SQL :: Getting Next Data With Loop

Jun 14, 2013

This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its address.

However, I want to execute this query for all owners so it should jump the previous owner it found. Lets say for that example that the loop ends at 4.
How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...also I should increase the sequence value for each situation

SELECT
DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Lock On Loop

Aug 28, 2012

begin
for i in 1..10000 loop
update table1
set col1= col1+1
where type =1;
commit;
end loop;
end;

My question is, if a strored procedure contains the script above and the said procedure will be invoked by two or more sessions at the same time. Does it mean that the 1st session will lock the related rows and other sessions will have to wait for loop in session one to finish?

View 18 Replies View Related

SQL & PL/SQL :: Getting Error By Using NVL() And Loop

Sep 22, 2012

Declare
x number;
Begin
Loop
if NVL(x,1)>=1 then
[code].......

May be this code is going into an infinite loop. I'm unable to understand where the problem is?

View 6 Replies View Related

SQL & PL/SQL :: Cursor For Loop

Jul 30, 2012

When a cursor returns no records, how to handle this in Cursor for loop..

I had given sample code for handling this by using normal open,fetch statement, provide by applying for -Cursor For loop

DECLARE
P_BU_ID NUMBER;
P_SUMRY_DATA_TY_ID NUMBER;
P_OP_DATE VARCHAR2(32767);
P_PROC_DATE VARCHAR2(32767);
P_FIN_YEAR NUMBER;
[code]........

View 3 Replies View Related

SQL & PL/SQL :: Cursor FOR Loop?

Jun 15, 2012

I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.

In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,

ID Year Amt ANSWER
1 2010 200 200
1 2011 ..... 200
1 2012 ..... 200
2 2009 300 300
2 2010 ..... 300
2 2011 450 450
2 2012 ..... 450

View 5 Replies View Related

PL/SQL :: LAST Record In A Loop?

Aug 27, 2012

in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,

gfsfsf gsgfsfds gsdgfdg
------------------------------------
edyet gdgtdgt gtdfdfdgd
------------------------------------
dfds hedhgg idudhdh

how can i achieve this within pls/sql procedure body.

View 20 Replies View Related

VPD Function With Loop - Oracle 11g

Jul 5, 2012

I have the following function:

CREATE OR REPLACE FUNCTION get_project_id(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
IS
projects_pred VARCHAR2 (400);
[code].......

I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.

View 6 Replies View Related

Loop Inside Cursor

Sep 11, 2012

I have a cursor returning some value.

for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).

E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)

I need to go to all of them and take some values and move them to a flat file. how should that be done?

Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9

View 1 Replies View Related

Loop Back Entry

Jun 1, 2013

How the loop back entry in /etc/hosts relates to listener?

View 1 Replies View Related

SQL & PL/SQL :: Update Two Table Using For Loop?

Feb 19, 2010

I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.

--for r in (( select (table2.y - table1.y as x from table1, table2 where table1.x = c and table2,.x = m))
declare
i number := 1;
c number ;
m number;

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Loop Through Records Using Block?

Nov 8, 2011

How do I loop through a Input parameter (varchar_table) and pass the input value to a select query.

Procedure Test
(
param1 IN dbms_sql.varchar2_table
)

[Code]....

1. How do I define temp_tbl and is it a best practice to use temporary table - if not what is the best method to do the same.

View 5 Replies View Related

SQL & PL/SQL :: Have Multiple Variable In For Loop

Apr 5, 2010

Is it possible to have multiple variables in a for loop. For expample:

FOR i AND j in 1..10

I was wanting to increase one of the variable more than the other.

View 8 Replies View Related

SQL & PL/SQL :: Inner And Outer Loop In Cursor?

Jul 9, 2013

BEGIN
FOR i IN 1 .. 10
LOOP

[Code]....

Assume the above is my code; there is inner and outer block.

Currently if inner block is failed, the outer block also going to exception block

But, my requirement is if inner block is failed it should not go to outer block, still the loop should continue and print 1...10 rows

View 9 Replies View Related

SQL & PL/SQL :: Loop In UTL_FILE Is Not Getting Closed

Nov 1, 2013

I am trying read a File to find out the error message through UTL_FILE.m. But the loop used inside is not getting closed.

CREATE OR REPLACE procedure alert_mail_A
as
v_flag varchar2(10);
mesg varchar2(100);
c1 utl_smtp.connection; -- passing the datatype to a variable.
c varchar2 (1000);
B utl_file.file_type;
[code]........

View 1 Replies View Related







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