Designing a stored procedure API for Pro*C

[rghome]

I am trying to design an API using stored procedures to encapsulate our database. The idea is that the C development department doesn't know about the internal database structure (tables, etc) but instead accesses it though stored procedures. This allows the DBA change the tables, and as long as the stored procedure signatures stay the same, the developers won't be affected.

To do this, I need to be able to call a stored procedure from C and have it return a number of rows of data.

For example, from C I need to be able to do something like:

EXEC SQL BEGIN DECLARE ...
struct Customer {
char name[50];
char company[50];
int accountNumber;
};

struct Customer customerList[100];

EXEC SQL END DECLARE ...

EXEC SQL CALL getCustomers(:customerList);

So, the stored procedure populates the customerList array with customers from the database. (Or something like that).

I don't think, Oracle doesn't support this syntax. Reading though the Pro*C documentation, there seem to be big restrictions on what you can do, and in parts it seems contradictory (for example, I am not sure if you can use arrays of structures). In any case, there is an obvious problem with the design in that it can only return 100 customers.

Has anybody had experience of doing this kind of thing ? Any suggestions on a possible approach would be apprecidated. In particular, whether you can load data into an array of structures.

We are using Oracle 8.1.4.

Thanks.

[shibha]

It's been long since I worked on pro*c, but I for one, think that pro*c is more flexible than you got the impression from the documentation. As far as I can remember there was no restriction on fetching rows in array of structure but I would go back and look at the documentation I can find.

I am assuming your only other problem is fixed length arrays. And it's been years since I worked on c as well, but I guess you could use malloc to dynamically allocate variable length memory when you don't know how much you are going to need ?

[rghome]

Thanks.

I'm having a look now at using OBJECTs and TABLEs and the OTT tool. Maybe I can return a TABLE from a stored procedure. However, I'd rather not have to do the whole OCI thing as it looks a lot more complex than regular Pro*C.

The other option someone suggested was returning a reference to a cursor.