Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

After inserting trigger is not working as expected.

User_1BBI0Sep 30 2021

Hey,
I had created one trigger which is supposed to fired up after the new row entered in the table.
SImple process is that: The trigger will consist of 2 values: ID and table name, which it passes to procedure which call the external POST API.
Now, The procedure is working fine but upon checking the log in the API server, I got to understand that triggers are fired before the row is inserted as I was getting error of row not found in the API server.
I have my Triggers to be run after insert, no errors while compiling.
Here are the trigger and procedure, I am using:
***TRIGGER***
CREATE OR REPLACE TRIGGER
MOB_TRANS_AF_INS
AFTER INSERT
ON MOB_TRANS
FOR EACH ROW

DECLARE
table_name_ VARCHAR2(100) := 'MOB_TRANS';
key_ NUMBER := :NEW.transaction_id;

BEGIN
Add_Notification(table_name_, key_);
END;

***PROCEDURE***
create or replace PROCEDURE
Add_Notification(
table_name_ IN VARCHAR2,
key_ IN VARCHAR2)
IS
req_ UTL_HTTP.req;
resp_ UTL_HTTP.resp;
url_ VARCHAR2(4000) := 'http://dummy.apiurl.com/public/api/notification';
content_ VARCHAR2(4000) := 'table_name='||table_name_||CHR(38)||'unique_id='||key_;
buffer_ varchar2(4000);

BEGIN
--rest API call
DBMS_OUTPUT.PUT_LINE('URL is: '|| url_);
req := UTL_HTTP.Begin_Request(url, 'POST');
DBMS_OUTPUT.PUT_LINE('Request Begin');
UTL_HTTP.Set_Header(req_, 'content-type', 'application/x-www-form-urlencoded');
DBMS_OUTPUT.PUT_LINE('Request content-type set.');
DBMS_OUTPUT.PUT_LINE('Content '|| content_);
UTL_HTTP.Set_Header(req_, 'Content-Length', LENGTH(content_));
DBMS_OUTPUT.PUT_LINE('Content '|| LENGTH(content_));
UTL_HTTP.Write_Text(req_, content_);
resp_ := UTL_HTTP.Get_Response(req_);

begin
loop
utl_http.read_line(resp_, buffer_);
DBMS_OUTPUT.put_line(buffer_);
end loop;
UTL_HTTP.End_Response(resp_);
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.End_Response(resp_);
End;
END Add_Notification;

Comments

Alanc-Oracle

The <threads.h> header from C11 is only available on Solaris 11.4. Older versions of Solaris do not have C11 support and require using the POSIX threads API instead.

1 - 1

Post Details

Added on Sep 30 2021
5 comments
5,153 views