JDeveloper, Java & XML :: Extracting Values - Not Returning Message ID

Aug 27, 2013

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.

View 3 Replies


ADVERTISEMENT

JDeveloper / Java & XML :: Extracting Values From String

Aug 11, 2012

I would like to extract only the values from the XML string. The xml string looks like

<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>

And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA

I need to extract only the values from the XMl string using sql.

View 4 Replies View Related

JDeveloper, Java & XML :: Convert XML Message To CSV And Assign It To (anyType) Element

Mar 28, 2012

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 Related

JDeveloper, Java & XML :: Select Entire Message From CLOB - ORA-22806

Jun 20, 2013

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.

View 1 Replies View Related

JDeveloper, Java & XML :: Extracting Sub-nodes From XML With Multiple Nodes?

Oct 27, 2011

how to extract sub nodes from multi nodes in this file.

<?xml version="1.0" encoding="utf-8"?>
<Orders>
<LeasingCoNum>123456</LeasingCoNum>
<DateTime>10/05/2011 10:00:00 AM</DateTime>
<NewOrders>
<Order>

[code]....

View 9 Replies View Related

JDeveloper, Java & XML :: Read Some Values From XML Code

Sep 3, 2013

I would like to read some values from XML code, I try to do some test sql query but not successfully.The values what I need from xml are:

<currency>EUR</currency>
<amount>64.93</amount>

And there is a one example of xml:

CREATE TABLE "XML_TAB"
("ID" NUMBER(10,0),
"FILENAME" VARCHAR2(100 BYTE),
"XML" "XMLTYPE"
)
[code]..

how to write sql which get me a needed values?

View 13 Replies View Related

JDeveloper, Java & XML :: How To Return Multi-values To PL/SQL

Jul 3, 2013

I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).

I will show pl/sql function..

CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;

[code].....

View 26 Replies View Related

JDeveloper, Java & XML :: Get Values With Two Columns And Pass It To Informatica To Frame

Aug 29, 2011

I have a scenario to frame an xml as below, below i have given a sample data

Create table xml_type
(msg varchar2(1000),
desp varchar2(1000),
val number) ;

[Code]....

Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query. How to get from an sql query.

<DETAIL>
<ERROR> 'Invalid Name ID' </ERROR>
<ID> 'Name ID' </ID>
<VALUE> 6 </VALUE>
<VALUE> 7 </VALUE>
<VALUE> 8 </VALUE>
</DETAIL>

[Code]...

Or i was planning to get the values as below with two columns and pass it to informatica to frame the XML.

Col1 Col2
Invalid Name Id Error
Name ID ID
6 Value
7 Value
8 Value
Invalid FAQ ID Error
FAQ Id ID
9 Value
10 Value

Let me know the best option and how to do this task.

View 1 Replies View Related

PL/SQL :: Extracting Data From XML Message

May 15, 2013

Currently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :

Message Reference     UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123               >(Cancelled)</UploadError>
456123               >4561</UserId>
456123               >1234</SecurityIdentifier

At the moment, I am extracting the required info as follows.

Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header

select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)

Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))

Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.

select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)

View 13 Replies View Related

JDeveloper, Java & XML :: Using Java.lang.Math In Oracle / ORA-31011 / Parsing Failed

Apr 26, 2013

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.

View 1 Replies View Related

JDeveloper, Java & XML :: Java Stored Procedure Invoking Windows Run Time Process

May 22, 2012

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.)

View 8 Replies View Related

JDeveloper, Java & XML :: Java.sql.SQLException - Execute Batch - Exception Equal To 1?

Jul 14, 2011

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 Related

JDeveloper, Java & XML :: Calling Java Stored Procedure From Oracle

Jun 20, 2011

I 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?

View 1 Replies View Related

JDeveloper, Java & XML :: Create Or Replace And Compile Java - Getting Ora 29531?

Dec 21, 2011

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

View 8 Replies View Related

JDeveloper, Java & XML :: Best Driver For Java To Oracle Database Connection

Jul 30, 2012

Which is the best driver for java to Oracle database connection ?

View 1 Replies View Related

JDeveloper, Java & XML :: Call DB Function From Java Hibernate

Feb 10, 2011

I 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"

View 1 Replies View Related

JDeveloper, Java & XML :: How To Pass Any Array From Java To Oracle

Feb 4, 2005

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 ?

View 11 Replies View Related

JDeveloper, Java & XML :: Native Java In Oracle Versus PL/SQL?

Aug 26, 2011

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 Related

JDeveloper, Java & XML :: Using PL/SQL Function To Execute Java Class?

May 20, 2013

i`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

View 1 Replies View Related

JDeveloper, Java & XML :: Connection Between Java And Oracle

Nov 26, 2012

I Program with Java . when we connect oracle by program ,

Get the following error :

'No suitable driver found for jdbc:orcl:thin:@localhost:1521 '

View 3 Replies View Related

SQL & PL/SQL :: Extracting Values Acquired Via UTL_TCP?

Jul 26, 2010

Using UTL_TCP package (for the first time; maybe there's smarter way to do that), I captured contents of a certain web page. The page contains (along with some text, images, etc.), a list of values I'd like to extract. Something like this:

PRVI miravira pond 23.7.2010 102,2221
NEKI miravira pond 23.7.2010 105,0996
DRUGI miravira pond 22.7.2010 101,3789

The result (output) of the PL/SQL procedure contains several hundreds of lines. I narrowed the output to a part which I'm interested in. It looks like this (a screenshot, because [code] or [pre] tags won't allow me to paint text, while the others don't preserve formatting):

Red values are what I'm looking for. The first one represents a date (July 23rd 2010), and the second one is amount (105,0996).

Now, what's the problem: the above mess doesn't look the same every day (probably because the rest of data that appears on a web page changes too). Therefore, SUBSTR (with hard-coded positions) that seems to be working today is wrong tomorrow. Moreover, IF condition I used might not provide desired part of a web page every time.

Here's the code (modified a little bit, unimportant for what matters):

declare
c utl_tcp.connection;
n number;
buffer varchar2(255);
l_x_dat varchar2(10);
l_x_izn varchar2(10);

[code].....

L_X_DAT and L_X_IZN are values I need. SUBSTR parameters, as I said, are far from being good.Is there any smart way to extract those two values? What (Oracle) technology could be used here? Or should I just try to, somehow, set SUBSTR values correctly (dynamically)?

If there was a way to uniquely fetch those values (instead of getting the whole web page and digging for what I'm interested in), it would be extraordinary.

View 11 Replies View Related

SQL & PL/SQL :: Returning Row With Same Values

Mar 22, 2012

create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)

My requirement is to return

id key
1 10
1 10

because both their id and key are same

View 2 Replies View Related

JDeveloper, Java & XML :: Parsing Through PL/SQL

Nov 21, 2011

oracle 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]...

View 3 Replies View Related

JDeveloper, Java & XML :: How To Get Thread ID

Dec 18, 2010

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 Related

JDeveloper, Java & XML :: How To Get Single Tag Out Of XML

Jul 14, 2011

We 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].......

View 10 Replies View Related

JDeveloper, Java & XML :: Read Value From XML

Oct 4, 2011

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].....

View 1 Replies View Related

SQL & PL/SQL :: Returning Values With MORE Option

Jan 19, 2012

Imagine I have the following scenario:

CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');

Imagine I want to retrieve those records in only 3 distinct rows, like this:

Munich
Madrid
Others

Is there any way I can do this with an simple Statement?

View 6 Replies View Related

JDeveloper, Java & XML :: Accessing XSD File Through PL/SQL

Feb 9, 2011

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.

View 1 Replies View Related

JDeveloper, Java & XML :: Updating CLOB Value

Dec 19, 2011

I 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.

View 6 Replies View Related

JDeveloper, Java & XML :: Generating XML From XSD At Runtime?

Aug 13, 2012

I Have got a requirement where we have to generate an xml conforming to a particular XSD.

What i will be having already in place is an XSD document.

We are thinking of making a procedure which will be recieving the set of inputs required for creating that xml.(Data content of that XML.)We would have to make the XML according to that XSD.

Everytime Parsing the XSD is not feasible.We are looking at a solution which would be easy to make the xml from xsd at runtime.These values (DATA) will be available at run time.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved