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
ADVERTISEMENT
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
Apr 22, 2013
I am trying to find the best solution for the following problem
I have a table called HIERARCHY with 3 columns.
col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8
I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.
For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.
How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!
View 5 Replies
View Related
Oct 2, 2010
presenting the case as follows:There are two dates with time components like
26-sep-2010 13:00 and 29-sep-2010 19:00
In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.
Procedure, cursor solution is not needed and need single select statement.
View 5 Replies
View Related
Oct 17, 2012
I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
View 9 Replies
View Related
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
Sep 5, 2012
I am studying about these two technologies and the only difference that I found was that optimization doesn't backup the duplicated archivedlogs.
If both ignore not changed blocks, what is more effective?
The optimization backup also has some tracking file?
View 1 Replies
View Related
Sep 10, 2011
does, block change tracking file is read during incremental level 0 backup?
View 4 Replies
View Related
Jun 20, 2012
i want to enable block change tracking as our incremental level1 backup take a lot of time but i have a worry which i found it on oracle support and tha is as follows Database Instance Hang at Database Checkpoint With Block Change Tracking Enabled. [ID 1326886.1]
View 2 Replies
View Related
Oct 11, 2011
i did such commands
1. RMAN -> backup incremental level 0 database;
2. SQLPLUS -> alter database enable block change tracking using file '/home/oracle/block_change_tracking.tr';
3. RMAN -> backup incremental level 1 cumulative database;
4. RMAN -> backup incremental level 1 cumulative database;
5. RMAN -> backup incremental level 1 cumulative database;
and for such order i noticed that block change tracking file wasn't used for any cumulative backup. for each cumulative backup was done full scan over database.
it was used after i did incremental level 0 backup, and again incremental 1 cumulative.
if i want this feature to be usefull i should enable it before my incremental level 0, or i should do incremental level 0 immediately after i enable it?
View 6 Replies
View Related
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
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
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
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
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
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
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
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
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
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Apr 22, 2010
My form consist two datablock cust_mstr,cust_dtls, each customer code consists two or three contact details records, when i delete the single detail record instead of deleting single record it deletes all the details records from cust_detail table.
Here by i attaching my form for your reference pls find the code in delete button
View 12 Replies
View Related
May 30, 2010
I have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:
MasterItem1 DetailItem1 DetailItem2 DetailItem3
I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:
mitem1-value1 ditem1value1 ditem1value2 ditem1value3
mitem1-value2
mitem1-value3
mitem1-value4
When the navigate down in master items, then the corresponding detail values are being shown on the same first record.
View 1 Replies
View Related
Mar 28, 2011
The main qn is in the subject line.
I have used the following code
GO_BLOCK('CHILD_BLK');
FIRST_RECORD;
LOOP
IF NVL(:CHILD_BLK.SELECT_FLAG,'N')='Y' THEN
V_REC_NO := GET_BLOCK_PROPERTY('CHILD_BLK',CURRENT_RECORD);
SET_ITEM_INSTANCE_PROPERTY('CHILD_BLK.ASSET_DESC',
V_REC_NO,
[code]....
View 5 Replies
View Related
Jul 10, 2012
The block size of my db(10gr2) is 8k, and the db is on the raw device file system on a aix machine.
OS block size is 512k.
How much block will be read from disk when a single oracle io call occurs? Which one is currect, 8K or 512K?
If the block size for a single io call depends on the os block size (512k), I think os block size need to be tuned for oracle block size (8k). If we use raw device file system, os level block size has no meaning?
View 1 Replies
View Related
Jul 12, 2010
I have a set of sql statements which i have to execute inside a pl/sql block. But i need to know the response of each statement and confirmation whether it is executed successfully.
Practically i need to get info as such in sqlplus status msg for each sql statement
E.g. :
SQL> insert into test values(1);
1 row created.
or similar to this.
View 7 Replies
View Related
Jul 12, 2012
I execute the below code in TOAD.
DECLARE
CURSOR c1
IS
SELECT *
FROM tab
WHERE ROWNUM < 5;
[code]....
If I add another FETCH INTO statement in the while loop block ,I will get the output.why I am getting this error exactly and how another FETCH INTO is preventing it.
View 10 Replies
View Related
Feb 7, 2011
1) SQL Statements are not using IMPLICIT CURSORS.
2) Only the SQL statements of the PLSQL program create implicit cursors.
View 1 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
View 3 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Mar 5, 2012
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
View 8 Replies
View Related