The architecture we use is a backend Oracle database, a collection of processes written in Pro*C and front end VB clients.
In the past SQL logic has been embedded in both the VB code in the Pro*C. We are currently in the progress of migrating SQL into Oracle packages whenever a VB or Pro*C module needs changing. This will be a long ongoing task and the goal is to prevent the need to repeat and maintain logic in two areas by consolidating into Oracle packages.
In Pro*C we commonly pass around structures containing records between functions. E.g:
struct sysusr r_sysusr;
struct ind_sysusr i_sysusr;
EXEC SQL select * into :r_sysusr:i_sysusr where usrnam = ‘auser’;
ret = lib_do_somthing(r_sysusr, i_sysusr);
It is possible to do something similar in oracle packages with:
l_sysusr sysusr%rowtype;
select * into l_sysusr where usrnam = ‘auser’;
ret := sp_do_somthing(l_sysusr);
It would be very useful if there is a way to populate a structure (or other object) in Pro*C and pass this into an Oracle package as a rowtype parameter.
The code may look something like this:
struct sysusr r_sysusr;
struct ind_sysusr i_sysusr;
EXEC SQL select * into :r_sysusr:i_sysusr where usrnam = ‘auser’;
/* do something here to convert r_sysusr & i_sysusr into a valid rowtype parameter */
/*convert_struct_into_rowtype(r_sysusr, i_sysusr, &row_sysusr); ??? */
EXEC SQL CALL pkg_users.sp_do_somthing(:row_sysusr) into :ret
The above is only a derived simple example. Obviously here you could just pass thru the username and query the data in the package. Our code contains thousands of functions passing structures around. We can only migrate a few at a time so being able to pass a whole structure is the only manageable way we can see to do this. So where possible (in Pro*C pre complied level) we want to avoid either passing each element or a structure as a single parameter and avoid using temporary tables each side on the calls.
Any solutions, suggestions or comments are most welcome.
Mark