Life, Universe and Everything Distributed.

// TODO: Place some witty description about Sajay's Blog here.

XML and Oracle.

How many of you wanted to get xml output from oracle quickly.. well here is a smiple way

set

pages 0;
SET LONG 100000;
select xmlgen.getxml('select * from scott.emp'/*your query should be here*/) from dual;

To get the schema also you can pass the parameter to the getXMl file like this

 select

xmlgen.getxml('select * from scott.emp'/*your query should be here*/,2) from dual;
But to get the cheese you should refer to this ..

Quite a good competition to the claim of XML support in MS SQL server.
http://www.cs.utah.edu/classes/cs5530/oracle/doc/B10501_01/appdev.920/a96620/xdb12gen.htm#1656

Regards
Sajay

Oracle and ref cursors in .NET

Now when unlike SQL server, filling datasets were a little different on Oracle Stored Procedures.

For starters lets define and procedure.

The sys_refcursor is an object in Oracle 9i that would be used to fill the dataset.

 

 

CREATE

OR REPLACE PROCEDURE sp_getItems (

   p_refcursor   OUT   sys_refcursor,

)

IS

BEGIN

   OPEN p_refcursor

    FOR

       SELECT itemid FROM itemmas;

END;

 

 

From now on calling the procedure would be the same.

 

 

 OrCmd.CommandText = "sp_getItems"

 OrCmd.CommandType = CommandType.StoredProcedure

 OrCmd.Parameters.Add(new OracleParameter("rc",OracleType.Cursor)).Direction =           ParameterDirection.Output 

 OrDA.Fill(lDbDs, Invoice)

 

 

 

Now just a tip for testing the sp in SQL+ is as follow

 

 

SQL> var rc refcursor;

SQL> exec sp_getItems(:rc);

SQL> print rc;