executing stored procedures over a database link in oracle 9i

bsj

Hi everyone,

I'm trying to successfully execute stored procedures using php and oracle 9i. I have the webserver and php and oracle installed on a development box, and I'm trying to execute stored procedures over a database link to the main database.

Oracle keeps on returning back : Warning: OCIStmtExecute: ORA-02064: distributed operation not supported ORA-06512: at "LTCS.LOTPKG", line 175 ORA-06512: at line 1 in /usr/local/apache/htdocs/ltcs_database/global_functions.php on line 51

I thought it might have something to do with autocommit being turned on, so I turned it off in the OCIExecute call ( OCIExecute( $sth, OCI_DEFAULT ) and it displays the same behaivor. Anyone have any suggestions for me?

Brian

millesm

From Oracle.......

ORA-02064 distributed operation not supported

Cause: One of the following unsupported operations was attempted:

Array execute of a remote update with a subquery that references a database link,
or
An update of a long column with bind variable and an update of a second column with a subquery that both references a database link and a bind variable,
or
A commit is issued in a coordinated session from an RPC with OUT parameters.

Action: Simplify the remote update statement.

ORA-06512 is just telling you what the offending program and line number is.

Can you run the stored procedure via SQL*Plus over the database link?

bsj

millesm,

I just tried executing the statement, and I can't successfully execute though sqlplus, it returns back the distributed operation not permitted error. Looks like I should seek oracle help, not php help.

On the off chance someone here could possibly help me, here's some more information:

SQL> declare
2 ret varchar2(2000);
3 begin
4 LotPkg.lot_start@ltcs_link( 'TEST1', '2BE7_1501', 'EPHEMTXX', 'GAAS', ret );
5 end;
6 /
declare
*
ERROR at line 1:
ORA-02064: distributed operation not supported
ORA-06512: at "LTCS.LOTPKG", line 175
ORA-06512: at line 4

Here's the relevant SQL code in the procedure :

INSERT INTO wip ( lot_id,
part_id,
process_id,
route_id,
route_seq,
status,
cur_oper,
cur_oper_rev,
cur_step,
cur_step_rev,
cur_step_seq,
cur_qty,
action )
VALUES ( lot_rec.LOT_ID,
part_rec.PART_ID,
process_rec.PROCESS_ID,
route_rec.ROUTE_ID,
first_route_seq,
'QUEUED',
first_op_id,
first_op_rev,
0,
'NULL',
0,
lot_rec.QTY_OPEN,
'LOT_START' );

ret := '0😮K:lot_id ' || lot_rec.LOT_ID;
ret := ret || ' inserted as part_id ' || part_rec.PART_ID;
ret := ret || ' process_id ' || process_rec.PROCESS_ID;
ret := ret || ' route_id ' || route_rec.ROUTE_ID;
ret := ret || ' quantity ' || lot_rec.QTY_OPEN;
DBMS_OUTPUT.PUT_LINE( ret );
DBMS_OUTPUT.PUT_LINE( 'commiting insertion' );
COMMIT;

I am thinking the unsupported operation I am attempting is "A commit is issued in a coordinated session from an RPC with OUT parameters" - and the solution is to simplify the statement - but I do not see how. Any suggestions?

Brian