Calling an Oracle SP in .Net

mewso

I'm hoping someone has some experience with oracle and .net as I'm at a bit of a dead end. A company who did our HR Clocking system was asked to give me a way to access the clocking status of staff so I could show on our staff index if they were currently in or out. So they offered to create some stored procedures and give me access to their oracle db to call them.

All well and good but the procedures are very strange looking to me and while I can open a connection I can't quite figure out how to pass in the required parameters.

For example there is a procedure specified like so:-

procedure get_all_clockings(p_date              in date,
                               p_clockings_table   in out all_clockings_tab)

So I can see it needs a date parameter called p_date. No problems there. But it also needs a parameter called p_clockings_table which is of type all_clockings_tab which is a custom type in their database and will contain the returned data. My best guess here is I am not going to be able to get this to work unless they change it so a non-custom type is returned as there is no way to declare a custom type in .net but I would love to know if it is possible and how to do it if anyone has any idea.

Custom type info.:-

   type all_clockings is record (person         ct_clocking.person%type,
                                 clock_time     ct_clocking.clock_time%type,
                                 clock_type     ct_clocking.clock_type%type,
                                 absent         boolean,
                                 return_date    date);


   type all_clockings_tab is table of all_clockings index by binary_integer;

More custom types.

trellheim

A: tell them to write a proper stored procedure that accepts simple types and to stop acting the maggot.


at some point you're going to have to iterate across a result set so their return set should be redefined as a simple name, in/out so they need to write another procedure on top of the first to filter out the doodoo.

Malice

I did some work with Oracle in .NET recently so I was all set to elaborate on how the DAL worked in that project until I opened it up and remembered that the bastard thing uses dynamically generated SQL and doesn't actually call any stored procedures at all .
It was also written in VB.NET so I'm not surprised my brain tried to forget all about it.

mewso

Thanks guys I have convinced them to allow me read only access on the relevant tables so that should solve it. How much they will bill for it is another thing. 2 Stored Procedures cost €3000. A joke.

kayos

Why didn't they just pass out a the normal sys ref cursor... any way a quick Google and that seems to be along the lines of what you need. .

http://www.codeproject.com/KB/cs/CustomObject_Oracle.aspx?display=Mobile

And I wont charge 3K for the advise