Amazon books

Tuesday, March 10, 2015

Using DBMS_PASSTHROUGH to run command in SQL Server from Oracle

This simple example below update a table in SQL Server from Oracle:

declare
   l_num_rows number;
begin
  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('USE ORAIntegration_V2_test');
  commit;
  execute immediate 'alter session close database link TMMASTER';
  commit;
 
  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('UPDATE trans SET    a=333 WHERE  a=222 ');
  commit;
 
--  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('COMMIT TRANSACTION');
  commit;


  execute immediate 'alter session close database link TMMASTER';
  commit;
end;



select * from trans@TMMASTER;


Best Regards,
Paulo Portugal

No comments:

Post a Comment