Why are you using OCINumber type? Why using OCI at all in this case? You
are not making calls back to Oracle from you external procedure.
here is an example, that works for me (allows to execute OS comands from
PL/SQL):
-- part of the PL/SQL package:
-- 24. Executes OS command
-- 'p-mode': 0 - sync, 1 - async.
-- If there are parameters to pass, they should be separated by TABs.
-- To run internal DOS command use 'cmd /C your_command'
PROCEDURE exec_os_cmd(
cmd_string IN varchar2,
e_mode IN BINARY_INTEGER,
ret_val OUT BINARY_INTEGER) IS
external
library externDDM
NAME os_cmd
LANGUAGE C
PARAMETERS (cmd_string STRING,
cmd_string INDICATOR,
cmd_string LENGTH,
e_mode INT,
e_mode INDICATOR,
ret_val INT);
-- C procedure (part of the dll):
extern C void __declspec(dllexport) os_cmd(char *cmd_string,
short cmd_string_indicator,
short cmd_string_length,
int e_mode,
short e_mode_indicator,
int *ret_val)
{
int exec_mode;
char *args[10];
char *l_buffer;
char *pdest;
char *pdest1;
int result;
int l_ch = ' ';/* TAB */
int i = 0;
int nI;
for (nI = 0; nI 10; nI++)
args[nI] = NULL;
l_buffer = (char *)calloc(cmd_string_length + 1, sizeof(char));
strncpy(l_buffer, cmd_string, cmd_string_length);
l_buffer[cmd_string_length] = '\0';
pdest1 = l_buffer;
while ((pdest = strchr(pdest1, l_ch)) != NULL)
{
result = pdest - pdest1;
args[i] = (char *)calloc(result + 1, sizeof(char));
strncpy(args[i++], pdest1, result);
pdest1 = pdest + 1;
}
args[i] = (char *)calloc(strlen(pdest1) + 1, sizeof(char));
strncpy(args[i++], pdest1, strlen(pdest1));
args[i] = NULL;
if (e_mode == SyncMode)
exec_mode = _P_WAIT;
else
exec_mode = _P_NOWAIT;
if (_spawnv(exec_mode, args[0], args) == -1)
{
if (errno == E2BIG)
*ret_val = 1;
else if (errno == EINVAL)
*ret_val = 2;
else if (errno == ENOENT)
*ret_val = 3;
else if (errno == ENOEXEC)
*ret_val = 4;
else if (errno == ENOMEM)
*ret_val = 5;
else
*ret_val = 6;
}
else
*ret_val = 0;
for (nI = 0; nI = i-1; nI++)
free(args[nI]);
free(l_buffer);
}
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, October 17, 2002 10:54 AM
Hi,
Resending with the hope that someone would have an answer :)
Can someone pls help us with the following problem?
Any help would be appreciated.
regards
harsh
Requirement:
Whenever a row is deleted from a database table, the client application
shall get the deletion details.
1. Create a trigger on the database table. This trigger will call a
stored function.
2. This function will a call a External C procedure.
3. All these triggers and functions are defined in next section.
Description of the Table naren_subscribers.
Name Null?Type
-
USERIDNOT NULL NUMBER(38)
SUBSCRIBER VARCHAR2(30)
HOST NOT NULL VARCHAR2(100)
ALIAS VARCHAR2(30)
AUTHFAILURENUMBER(38)
BLOCKSTATUSNUMBER(38)
The trigger is defined as follows
CREATE OR REPLACE TRIGGER Pre_del_trigger
BEFORE DELETE ON naren_subscribers
FOR EACH ROW
declare
return_value double precision;
Begin
return_value := senddata(:old.userid, :old.authfailure);
end;
/
The function is defined as follows
SQL CREATE OR REPLACE FUNCTION senddata (
arg1 IN NUMBER,
arg2 IN NUMBER )
return DOUBLE PRECISION AS
EXTERNAL NAME senddata
LIBRARY libsenddata
LANGUAGE C;
/
The C procedure is as follows
doublesenddata (OCINumber USER_ID, OCINumber AUTHFAILURE )
{
/* This c procedure opens a socket connection to client application
(which needs the information of the deleted row.) and passes this USER_ID
and AUTHFAILURE */.
}
This C procedure is compiled and senddata.so is generated. This .so is
placed in oracle server by creating a library libsenddata. All OCI related
headers are included in C procedure
Assumptions:
The function senddata that is defined above is sending arguments as
NUMBER. So the equivalent datatype in C is OCINumber.
The problem description:
1. When I am trying to convert the OCINumber to integer in C procedure
using OCI library, an error is returned.
2. Is there any way to do this conversion from NUMBER to equivalent C
data type. as well as VARCHAR equivalent C data type.
This message is proprietary to Hughes Software Systems Limited (HSS) and
is
intended solely for