-------------------------------------------------------------------------------------------------------
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
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
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)
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
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; /
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?
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
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.
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
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?
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;
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?
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.
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].....
[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.
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
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?
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,
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,
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.
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;
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]........
