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
> ----------------------------------------- --------
> ----------------------------
> USERID NOT NULL NUMBER(38)
> SUBSCRIBER VARCHAR2(30)
> HOST NOT NULL VARCHAR2(100)
> ALIAS VARCHAR2(30)
> AUTHFAILURE NUMBER(38)
> BLOCKSTATUS NUMBER(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
>
> double senddata (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 the use of the individual to whom it is addressed. It
> may contain privileged or confidential information and should not be
> circulated or used for any purpose other than for what it is intended. If
> you have received this message in error, please notify the originator
> immediately. If you are not the intended recipient, you are notified that
> you are strictly prohibited from using, copying, altering, or disclosing
> the contents of this message. HSS accepts no responsibility for loss or
> damage arising from the use of the information transmitted by this email
> including damage from virus.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
