Call an Oracle function that does insert / update from PHP?
We have a function, if we call it with SELECT, we get the error code ORA-14551 "Cannot perform DML operation inside query"
select pkg_tools.replace_site(1121,3343) from dual;
how to run this function and get results
when we run it in SQL developer like this:
declare
v_return VRACHAR2(200);
begin
v_return := pkg_tools.replace_site(1121,3343);
end;
works without errors
and we need this function to be called inside PHP
note: I cannot insert this function here because it is long, but it contains all operations including insert and update!
source to share
A function that DML cannot call in a statement SELECT
, regardless of the calling language.
If you want to do DML and return a value, it would be much smarter to create a stored procedure with a parameter OUT
instead of using a function. So it would be much wiser
CREATE OR REPLACE PROCEDURE proc_name( p_1 IN NUMBER,
p_2 IN NUMBER,
p_ret OUT VARCHAR2 )
AS
BEGIN
p_ret := pkg_tools.replace.site( p_1, p_2 );
END;
and then calling this stored procedure from PHP
$sql = 'BEGIN proc_return( :p_1, :p_2, :p_ret ); END;';
If you don't want to do this, I guess you could do something like this (adapted from one of the scripts on the Underground PHP page 164 and Oracle manual )
<?php
$c = oci_connect('hr', 'hrpwd', 'localhost/XE');
$s = oci_parse($c, "begin :ret :=pkg_tools.replace_site(1121,3343); end;");
oci_bind_by_name($s, ':ret', $r, 200);
oci_execute($s);
echo "Result is: ".$r;
?>
source to share