calling nested function to perform DML and wait for commit

jutzin

Hi everybody,

I would like to do some DML -- an insert statement, to be specific -- in a function and have the function then return the numeric key of the newly added row. I call this function from another context and woud then be able to use the newly added data to do some stuff.

More specifically, what I am going to do is this: I have a graph consisting of source, destination and distance triplets in a table. A user should now be able to

1.) add a node 'A' to the graph,
2.) add a node 'B' to the graph
3.) get the shortest path from A to B through the graph.

I have an inner function

function INSERT_NEW_NODE(node_in in sdo_geometry, graph_in in integer) return integer
is  

pragma autonomous_transaction;

cursor node_cur is  
  select 
      source,
      source_geom
  from graph  
;
    
cursor edge_cur is
    select
        source,
        destination,
        distance,
        edge_geom
    from
      graph
    where
      sdo_geom.relate(edge_geom, 'anyinteract', node_in, .005) = 'TRUE';

begin

  -- check if identical with any existing node
  for node_rec in node_cur loop  
    if sdo_geom.relate(node_rec.source_geom, 'EQUAL', node_in, .005) = 'EQUAL' then
      return node_rec.source;
    end if;
  end loop;    
  
  -- get edges
  for edge_rec in edge_cur loop
     -- new_node-->edge.destination and vice versa
    insert into
      graph
      (
        ID,
        GRAPH,
        SOURCE,
        DESTINATION,
        DISTANCE,
        SOURCE_GEOM,
        DESTINATION_GEOM,
        EDGE_GEOM
      )
    values
    (
      graph_id_seq.nextval, --id
      graph_in, --graph
      morton(node_in.sdo_point.x, node_in.sdo_point.y),  -- source morton key
      edge_rec.source, -- destination morton key
      sdo_geom.sdo_distance(edge_rec.source_geom_marl2000, node_in, .005, 'unit=M'), -- distance
      node_in, -- source geom
      edge_rec.source_geom,  -- dest geom
      split_line(edge_rec.edge_geom_marl2000, node_in).segment1 -- edge geom
    );
    commit;
    --new_node-->edge.source and vice versa
    insert into
      gl_graph
      (
        ID,
        GRAPH,
        SOURCE,
        DESTINATION,
        DISTANCE,
        SOURCE_GEOM,
        DESTINATION_GEOM,
        EDGE_GEOM
      )
    values
    (
      graph_id_seq.nextval, --id
      graph_in, --graph
      edge_rec.source, -- source morton key
      morton(node_in.sdo_point.x, node_in.sdo_point.y), -- destination morton key
      sdo_geom.sdo_distance(edge_rec.source_geom, node_in, .005, 'unit=M'), -- distance
      edge_rec.source_geom,  -- source geom
      node_in, -- dest geom
      split_line(edge_rec.edge_geom, node_in).segment2 -- edge geom
    );
    commit;
  end loop
  ;
 
  return(morton(node_in.sdo_point.x, node_in.sdo_point.y));
  
end insert_new_node;

, which adds the new nodes to the graph, connects, calculates distances etc. and returns a handle to the newly added node. I call this function twice from another, outer function

function get_path (line_in in sdo_geometry, graph_in in integer) return sdo_geometry
is  

source number;
destination number;
source_geom mdsys.sdo_geometry;
destination_geom mdsys.sdo_geometry;
  
begin
  
  source := insert_new_node(get_firstvertex(line_in), graph_in);
  destination := insert_new_node(get_lastvertex(line_in), graph_in);
 
  -- source := insert_new_node(get_firstvertex(line_in), graph_in);
  -- destination := insert_new_node(get_lastvertex(line_in), graph_in);
  
  return(get_path_geom(source, destination)); --returns a geometry which is the shortest path between source and destination
  
end get_path;

; and I think, I have to use automous transaction in the inner function, so that the outer function can see any change performed by the inner one. However, this only works, when I call the inner function twice (i.e. remove the comment signs in front of the last two lines of code right before the return statement in the outer function.

So here's my questions: 1.) Why do I have to call the function twice to see the transaction complete? and 2.) How can I avoid that? Is there a way to wait with the execution of the return statement in the inner function until the insert is committed and can be seen by the outer function?

Cheers!

jutzin

Ok, here's the solution: The outer function get_path() calls a function get_path_geom(source, destination), which itself calls something like table(dijkstra(source, destination)), (omitted by me because thoroughly tested and found ok, my bad!) which does the work of finding the shortest path and returns a pipelined table. It turns out that this function for some reason does not get the scope of the outer function and hence does not see the transaction commited on the graph-table. After modifying the dijkstra()-function to return a list instead of a table, the whole thing suddenly works.

So this question has been answered; I would still like to know why the table function does not have the same scope as the rest of the transaction.

Edit: removed misleading blame on external application and inserted correct solution.

smon

if you remove the pragma statement, does it work then?

sb92075

COMMIT inside LOOP is sub-optimal implementation (increases elapsed time) & can result in ORA-01555 error.

user1983440

I have to use automous transaction in the inner function, so that the outer function can see any change performed by the inner one

Are you certain that this is true? I'm fairly certain that ANY data changes performed within the outer function, including subroutines called within it, should be "visible" within the outer function, regardless of whether the inner function is specified as an autonomous transaction. Typically, you would use an autonomous transaction when you want the inner function to commit only the changes enacted within itself and not the changes enacted in the outer function. The classic example is an audit logging function.

Example:

- logging function is autonomous
1. insert some record, but don't commit
2. call audit logging function (inserts/commits log record [into a separate log table] indicating that an insert occurred (in step #1), but DOES NOT commit record inserted in step 1 due to autonomous transaction)

- logging function is NOT autonomous
1. insert some record, but don't commit
2. call audit logging function (inserts/commits both log record and record inserted in step 1)

hth

davidp 2

Most times people use autonomous transactions they should not, and this seems to be one of the incorrect uses. The outer function will see whatever the inner function has done, without commit or autonomous transaction, because it is part of the same database session. Your inner function should not commit and not be an autonomous transaction. Its update should be committed by the caller of the outer function, once the appropriate unit of work has been done.

In PL/SQL, the default commit is COMMIT WRITE NOWAIT, which I think does mean the transaction might not be instantly visible to the outside transaction, because the call returns before the commit really finishes. The correct solution is to not do this as an autonomous transaction.

jutzin

Tanks, everybody, for your replies! Let me go through them one by one

smon asked:

if you remove the pragma statement, does it work then?

No, it does not, at least not, if I call the function from the outer function. In this case the insert statements in the inner function are not committed.
If I call the inner function like this

DECLARE
  NODE_IN SDO_GEOMETRY;
  GRAPH_IN NUMBER;
  v_Return NUMBER;
BEGIN

  NODE_IN := MDSYS.SDO_GEOMETRY(2001,<srid>,MDSYS.SDO_POINT_TYPE(<x>,<y>,<z>),NULL,NULL);
  GRAPH_IN := 3;

  v_Return := INSERT_NEW_NODE(
    NODE_IN => NODE_IN,
    GRAPH_IN => GRAPH_IN
  );
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  :v_Return := v_Return;
END;

, it works without autonomous transaction, but then again like this I do not use the handle to access the newly inserted data immediately to perform some other task with it.

sb92075 said:

COMMIT inside LOOP is sub-optimal implementation (increases elapsed time) & can result in ORA-01555 error.

Thanks, that was very helpful; I changed my code to commit outside of the loop, just before the return statement and it performs a lot faster now.

user1983440, regarding my statement

I think, I have to use automous transaction in the inner function, so that the outer function can see any change performed by the inner one.

asked:

Are you certain that this is true?

No, anything but certain. I should have said "It +seems+, I have to use autonomous transaction". I wish it would work without autonomous transaction, I think it actually should and I wonder why it does not. However, if I do not use autonomous transaction, the outer function seems to try to access the data that I have inserted in the inner function before it is committed, throws a no-data-found-exception, hence a rollback is performed.

davidp 2 said:

The outer function will see whatever the inner function has done, without commit or autonomous transaction [...] In PL/SQL, the default commit is COMMIT WRITE NOWAIT, which I think does mean the transaction might not be instantly visible to the outside transaction, because the call returns before the commit really finishes.

Yes, that is my notion, too. However, without autonomous transaction the inner function completes without error, then the outer uses the handles returned by the inner function to call the function <font face="courier">get_path_geom()</font> which cannot find the handles in the graph-table which raises an exception and causes a rollback.

Let me summarize: The inner function completes fine, without and with autonomous transaction and returns the handle. The inner function commits, if called directly, without and (of course) with autonomous transaction. The outer function does not see the data inserted by the inner function immediately, whether without or with autonomous transaction. If I let the outer function call the inner function twice (4 times, to be specific, but twice for each newly inserted row) from the outer function and do not use autonomous transaction, I get a no-data-found exception. If I let the outer function call the inner function twice and do use autonomous transaction, it works.

I agree with everything that was said about not using autonomous transaction in this case and I still want to solve this the right way. Any ideas are welcome!

BrendanP

This is far more likely to be a simple logical error in your code than anything to do with transaction processing. The second calls seem to return the handle from a different location in the code, maybe the first calls just return incorrect handles? Hard to say, I would add lots of write statements and try to debug it that way, omitting any commits.

davidp 2

without autonomous transaction the inner function completes without error, then the outer uses the handles returned by the inner function to call the function get_path_geom() which cannot find the handles in the graph-table which raises an exception and causes a rollback

That will happen if get_path_geom is also using an autonomous transaction - It can't see the uncommitted item. Try getting rid of all the "pragma autonomous transaction" clauses from the whole set of procedures and packages.

jutzin

Ok, here's the solution: The outer function get_path() calls a function get_path_geom(source, destination), which itself calls something like table(dijkstra(source, destination)), (omitted by me because thoroughly tested and found ok, my bad!) which does the work of finding the shortest path and returns a pipelined table. It turns out that this function for some reason does not get the scope of the outer function and hence does not see the transaction commited on the graph-table. After modifying the dijkstra()-function to return a list instead of a table, the whole thing suddenly works.

So this question has been answered; I would still like to know why the table function does not have the same scope as the rest of the transaction.

Edit: removed misleading blame on external application and inserted correct solution.