This content has been marked as final. Show 8 replies
AFAIK, there's no such tool for PL/SQL.
What's the generated PL/SQL code supposed to do?
XML DB already provides efficient XML loading, parsing techniques, and mappings to Oracle object and collection types (Object-Relational storage).
That's all described in detail in the documentation : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/toc.htm
A similar tool already exist for every other programing language. It would be very code efficient if at least the skeleton of the procedures is generated. Also it would make it easier to call a web service from PL\SQL .
What's the generated PL/SQL code supposed to do?
All other generated codes in other programming languages don't DO anything. They just generate definitions. Also they create client side objects which makes it easier to communicate with the server objects. In web services generating the skeleton package of the web service is ~50% of the work.
The scenario that we are facing is this : we have developed lots of web services in Java. these web services do variate of things . some of our clients have oracle solutions. we are not able to integrate our web services into there Oracle solutions because the current Oracle method of consuming the web service is to static, i.e. you have to generate the soap envelop almost manually and then send it.
so the answer in other scenarios was to generate a client in our customers systems programming language, the WSDL file is already there , its just a mater of generating the code.
similar to what the program in my firs link does, only in PL\SQL
Well, PL/SQL is primarily a server-side language to begin with.1 person found this helpful
(Oracle Forms uses it too but it has its own PL/SQL engine)
As said (did you read the links?), XML DB already implements an automatic generation of objects (similar to classes in Java, C#, C++ etc.) based on an XML schema (search for "schema registration" in the guide).
An incoming XML file can then be converted to an object type, with all its nested complexity.
The other way around, given an object instance, you can convert it back to an XML document.
Looks similar to what you're describing?
Can you please provide a valid tutorial link to XML DB, it sounds like the answer.but all the tutorials im finding are not proper.
XML DB covers a large range of topics : XML schema management, storage strategies, XQuery support, XML indexing, Native Web Services, Repository features etc.1 person found this helpful
All these topics are covered in details in the documentation, with sample codes.
So I strongly suggest you invest some time in reading the chapters of interest.
This forum also has a FAQ thread.
The XML DB home page on OTN and the Sample Code page may also provide you with some good real-life examples of XML DB usage :
As for the actual discussion, here's a basic example showing what you can do using native XML to SQL mappings :
1) Registering the schema :
Oracle automatically generates the object and collection types that map the XML structure to SQL :
DECLARE v_schema clob := '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"> <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET"> <xs:attribute name="sheetName" type="xs:string"/> <xs:attribute name="sheetId" type="xs:integer"/> </xs:complexType> <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false"> <xs:sequence> <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/> </xs:sequence> </xs:complexType> <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/> </xs:schema>'; BEGIN dbms_xmlschema.registerSchema( schemaURL => 'workbook.xsd' , schemaDoc => v_schema , local => true , genTypes => true , genTables => true , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE ); END; /
2) Sample test code :
SQL> desc t_workbook t_workbook is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- worksheet T_WORKSHEET_COLL SQL> desc t_worksheet_coll t_worksheet_coll VARRAY(2147483647) OF T_WORKSHEET T_WORKSHEET is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T sheetName VARCHAR2(4000 CHAR) sheetId NUMBER(38)
SQL> declare 2 3 doc xmltype := xmltype('<workbook> 4 <worksheet sheetName="Sheet1" sheetId="1"/> 5 <worksheet sheetName="Sheet2" sheetId="2"/> 6 <worksheet sheetName="Sheet3" sheetId="3"/> 7 </workbook>'); 8 9 obj t_workbook; 10 11 begin 12 13 -- create an object instance from the input XML document : 14 doc.toObject(obj, 'workbook.xsd', 'workbook'); 15 16 -- update an attribute : 17 obj."worksheet"(2)."sheetName" := 'new_sheet2'; 18 19 -- save back the object as XML : 20 doc := xmltype(obj, 'workbook.xsd', 'workbook'); 21 22 dbms_output.put_line(doc.getstringval); 23 24 end; 25 / <workbook> <worksheet sheetName="Sheet1" sheetId="1"/> <worksheet sheetName="new_sheet2" sheetId="2"/> <worksheet sheetName="Sheet3" sheetId="3"/> </workbook> PL/SQL procedure successfully completed
Thank you very much, not quite what i was looking for.
but your afford is amazing.
at least now i understand XML DB. :)