resending: Database triggers and Stored Precedure related
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 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).
trigger and stored procedure
Hi,
Can someone pls help me with the following problem?
thanks
harsh
Requirement:
Whenever a row is deleted from a database table, the client application
shall get the deletion details. Based on the studies done the following
approach is thought to be more appropriate.
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.subscriber);
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 error is returned.
2. Or 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).
trigger and stored procedure related
Hi,
Can someone pls help me with the following problem?
thanks
harsh
Requirement:
Whenever a row is deleted from a database table, the client application
shall get the deletion details. Based on the studies done the following
approach is thought to be more appropriate.
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.subscriber);
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 error is returned.
2. Or 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).
