how to call/trigger PHP Script from ORACLE SQL Database

Question

My task is that I need to check my DB-table every day. Rows older than 10 years have to be deleted automatically and if the state of my row didn't change since 5 days an automatic email should be sent. I already have a script in my WebApplication, but the problem is that clients have to run my Application to execute the Script inside. Its not that smart. I googled a lot but couldnt find some good solutions with good examples.

Does someone has a good solution for my problem? How do i trigger a PHP Script from Oracle if conditions are true?

EDIT:

For my automatic delete concept i have a solution.

But for my Email problem i have found something:

grant execute on UTL_MAIL to public;

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => '[email protected]',
            recipients => '[email protected]',
               subject => 'Test Mail',
               message => 'Hello World',
             mime_type => 'text; charset=us-ascii');
END;

Unfortunetly i get the following error msg:

*24247. 00000 - "network access denied by access control list (ACL)" *Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list. Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

Any ideas what im doing wrong and how i can fix this ?

Edit:

FIXED my smtp_out_server was the wrong one. I simply got the server by using this query:

SELECT * FROM dba_network_acls;

Thank you all for every single information and help attempt!!

-CAN BE CLOSED-

Read this and this. You can simply trigger the stored procedure without using PHP at all. — Aksen P
– Aksen P, Commented Dec 13, 2019 at 11:00

gsalem · Accepted Answer · 2019-12-13 11:00:40Z

1

I think you would be much better off if you write a PL/SQL script to do what you are doing in PHP, and schedule a job in the DB to run this PL/SQL.

answered Dec 13, 2019 at 11:00

gsalem

2,0281 gold badge10 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

delboy1978uk · Accepted Answer · 2019-12-13 10:58:59Z

0

Create your PHP script that calls your database, checks if whatever conditions are true, and then processes whatever it needs to if the conditions are true.

Then, you need to set up a CRON Job in order to get the script to trigger at certain times. You can read about setting up a CRON here https://help.dreamhost.com/hc/en-us/articles/215767047-Creating-a-custom-Cron-Job

answered Dec 13, 2019 at 10:58

delboy1978uk

12.4k2 gold badges25 silver badges42 bronze badges

Collectives™ on Stack Overflow

how to call/trigger PHP Script from ORACLE SQL Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related