Re: Questions about calling PL/SQL packages from ProC

Ian Britten <britten_at_caris.universal.ca> wrote:

>Hi,
>I've got a .pc file that contains a bunch of C functions. These
>functions are basically just wrappers around calls to PL/SQL packages
>(stored in the database).
 

>I've got 2 questions about the arguments to the packages.
 

>First, why can't I (seem to) do this:
>I'm trying to pass C function arguments directly into the package call
 

>int someFunction(int x, int y)
>{
> .
> .
 

> EXEC SQL EXECUTE
> BEGIN
> somePackage.someProcedure(:x, :y);
> END;
> END-EXEC;
 
> .
> .
>}
 

>I seem to have to do this:
>Copy the arguments into local variables and pass those into the package
 

>int someFunction(int x, int y)
>{
> EXEC SQL BEGIN DECLARE SECTION;
> int xCopy=x;
> int yCopy=y;
> EXEC SQL END DECLARE SECTION;
 
> .
> .
>
> EXEC SQL EXECUTE
> BEGIN
> somePackage.someProcedure(:xCopy, :yCopy);
> END;
> END-EXEC;
>
> .
> .
>}

You have to declare you function arguments for oracle. Try something like this
int someFunction( x, y)
EXEC SQL BEGIN DECLARE SECTION;
 int x;
 int y;
EXEC SQL END DECLARE SECTION;
{

	.
	.

	EXEC SQL EXECUTE
	BEGIN
		somePackage.someProcedure(:x, :y);
	END;
	END-EXEC;
	.
	.

>Second, why can't I (seem to) do this:
>I want to pass structure members into the package call
 

>int someFunction(void)
>{
> EXEC SQL BEGIN DECLARE SECTION;
> someStruct x;
> EXEC SQL END DECLARE SECTION;
 
> .
> .
 

> EXEC SQL EXECUTE
> BEGIN
> somePackage.someProcedure(:x.var1, :x.var2);
> END;
> END-EXEC;
>
> .
> .
 

>}
 

>I seem to have to do this:
>Duplicate the structure members as (primative) local variables and use them
 

>int someFunction(void)
>{
> EXEC SQL BEGIN DECLARE SECTION;
> someStruct x; /* In or out of this DECLARE block */
> int var1;
> int var2;
> EXEC SQL END DECLARE SECTION;
>
> .
> .
>
> EXEC SQL EXECUTE
> BEGIN
> somePackage.someProcedure(:var1, :var2);
> END;
> END-EXEC;
>
> x.var1 = var1;
> x.var2 = var2;
> .
> .
 

>}
 

>Both of these problems are going to result in a substantial performance
>hit for me (due to the number of times called, number of variables, etc.)
>Is there something obvious (or not so obvious) I've missed that will
>allow me to do what I want? (Sorry, I've only been doing this for about
>2 weeks now)
 

>(email preferred if possible)
>Thanks,
>-------------------------------------------------------------------------

The Oracle precompiler does not recognise structures at all until Version 2 or 2.1

You can fool it using #define

I can't remember the exact details of how we do it (I'm not at work) but it is something like this.

eg
#define VAR1 x.var1
#define VAR2 x.var2

EXEC SQL BEGIN DECLARE SECTION
  int VAR1
  int VAR2
EXEC SQL END DECLARE SECTION I think we usually pass structure pointers so the define would actually be

#define VAR1 x->var1.

You will need to experiment, but bear in mind that the pre-compiler knows very little C syntax and the pre-compilation is pure text manipulation so soping rules may not be what you would expect. Received on Sun Jul 30 1995 - 00:00:00 CEST