You Asked
Hi,Tom:
If I have a external procedure like:
int donothing(with_context, num_in, num_in_ind, num_out, num_out_ind)
OCIExtProcContext *with_context; /* With Context */
int num_in;
short num_in_ind;
int *num_out;
short *num_out_ind;
{
ocictx oci_ctx;
ocictx *oci_ctxp = &oci_ctx;
sb4 err;
char errbuf[512];
/* Obtain OCI handle for SQL statement using the context passed. */
err = OCIExtProcGetEnv(with_context, /* With context */
&oci_ctxp->envhp,
&oci_ctxp->svchp,
&oci_ctxp->errhp);
check_err("OCIExtProcGetEnv", err, errbuf, oci_ctxp);
*num_out = num_in;
*num_out_ind = (short)OCI_IND_NOTNULL; /* indicate not null value */
return num_in;
}
and I want to use it in a cursor in a package like this way:
create or replace package test_out_pkg is
FUNCTION donothing(num_in IN binary_integer, num_out OUT BINARY_INTEGER)
return pls_integer ;
procedure test_out;
end;
/
create or replace package body test_out_pkg is
FUNCTION donothing
(num_in IN binary_integer, num_out OUT BINARY_INTEGER)
return pls_integer
IS EXTERNAL
NAME "donothing"
LIBRARY SELIB
WITH CONTEXT
PARAMETERS
(
context,
num_in INT,
num_in INDICATOR SHORT,
num_out int,
num_out INDICATOR SHORT
);
procedure test_out is
v_num_out1 number;
ls number;
cursor c1(ci_num_out out number) is
select empNo,donothing(empNo,ci_num_out) ls
from emp
order by ls desc;
begin
ls := donothing(1,v_num_out1);
dbms_output.put_line('ls='||to_char(ls));
dbms_output.put_line(v_num_out1);
for c in c1(v_num_out1) loop
dbms_output.put_line('num_in='||to_char
(c.mediol_case_id)
||' num_out='||to_char(v_num_out1)||' ls='||
to_char(ls));
end loop;
end;
end;
/
The result is :
when call the "DONOTHING" external procedure in procedure body , I succeeded. but it failed in the cursor. My target is
call it once only in the cursor, because i need the return value and the out parameter.
I apologize for the email sended uncompletely. By the way , I suggest that improve the interface of this page, yesterday, after
i typed some stuff, i used to type the TAB key and the cursor located in TAB key, and my information just sent out. I suggest there should be an confirm interface after submit.
Thanks
Minnie
and Tom said...
2 problems here:
1) You cannot call a PROCEDURE from SQL only a function. You have the above defined as a function but it doesn't return anything sensible.
2) you cannot call a function that has an OUT parameter -- only in parameters are allowed in SQL.
Your solution is easy -- use a function that returns the number.
Say you have:
function return_number return number;
and
function return_number return number
as
language C
name "return_number"
library demoPassing
with context
parameters ( CONTEXT,
RETURN INDICATOR short,
RETURN OCINUMBER );
And then C code like:
#ifdef WIN_NT
_declspec (dllexport)
#endif
OCINumber * return_number
( OCIExtProcContext * ctx,
short * return_i )
{
double our_number = 123.456;
OCINumber * return_value;
myCtxStruct*myCtx;
*return_i = OCI_IND_NULL;
/* gets the svchp, errhp, envhp and so on... */
if ( (myCtx = init( ctx )) == NULL ) return NULL;
/*
* Allocate storage for the number we are returning. We
* cannot just
* use a stack variable as it will go out of scope when we
* return.
* Using malloc would be a cause for a memory leak. Using a
* static
* variable would not work either as due to dll caching --
* someone else
* can come along and alter the values we are pointing to
* after we
* return (but before Oracle has copied the value).
* Allocating storage is the only correct way to do this.
*/
return_value =
(OCINumber *)OCIExtProcAllocCallMemory(ctx,
sizeof(OCINumber) );
if( return_value == NULL )
{
/* a function i wrote to call the oci error function */
raise_application_error( myCtx,
ERROR_OCI_ERROR, "%s", "no memory" );
}
else
{
/*
* Here we just want to return the number 123.456 --
* nothing fancy,
* therefore we just convert that double into an
* OCINumber, set the
* indicator variable and return our return_value...
*/
if ( OCINumberFromReal( myCtx->errhp, &our_number,
sizeof(our_number), return_value ) !=
OCI_SUCCESS )
{
raise_application_error(myCtx,ERROR_OCI_ERROR,
"%s",lastOciError(myCtx));
}
*return_i = OCI_IND_NOTNULL;
}
/* undoes any setup i did in init() */
term(myCtx);
return return_value;
}
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment