| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: resending: Database triggers and Stored Precedure related
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):
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
ineyman_at_perceptron.com
>
>
> 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: hbhondwe_at_hss.hns.com > > 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: ListGuru_at_fatcity.com (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: ineyman_at_perceptron.com
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: ListGuru_at_fatcity.com (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). Received on Thu Oct 17 2002 - 10:30:19 CDT
 |
 |