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'.
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]...
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?
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
Is it possible to take execution control back from exception handling section to Execution statement?.. If Yes then How?..
View 25 Replies View RelatedI 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.
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..
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
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] .........
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;
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.
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.
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;
/
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;
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]...
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?
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 RelatedHere'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
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.
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]...
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.
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.
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 ;
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.
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 RelatedI 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?