we have a function if we call it by using SELECT we get the error code ORA-14551 “Cannot preform a DML operation inside a query”
select pkg_tools.replace_site(1121,3343) from dual;
how to run this function and get the results
when we run it in SQL developer in this way:
declare v_return VRACHAR2(200); begin v_return := pkg_tools.replace_site(1121,3343); end;
works with no errors
and we need this function to be called inside PHP
note: I can not paste this function here, because it’s to long, but it does allot of operations including insert and update!
Advertisement
Answer
A function that does DML cannot be called in a SELECT
statement regardless of the calling language.
If you want to do DML and return a value, it would make much more sense to create a stored procedure with an OUT
parameter rather than using a function. So it would make much more sense to
CREATE OR REPLACE PROCEDURE proc_name_return( 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 call that stored procedure from PHP
$sql = 'BEGIN proc_name_return( :p_1, :p_2, :p_ret ); END;';
If you don’t want to do that, my guess is that you could do something like this as well (adapted from one of the scripts on page 164 of the Underground PHP 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; ?>