PL/SQL Dynamic SQL problem

From: M.Casey <caseym_at_phoenix.ie>
Date: Tue, 31 Aug 1999 15:07:45 +0100
Message-ID: <7qgnrm$mc7$1@ezekiel.eunet.ie>

I am having a problem when executing some Dynamic Sql. (Oracle 7.3.4) on NT4.

I have a procedure which normally inserts into the database but if it finds a duplicate primary key it should perform an update.

The code actually goes into the update section , binds the values , appears to execute successfully, but when checking the database , the row has not been updated.

An example of the code is below:

CREATE OR REPLACE procedure insert_allocate_update is   cid integer;
  cid2 integer;
  w integer;
  w2 integer;
begin

  cid := dbms_sql.open_cursor;
  cid2 := dbms_sql.open_cursor;

  for c2 in ( select server_name

                from P_S
               where priority = 0 )

  loop

    dbms_sql.PARSE( cid,

                    'insert into mis_allocate@ ' || c2.server_name || ' ' ||
                     '(  allocateId, createdby, reasoncode, workitemid,

commentId, createDate, childid, changeOwnership, ' ||

   'changePriority, insert_date, update_date ) ' ||

                     'values ( :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9,
:b10, :b11 )',
                    dbms_sql.native );

    dbms_sql.PARSE( cid2,
                    'update mis_allocate@' || c2.server_name || ' M_A ' ||
                       'set allocateid = :b1, ' ||
                           'createdby = :b2, ' ||
                           'reasoncode = :b3, ' ||
                           'workitemid = :b4, ' ||

'commentId = :b5, ' ||
'createDate = :b6, ' ||
'childid = :b7, ' ||
'changeOwnership = :b8, ' ||
'changePriority = :b9, ' ||

                           'insert_date = :b10, ' ||
                           'update_date = :b11 ' ||
                     'where allocateid = :b1 ' ||
                       'and M_A.update_date < :b11',
                    dbms_sql.native );

    for c1 in ( select allocateId, createdby, reasoncode, workitemid, commentId, createDate, childid, changeOwnership,

   changePriority, insert_date, update_date

                  from allocate
                 where insert_date > ( select * from mistest ) or

update_date > (select * from mistest))

    loop

      begin

        dbms_sql.bind_variable( cid, 'b1', c1.allocateid );
        dbms_sql.bind_variable( cid, 'b2', c1.createdby );
        dbms_sql.bind_variable( cid, 'b3', c1.reasoncode );
        dbms_sql.bind_variable( cid, 'b4', c1.workitemid );
        dbms_sql.bind_variable( cid, 'b5', c1.commentid );
        dbms_sql.bind_variable( cid, 'b6', c1.createdate );
        dbms_sql.bind_variable( cid, 'b7', c1.childid );
        dbms_sql.bind_variable( cid, 'b8', c1.changeownership );
        dbms_sql.bind_variable( cid, 'b9', c1.changepriority );
        dbms_sql.bind_variable( cid, 'b10', c1.insert_date );
        dbms_sql.bind_variable( cid, 'b11', c1.update_date );

        w := dbms_sql.EXECUTE( cid );

    exception

        when DUP_VAL_ON_INDEX then
        dbms_output.enable;
        dbms_output.put_line('duplicate pk');


    dbms_sql.bind_variable( cid2, 'b1', c1.allocateid );
        dbms_sql.bind_variable( cid2, 'b2', c1.createdby );
        dbms_sql.bind_variable( cid2, 'b3', c1.reasoncode );
        dbms_sql.bind_variable( cid2, 'b4', c1.workitemid );
        dbms_sql.bind_variable( cid2, 'b5', c1.commentid );
        dbms_sql.bind_variable( cid2, 'b6', c1.createdate );
        dbms_sql.bind_variable( cid2, 'b7', c1.childid );
        dbms_sql.bind_variable( cid2, 'b8', c1.changeownership );
        dbms_sql.bind_variable( cid2, 'b9', c1.changepriority );
        dbms_sql.bind_variable( cid2, 'b10', c1.insert_date );
        dbms_sql.bind_variable( cid2, 'b11', c1.update_date );

    w2 := dbms_sql.EXECUTE(cid2);
 commit;

      end;

    end loop;

  end loop;

  dbms_sql.close_cursor( cid );
  dbms_sql.close_cursor( cid2 );

end insert_allocate_update;
/ Received on Tue Aug 31 1999 - 09:07:45 CDT