Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro*C structure (record) used as a rowtype parameter for an Oracle package?
Pro*C structure (record) used as a rowtype parameter for an Oracle package? [message #364672] Tue, 09 December 2008 08:38 Go to next message
culllcfc
Messages: 3
Registered: December 2008
Junior Member
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
Re: Pro*C structure (record) used as a rowtype parameter for an Oracle package? [message #438352 is a reply to message #364672] Fri, 08 January 2010 04:50 Go to previous messageGo to next message
culllcfc
Messages: 3
Registered: December 2008
Junior Member
bump Smile
Re: Pro*C structure (record) used as a rowtype parameter for an Oracle package? [message #454497 is a reply to message #438352] Thu, 06 May 2010 02:40 Go to previous message
kunalkumar2611
Messages: 4
Registered: April 2010
Junior Member
Hi All,
I have a similar situation as of mark and i am not able to find a solution to it

I want to populate a structure in Pro*C and pass this into an Oracle package as a rowtype parameter.

Any suggestion to asap will be appreciated.

Thanks
Previous Topic: compiling the c file (output of pc file precompialtion)
Next Topic: Problem In Running OCCI program on Linux
Goto Forum:
  


Current Time: Thu Mar 28 11:24:33 CDT 2024