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
ADVERTISEMENT
Mar 18, 2013
Below is the XML i have :
<?xml version="1.0" encoding="UTF-8" ?>
- <Transaction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.test.com/Support/Services/test1/2012" xsi:schemaLocation="http://schemas.test.com/Support/Services/test1/2012 Support.test1.v1.xsd">
- <Request>
<Memid>10</Memid>
<Actid>32</Actid>
[code].......
I want to extract the element values :
The below code will be useful when i have no namespace , what must be done in order to work with namespace and extract element value
v_string_xml :=
' //Request[' || TO_CHAR (counter_xml) || ']/Memid/text()';
v_ssp_table (v_ssp_table.COUNT).memid :=
p_xml_in.EXTRACT (v_string_xml).getnumberval ();
v_string_xml :=
[code].......
View 2 Replies
View Related
Nov 26, 2010
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
View 6 Replies
View Related
Sep 24, 2010
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
May 17, 2010
oracle 10g to select column from about 8 table . I start with this statement
select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h
from A
full outer join B on(A.a=B.b)
full outer join C on(B.b=C.c)
full outer join D on(C.c=D.d_
.
.
..
.
.
.
View 12 Replies
View Related
