I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query
select c.cc_data_area.getClobVal() as cc_xml
from ci_cc c;
This query returns an ORA-22806: not an object or REF
cc_data_area is the clob column, and contains messages longer than 4000 characters.
I am having two XML's in my table.
XML1 =
<?xml version="1.0" encoding="UTF-8" ?>
<Twist xmlns="URL...." xmlns:ns2="dsig:URL....xmlns:dsig="URL....
xsi:schemaLocation=URL....
xsi:type="ElectronicBillingMsg">
<messageId>TWISTMSG</messageId>
</Twist>
[code]...
I am using the below query to extract value from XML.
SELECT extractvalue(xml_column, '/Twist/messageId') MsgId FROM mytab;
When i run this query on XML2, it gives the output.But when the same query is fired on XML1, its not returning the message id.
I have a requirement that is to convert XML message to CSV format and assign this to a "anyType" element. How would I convert to CSV?
View 1 Replies View RelatedI have the following xml in a CLOB Field
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<TuningDescription xmlns="http://www.alcatel.com/2005/MUSE/Tuning/maatTuningDefinition">
<tuningSession>
<TuningSessionProperties creationDate="2011-11-09+05:30" lastUpdate="2011-11-09+05:30"
owner="osmadmin" recommendedDataOfApplication="10/11/2011" sourceTuningSessionId="TS_1" tempTuningSessionId="TS_1_TMP_1" tuningSessionName="TS1"
tuningSessionOverview="Parameter Tuning Modified:CELL3G: 1,
[Code] ......
I have to look for objectId Tag in the xml and Here I want to replace objectId with another Mapping value from a mapping table
Below is the mapping table.
create table ODMAP(OID number,mapID number);
insert into ODMAP values(3230,7000);
insert into ODMAP values(3231,7001);
insert into ODMAP values(3232,7002);
I am not good in xml extraction.
I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.
SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');
The only test that I have been able to get working is the one below.
SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';
It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.
I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.
As an example this has not worked either...
WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;
So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.
Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.
===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)
[code]....
I am having an issue as to how we can resolve the error ..
ORA-22813 operand value exceeds system limits
I think XMLAGG on clob is creating this issue.
Create table statements :
Create table x
as
Select '42' id, to_clob(null) notes from dual;
[b] replace the to_clob(null) with sheet attached.[/b]
I am just attaching just one clob record for notes column.
SELECT XMLElement("Notes",
(XMLAGG(XMLELEMENT("Text", XMLELEMENT("CHUNK", Notes))))) -- Issue here..I think.
from x
How do we use XMLAGG for clob fields?? (Notes is a clob field from table x)
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
adding some value in xml type clob column, for ex: xml_tab table has more than 100 rows, below i have created some rows
Quote:create table xml_tab(id number, ex_xml clob);
select * from xml_tab;
1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old"><1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> </Year> </CData>
[Code]....
I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'
<RCA>
<SUB_PROBLEM_TYPE>
Bla bla bla...............
</SUB_PROBLEM_TYPE>
[Code]....
I wish to generate in my html form like..
PREVENTION: bla bla bla....
Is there any simple way to query out ?
I try
"SELECT T1.root_cause_analysis.query('/RCA/PREVENTION') as RCAXML from Submission_Record.............."
set rx=server.createobject("adodb.recordset")
rx.open sqlxmltest,con,1,3
while not rx.eof
<table>
<tr>
<td ><% response.write rx("RCAXML")%></td>
</tr>
</table>
BUT IT GIVE ME AN ERROR... [ORA-22806: not an object or REF]
SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE
SQL> select * from res;
RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>
how can me write results of select statement into xml file instead of show them on screen?
I got table with column of XML codes like this:
select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="result"><value>success</value></parameter>
<parameter name="showBirthday"><value>false</value></parameter>
<parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>'
) val
from dual;
And I need to select records from table where xml code complies with requirement:
name="result" equals success.
name="result"><value>success</value>
I am trying to execute an xslt (for XML transformation) using xmltype.transform function.
I can't paste XSLT code here, but below is similar what I am doing.
[URL]
I have used a reference of random() method of java.lang.Math in xslt file, and it's giving me below error.
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00607: Invalid reference: 'random'.
I doubt, it's because of "Math" class, which is not present in Oracle, how to load standard java classes.
I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.
Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:
c:javajdkinjava -cp
and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:
C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
I was getting java.sql.SQLException: executeBatch, Exception = 1when i tried to use executeBatch().could not understand why i am getting it.
View 1 Replies View RelatedI am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.
Some thing like following:
I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.
My problem is how can I issue the
SELECT details FROM CONNECTION_DETAILS for USER2
so that i can get the details..?
Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.
I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?
I've this java source create or replace and compile java source named "Decodificador" as
public class Decodificador {
public static String decodifica(String codigo)
return codigo;
and this function
create or replace function F_Decodificador(codigo varchar2) RETURN VARCHAR2 IS
LANGUAGE java NAME 'Decodificador.decodifica(String) return String';
when I execute the function the result is:
ORA-29531: no method decodifica in class Decodificador
Which is the best driver for java to Oracle database connection ?
View 1 Replies View RelatedI need to call DB function through Java Hibernate.
My Sample XML File looks like.
---------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<sql-query name="getchequeno" callable="true">
?:=call cz_nab_mw_fn_gen_ol_chq_no()
</sql-query>
</hibernate-mapping>
---------------------------------------------------------------
I am getting below error while reading the file
"java.lang.RuntimeException: org.hibernate.HibernateException: Errors in named queries: getchequeno"
Here is a problem. I have to pass an array e.g ('Account','Marketing',Computer' ) from java to Oracle 9i.
Would I be able to do in Oracle, if yes then how ?
we can you java code natively with Oracle. But I didn't get how to decide when to use native java in oracle and when to use pl/sql.
View 5 Replies View Relatedi`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.
table1 is something like this:
ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100
table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)
[code]....
Then i wanna make a pl/sql function for:
1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1
2) change i=1 to i=2 and do that same
3) end if i=101
I Program with Java . when we connect oracle by program ,
Get the following error :
'No suitable driver found for jdbc:orcl:thin:@localhost:1521 '
I need to select all names that can possibly have ether accents or french characters what is the easiest way?
View 1 Replies View Relatedoracle PL/SQL. I have almost finished this xml parsing task but their is one problem. Actually in our table there are more than 70-80 columns & due to that only I don't want to put the hard coded column name in my procedure, because if I will do that, the unnecessary procedure size will be increase(means line of code).Here is our procedure
Create or replace procedure loadMyXML(dir_name IN varchar2, xmlfile IN varchar2) AS
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl1 dbms_xmldom.DOMNodeList;
l_nl2 dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
node1 dbms_xmldom.DOMNode;
l_colName VARCHAR2(100);
[code]...
I want to do concurrency program analysis, how can I get the id of the thread when this thread started dynamic in the progress of execution?
View -1 Replies View RelatedWe have the following XML info.
<connection value="0">
<GroupDetails>
<info groupId="100" classType="0"/>
<info groupId="128" classType="1"/>
<info groupId="179" classType="0"/>
<info groupId="198" classType="0"/>
</GroupDetails>
</connection>
We need to get the single node with class type ="1" from this XML as given below.At any point of time, there will be only one such tag with classType="1". Its position could be anywhere inside the "GroupDetails" Tag.ie, The query should get the tag irrespective of its position inside "GroupDetails".
<info groupId="128" classType="1"/>
Is there any way using EXTRACT function with 3 parameter? I tried but with failure.
with rec AS (
select XMLTYPE(
'<connection value="0">
<GroupDetails>
<info groupId="100" classType="0"/>
<info groupId="128" classType="1"/>
[code].......
I need to select all records from table tmp_mape where value <loyaltyDebit id="5554431"> from xml_params is for example 5554431.
create table tmp_mape
(xml_params clob)
Example of xml
insert into tmp_mape values (
'<prolongationData mode="normal" exceptionCaseID="" xmlns="URL....
<customer status="active" xmlns:ns2="URL.....mobile.sk/client/twizard/communication/orderentry" xmlns:tns="URL....
<customerReferences>
<reference name="customerID" platform="bscs">2319375</reference>
<reference name="externID" platform="other">BSCS-PRD-CUSTOMER-2319375</reference>
<reference name="siteID" platform="clarify">S-061009-1893787</reference>
[code].....
I wanted to know whether is there any utility which can
1. Create table from xml /xsd file.
2. insert records into the newly created table, through the given xml file.