Oracle insert statement doesn't work when called from PHP code

Question

I have a stored procedure in my Oracle 12c database which deletes all data from a table and then inserts new data to that same table. Stored procedure works as intended when called from SQL Developer, however, when i call the procedure with PHP code it only executes the "delete" statement, but not the insert.

My stored procedure:

create or replace procedure myProcedure(
returnResult out varchar2
)
is
inc number;
current_day date;
begin

inc := 0;
select to_date(sysdate, 'yyyy.mm.dd') into current_day from dual;

delete from TEMP_GRAFIKAI;
commit;

while inc < 10
loop
insert into TEMP_GRAFIKAI (NUO, IKI, JUST_DATE, NUO_TIME, IKI_TIME, STAFF_ID, STUN_ID, PROFESSION_ID, PLACE_ID)
with t as (select /*+ materialize */ min(a.SCHEDULE_TIME) NUO, 
max(a.SCHEDULE_TIME) IKI,
to_char(min(a.SCHEDULE_TIME), 'yyyy.mm.dd') JUST_DATE , 
to_char(min(a.SCHEDULE_TIME), 'hh24:mi') NUO_TIME, 
to_char(max(a.SCHEDULE_TIME), 'hh24:mi') IKI_TIME,
b.ID STAFF_ID, e.ID STUN_ID, d.PROFESSION_ID, f.PLACE_ID
from SCHEDULE_TIME a
join STAFF b on a.TREATMENT_STAFF_ID = b.ID
join COMPANY_PERSON c on c.STAFF_ID = b.ID and c.COMPANY_ID = 1 and c.STRUCTURE_UNIT_ID != 1
join PROFESSION_TEXT d on a.TREATMENT_PROF_ID = d.PROFESSION_ID and d.LANG_DOVA_CODE = 'lt'
join ORGANIZATION e on a.TREATMENT_STUN_ID = e.ID
join PLACE_TEXT f on a.TREATMENT_PLACE_ID = f.PLACE_ID and f.LANG_DOVA_CODE = 'lt'
join SCHEDULES g on a.SCHEDULE_ID = g.ID and g.SYS_DELETE_STATUS = 'N'
where a.SCHEDULE_TIME > current_day+inc and a.SCHEDULE_TIME < current_day+1+inc
and a.SYS_DELETE_STATUS = 'N'
group by b.ID, e.ID, d.PROFESSION_ID, f.PLACE_ID)
select t.NUO, t.IKI, t.JUST_DATE, t.NUO_TIME, t.IKI_TIME, t.STAFF_ID, t.STUN_ID, t.PROFESSION_ID, t.PLACE_ID from t
;
commit;
inc := inc +1;
returnResult:= 'in_loop'||to_char(inc);
end loop;

if returnResult != 'in_loop10' then
returnResult := 'ok';
end if;

end;

With the "in_loop" part I was checking if the loop is actually being looped, found no problems there, it properly loops the loop 10 times.

My PHP code:

$dbhandle = oci_connect($user, $password, $host, "AL32UTF8");

$proc = oci_parse($dbhandle, "call myProcedure(:returnResult)");
oci_bind_by_name($proc, ":returnResult", $returnResult, 50);
$result = oci_execute($proc);

if (!$result) {$e = oci_error($proc); $err = 'err:'.$e["message"];}

$result doesn't throw any error, $returnResult properly returns text string "in_loop10".

Delete statement gets executed with no problems, but the insert part doesn't insert anything. If I execute the same stored procedure from SQL Developer, everything (delete and insert) works perfectly.

I think the problem might be within the insert statement, especially the "materialize" part, but I'm not exactly sure.

I'm at a loss here, have no idea, why the insert part doesn't work when i call the procedure from PHP code. Any help will be greatly appreciated.

I modified your code to operate on a single-field table. It works for me (Oracle 11g/PHP 7). Try simplifying your insert until it works and build up from there. — timclutton
– timclutton, Commented Jul 7, 2016 at 14:51

Timekiller · Accepted Answer · 2016-07-07 15:05:05Z

1

If I had to guess, it's because of

select to_date(sysdate, 'yyyy.mm.dd') into current_day from dual;

You see, sysdate already has date type. With this line, you are implicitly converting it to varchar2 using the connection default NLS settings, then converting it back to date. If PHP connects with NLS date settings other than yyyy.mm.dd, you'll get either an exception (not this case) or an incorrect date when trying to convert back. If you want only a date portion without time, use trunc(sysdate) instead. Also, you can simply assign to variable: current_day := trunc(sysdate);

If that doesn't work, try running the select directly from PHP, perhaps it actually returns no results under that connection due to different configuration or something else.

answered Jul 7, 2016 at 15:05

Timekiller

3,1562 gold badges18 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Oracle insert statement doesn't work when called from PHP code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related