Calling Java Program from Oracle Trigger
I will try to be concise here. Actually I have a piece of code written in Java for sending sms to our customers.
We are using Oracle 10g as our database. All what I want here is that
"Whenever a row is inserted in a particular table, i want to create a trigger that can call my Java Program and passes couple of parameters to it e.g., Customer No, Invoice Id, Invoice amount etc."
I tried to find certain tutorials but mostly I am getting tutorials on creating java stored procedure to make certain changes in the database. I don't want that.
All I want is to have a way through which I can fire a trigger that calls my java program and pass some parameters to my java program. is it possible ?
Can anyone provide me with a code to write in an oracle trigger that just calls my program (lets say Test.java) with some parameters?? I'll be really greatful
Many Thanks
java oracle stored-procedures triggers
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
add a comment |
I will try to be concise here. Actually I have a piece of code written in Java for sending sms to our customers.
We are using Oracle 10g as our database. All what I want here is that
"Whenever a row is inserted in a particular table, i want to create a trigger that can call my Java Program and passes couple of parameters to it e.g., Customer No, Invoice Id, Invoice amount etc."
I tried to find certain tutorials but mostly I am getting tutorials on creating java stored procedure to make certain changes in the database. I don't want that.
All I want is to have a way through which I can fire a trigger that calls my java program and pass some parameters to my java program. is it possible ?
Can anyone provide me with a code to write in an oracle trigger that just calls my program (lets say Test.java) with some parameters?? I'll be really greatful
Many Thanks
java oracle stored-procedures triggers
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13
add a comment |
I will try to be concise here. Actually I have a piece of code written in Java for sending sms to our customers.
We are using Oracle 10g as our database. All what I want here is that
"Whenever a row is inserted in a particular table, i want to create a trigger that can call my Java Program and passes couple of parameters to it e.g., Customer No, Invoice Id, Invoice amount etc."
I tried to find certain tutorials but mostly I am getting tutorials on creating java stored procedure to make certain changes in the database. I don't want that.
All I want is to have a way through which I can fire a trigger that calls my java program and pass some parameters to my java program. is it possible ?
Can anyone provide me with a code to write in an oracle trigger that just calls my program (lets say Test.java) with some parameters?? I'll be really greatful
Many Thanks
java oracle stored-procedures triggers
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
I will try to be concise here. Actually I have a piece of code written in Java for sending sms to our customers.
We are using Oracle 10g as our database. All what I want here is that
"Whenever a row is inserted in a particular table, i want to create a trigger that can call my Java Program and passes couple of parameters to it e.g., Customer No, Invoice Id, Invoice amount etc."
I tried to find certain tutorials but mostly I am getting tutorials on creating java stored procedure to make certain changes in the database. I don't want that.
All I want is to have a way through which I can fire a trigger that calls my java program and pass some parameters to my java program. is it possible ?
Can anyone provide me with a code to write in an oracle trigger that just calls my program (lets say Test.java) with some parameters?? I'll be really greatful
Many Thanks
java oracle stored-procedures triggers
java oracle stored-procedures triggers
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
asked Nov 25 '18 at 14:04
Farhan Farhan
1,599113558
1,599113558
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13
add a comment |
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13
add a comment |
1 Answer
1
active
oldest
votes
See Calling Java Stored Procedure from Database Trigger
CREATE OR REPLACE PROCEDURE log_sal (
emp_id NUMBER,
old_sal NUMBER,
new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';
Next, create the sal_audit table, as follows:
CREATE TABLE sal_audit (
empno NUMBER,
oldsal NUMBER,
newsal NUMBER
);
Finally, create the database trigger, which fires when a salary increase exceeds 20 percent:
CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > 1.2 * old.salary)
CALL log_sal(:new.employee_id, :old.salary, :new.salary);
Define your code in DBTrigger.logSal method in this example
public class DBTrigger{
public static void logSal (int empID, float oldSal, float newSal)
throws SQLException{
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468288%2fcalling-java-program-from-oracle-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
See Calling Java Stored Procedure from Database Trigger
CREATE OR REPLACE PROCEDURE log_sal (
emp_id NUMBER,
old_sal NUMBER,
new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';
Next, create the sal_audit table, as follows:
CREATE TABLE sal_audit (
empno NUMBER,
oldsal NUMBER,
newsal NUMBER
);
Finally, create the database trigger, which fires when a salary increase exceeds 20 percent:
CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > 1.2 * old.salary)
CALL log_sal(:new.employee_id, :old.salary, :new.salary);
Define your code in DBTrigger.logSal method in this example
public class DBTrigger{
public static void logSal (int empID, float oldSal, float newSal)
throws SQLException{
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
|
show 2 more comments
See Calling Java Stored Procedure from Database Trigger
CREATE OR REPLACE PROCEDURE log_sal (
emp_id NUMBER,
old_sal NUMBER,
new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';
Next, create the sal_audit table, as follows:
CREATE TABLE sal_audit (
empno NUMBER,
oldsal NUMBER,
newsal NUMBER
);
Finally, create the database trigger, which fires when a salary increase exceeds 20 percent:
CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > 1.2 * old.salary)
CALL log_sal(:new.employee_id, :old.salary, :new.salary);
Define your code in DBTrigger.logSal method in this example
public class DBTrigger{
public static void logSal (int empID, float oldSal, float newSal)
throws SQLException{
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
|
show 2 more comments
See Calling Java Stored Procedure from Database Trigger
CREATE OR REPLACE PROCEDURE log_sal (
emp_id NUMBER,
old_sal NUMBER,
new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';
Next, create the sal_audit table, as follows:
CREATE TABLE sal_audit (
empno NUMBER,
oldsal NUMBER,
newsal NUMBER
);
Finally, create the database trigger, which fires when a salary increase exceeds 20 percent:
CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > 1.2 * old.salary)
CALL log_sal(:new.employee_id, :old.salary, :new.salary);
Define your code in DBTrigger.logSal method in this example
public class DBTrigger{
public static void logSal (int empID, float oldSal, float newSal)
throws SQLException{
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
See Calling Java Stored Procedure from Database Trigger
CREATE OR REPLACE PROCEDURE log_sal (
emp_id NUMBER,
old_sal NUMBER,
new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';
Next, create the sal_audit table, as follows:
CREATE TABLE sal_audit (
empno NUMBER,
oldsal NUMBER,
newsal NUMBER
);
Finally, create the database trigger, which fires when a salary increase exceeds 20 percent:
CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > 1.2 * old.salary)
CALL log_sal(:new.employee_id, :old.salary, :new.salary);
Define your code in DBTrigger.logSal method in this example
public class DBTrigger{
public static void logSal (int empID, float oldSal, float newSal)
throws SQLException{
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
edited Nov 25 '18 at 14:14
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
answered Nov 25 '18 at 14:09
user7294900user7294900
23.2k113363
23.2k113363
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
|
show 2 more comments
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
you mean DBTrigger would be my javaClass name and logSal would be the method in my java program ?
– Farhan
Nov 25 '18 at 14:13
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
@Farhan yes, added to answer code from example
– user7294900
Nov 25 '18 at 14:14
i will try it now...
– Farhan
Nov 25 '18 at 14:15
i will try it now...
– Farhan
Nov 25 '18 at 14:15
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
I am getting this error after inserting row in the table "class DBTrigger does not exist. Error during execution of trigger". Am I supposed to keep the class file at a particular location ?
– Farhan
Nov 25 '18 at 14:27
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
@Farhan See docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
– user7294900
Nov 25 '18 at 14:29
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468288%2fcalling-java-program-from-oracle-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Once you have a SMS gateway this is quite easy to do in pure PL/SQL using built-in PL/SQL UTL_HTTP library. For instance @JefferyKemp has published his implementation for clicksend which I'm sure you could adapt to your own needs. Find out more
– APC
Nov 25 '18 at 15:13