Hierachial Dyanset - OO4O and Wizards(Archived)

Whats the Oracle equilevent of the ms shape command?
I need to build a XML doc from my dynaset, then XML
doc has nested elements, sort of like a parent/child
scenario. Until know I have been looping through one
dynaset and using values from the current record to
open another dynaset inside the loop and then loop
throught that dynaset. Isnt there a better way of
doing this?


XML Import for End-User

I need an application where End-User can upload a XML-File which should be imported into the database.
Therefore I need a process to import the data into the different rows of a table.
Have anybody some experiences with importing XML-Files?
Would this process be complex?
Thank you,
Is there nobody who has any idea, or can give me advices if it is possible?
I would be very glad for any help! 
Hello Tim,
In the last few days I've played a bit with manipulating XML files from within Apex. I'm using "Oracle XML DB Developer's guide" (available as part of the DB documentation) as my main reference. For my, pages 3-5 and 3-6 seems to be the key.
In my case, I need to extract some specific nodes from the XML file, and insert them into some DB tables. I'm uploading the XML file into a table with PK and XMLType columns, and then extracting the data using the extract() and extractValue() XMLType functions. So far, it seems to work fine.
Hope this can give you a starting point to what you need.
Hey Arie,
thanks for your reply!
It seems to be not so difficult to solve my problem!
But there are a few general questions:
Is the XML DB an included feature of a 9i database?
And when I want to insert the XML file into the database, are they stored as XMLType columns, or can I convert them into "real" database columns?
Can you publish an example application in which you can show me how it could work?
That would be very nice, because I am still a beginner.....
Thank you!
Hello Tim,
>>" It seems to be not so difficult to solve my problem!"
"Not so difficult " is a relative term…>> "Is the XML DB an included feature of a 9i database?"
Well, as far as I can tell, XML DB is part of 9iR2. I don't know about earlier versions.
>> "And when I want to insert the XML file into the database, are they stored as XMLType columns, or can I convert them into "real" database columns?"
There are two phases in using the XML data (at least in the way I do it). First, you need to upload the file into the database. In this stage, I'm using a table with XMLType column, so I'll be able to use the extractValue() function. In my case this table also include "regular" column, as a PK, so I'll be able to easily locate the proper XML document I need. In the second phase, I'm extracting the specific nodes I need from the XML document, and insert them into what you call "real" database columns.
>>"Can you publish an example application …"
As I stated in my first post, I'm also just starting to play with it. I'll try to see over the weekend if it is possible to post something on apex.orcale.com. I know that the public site does not always support all the DB and Apex options (mainly for security reasons or lack of privileges). I'll see what I can do, and post an update if I'll succeed. In any case, the core code I'm using is in the user's guide pages I mentioned in my first post.
Hey Arie,
thanks for your advices!
I started to try this....
I created a form to upload the xml file and store it in an XMLTABLE.
But there is this error:
ORA-22285: non-existent directory or file for FILEOPEN operation
This was the code I use to create the table,the function and the insert statement:
create or replace function getClobDocument(
filename in varchar2,
charset in varchar2 default NULL)
return CLOB deterministic
file bfile := bfilename(’DIR’,filename);
charContent CLOB := ’ ’;
targetFile bfile;
lang_ctx number := DBMS_LOB.default_lang_ctx;
charset_id number := 0;
src_offset number := 1 ;
dst_offset number := 1 ;
warning number;
if charset is not null then
charset_id := NLS_CHARSET_ID(charset);
end if;
targetFile := file;
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,
DBMS_LOB.getLength(targetFile), src_offset, dst_offset,
charset_id, lang_ctx,warning);
return charContent;
-- create XMLDIR directory
-- connect system/manager
-- create directory XMLDIR as ’<location_of_xmlfiles_on_server>’;
-- grant read on directory xmldir to public with grant option;
-- you can use getCLOBDocument() to generate a CLOB from a file containin
-- an XML document. For example, the following statement inserts a row into the
-- XMLType table Example2 created earlier:
I think the problem is, that I want to store the XML File with a FileBrowser.
Is it correct that the function needs a physical direction of the file which should be stored?
Do you have an idea?
So long, I wish you a nice weekend!
Hello Tim,
I saw your post in the XML DB forum, so I assume you still need some help on this. I've encountered some XML issues of my own, but now it seems to work as I need.
If I understand correctly, from all your forum threads, all you need to do is to extract some values from the XML file, and insert them into "regular" tables (in oppose to the need to manipulate the XML document itself, after loading it to the DB). In this case, you don't need to store your XML file as CLOB. Hence, you don't need to use your getClobDocument function. You can use the bfilename() function, which can handle external (OS level) files.
You chose to use XMLType table. That can be OK, but if you'll have several XML files do deal with – as in my case – I think it would be easier to add a reference column, to identify the XML document. The XML document itself will be stored as an XMLType column. The table I created looks something like that:
    create table XML_SOURCE (
    xml_ref     varchar2(15) primary key,
    xml_doc     xmltype )Before you can start uploading XML document into the table, you need to define a directory object, which will point to the OS directory location of your XML files. It should look something like: CREATE OR REPLACE DIRECTORY xmldir AS 'K:\XML';
Now you are ready to upload the XML files. In my case, I let the user insert the XML file name, and use it as a reference. If the page item is 'P1_XMLNAME', I'm using an After Submit Process, with the following pl/sql statement:
insert into xml_source values (
   :P1_XMLNAME, xmltype
);( The nls_charset_id is the character set of your XML document, NOT the database charset).
The next step is to extract the values from the XMLType column, and inset them into "regular" columns. There are several ways to do that. You can choose to extract the XML values and insert them directly into the proper tables. You can see several examples for that in the XML DB user's guide. In my case, I need to extract the XML values, and display them within a form, to be inspected by the user. There are several functions that can help you extract the XML values, and you need to choose them according to the structure of your file. Here, is an example of one process that works for me. I defined several text items on my form, and I'm using a Before Header process to populate them with the XML values:
   extractValue(xml_doc, '/main/row/item2'),
   extractValue(xml_doc, '/main/row/item3'),
FROM xml_source
WHERE xml_ref = :P1_XMLNAME;This code is for a single collection XML file, with element values. If you have multi collection files, and/or attribute values, you'll need to adapt the extract functions to the specific file structure.
It came out a bit long, but I hope it will help you find your way (writing it, certainly help me organize my thoughts).

How to read XML message present in Table using PL/SQL?

How to read XML content present in Table using PL/SQL .And is it possible to parse the xml uisng xslt and insert xml output in same table again ?
Late reply, but hopefully better late than never.
You can possibly do it all via a single SQL statement, such as {message:id=4232077}
XMLTable Syntax can be found at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions228.htm#CIHGGHFB (for 10.2 users) else find your correct version at http://www.oracle.com/technology/documentation/index.html

Dynamic Split Join:Parallel flow using OSB

I am implementing a dynamic split join in OSB,where in the for -loop i am invoking a database adapter.
If I select parallel='yes' in the for-loop settings, I get a random number of records from the database as an output.
While on the other hand, on running the for-loop sequentially, i get the exact number of records i am expecting.
Could anybody figure out why is this happening?
If I understand correctly, this is what you are doing:
Configure a For loop based on a repeating element in source XML (for ex. for each customer id in xml message).
Inside each loop you are making a DB lookup and getting one record for each element (for ex. customer details of each customer id)
Finally you are aggregating them in the same variable (join)
The problem that you see is that if you do it in parallel then you do not see details of all customers in the out variable?
If yes, then can you please let me know how you are updating the details of each record in the out variable. 
Also can you check scope defined for output record variable structure which stores record from each parallel query execution?
This variable needs to be defined locally within scope of for loop so that it can hold different instances for each parallel thread. 
like ankit said i think this has got to do with the defining of the variables and the scope
i had the same issue, but can't really remember how i fixed it.
so try to skip the call to the db but just log "inputvariableRequest" of your db call in the looping to see if it got the correct values
is this variable defined in the "looping-scope" itself?

Problem Using XMLTYPE

After much fiddling I have managed to write some PLSQL that allows me to add elements and attributes to any node in an XML document using XPath to find the parent. BUT the process of using extract to get the parent node, converting it into a DOM, appending a chils and then using UPDATEXML to update the original document is 300 times slower than using string manipulation!!!
Can we have a SelectNode function in the DOM or an appendNode function in XMLTYPE
Hi Peter,
Iam not sure of the functions you wnat.. but to increase the speed you can try creating index on the nodes in the XMLType you are using. You can even try creating Oracle Text Index.
Check the Survey Sample Application which uses Oracle Text Index to increase the performance.
The sample is present at the following link:
And a tutorial on how Oracle Text is used in the application is present at
Hope this helps.
I am not storing the XMLTYPE in a table.
What I want is the ability to add/delete etc nodes to an XMLTYPE directly
where parent is an XPATH string 
YES!!! I am looking for the same thing. How to manipulate XMLType as stand alone variable (not a value of column in table). I am looking for smth like selectSingleNode, selectNodes as in MS XMLDOM.

Pass Cursor/Array to Stored Procedure

Hi All,
I am trying to find, if I can pass an array or a DataTable/Cursor from a VB.Net/C# program to an Oracle Stored Procedure or not. I know we can do that from one Stored Procedure to another in Oracle.
I appreciate, if anyone can throw some light or give any kind of directions if this is possible.
Thanks in Advance
- Jaideep
An array can be passed as a parameter to a stored procedure using the Array Binding feature of ODP.NET.
For more information refer:
How-to achieve this refer:
How can this be applied to updating multiple rows in one batch and also - within the stored procedure is there anyway to access each row individually.
My requirement is to pass multiple rows from a datagrid to an Oracle stored procedure, each row may be an insert or an update and so I woudl either run an INSERT or UPDATE. Also, other actions must be taken depending on some of the values within each row. I want to so this all in one trip to the database rather than multiple trips for each row, or one trip for the insert/update and othe rmultiple trips for the related actions. Is this possible? 
You could do the processing on the server-side with PL/SQL. Just pass all the update values in an array, then build the PL/SQL logic to figure out if you want to do an insert or update.
If you are executing the same INSERT or UPDATE statement repeatedly, you may want to use parameter array binding feature. This is documented in Chapter 3 of the ODP.NET doc right after the PL/SQL associative array section. 
Hi mctears1. You should be able to use the OracleDataAdapter class to do this. I am not sure if it handles everything in one round trip. Also, if you would prefer to do this in PL/SQL, you might find the MERGE command helpful with determining if you should do an INSERT or an UPDATE.
-- Matt 
Hi Jaideep,
Did you ever get an answer to this question? I'm doing the same and wanted to find out how you achieved this thru C# and ref cursor.
Here is what I am suppose to do -- data from C# datatabes will have to be passed to oracle procedure and i have to do an insert or an update.
Any help is appreciated.