1

I am working in a Oracle 11g database migration to SQL Server 2012, I did the migration using SSMA v5.3 and all works fine. However, I did re-write code in some objects like triggers and stored procedures because the SSMA did a standard migration.

But my questions is about this: The Oracle Db have some packages that use global variables like this code:

CREATE OR REPLACE package OPS_TRACKING.pkg_trade_data as

-- packaged variable to be used by triggers not to execute during
-- archival and resoring process of the trade data
archive_restore_flag  varchar2(1) := 'N';
procedure p_archive_trade_data( p_trade_id    number );

In my case this global variable is called for some triggers like it:

    CREATE OR REPLACE TRIGGER "OPS_TRACKING"."TG_TRADE_GROUP_AER_DI" 
after insert or delete on ops_tracking.trade_group
for each row
declare
  v_id  number;
  v_trade_id number;
begin
   If pkg_trade_data.archive_restore_flag = 'N' Then

      if inserting then
         v_trade_id := :new.trade_id;
      else
         v_trade_id := :old.trade_id;
      end if;

      select id into v_id from ops_tracking.trade_summary
      where trade_id = v_trade_id
      for update nowait;

      update ops_tracking.trade_summary
         set transaction_seq=ops_tracking.seq_trade_summary_transaction.nextval
            ,last_update_timestamp_gmt = sysdate
       where trade_id = v_trade_id;

   End If; -- archive_restore_flag
end tg_trade_group_aer_di;
/

As you can see in this trigger the global variable (pkg_trade_data.archive_restore_flag = 'N') is used for take a decision and based in their value the oracle trigger execute the action.

I would like to know which is the equivalent or which are my options to migrate this code to SQL 2012.

I appreciate your comments and advice's.

  • Package variables are not global, they persist for the duration of a session. In SQLServer you have similar behaviour (in your case) with ` CONTEXT_INFO ` - technet.microsoft.com/en-US/library/ms189252(v=SQL.105).aspx – a1ex07 Jul 29 '15 at 16:54
  • Alternatively you can use a local temporary table to store the value, as it has a similar lifecycle. – mustaccio Jul 29 '15 at 16:57
  • But using temporary table i will need the application code create the temp table for get a value or how you think I can save the value in the temp table for use it in the trigger. Thanks – jmonterohn Jul 29 '15 at 17:00
  • Befor the trigger is created the temp table has to be created. Where is the problem? – miracle173 Jul 29 '15 at 22:02
  • @a1ex07 I think one should avoid CONTEXT_INFO. there is only one CONTEXT_INFO. So the conept cannot be extended to a second package variable. If a script uses CONTEXT_VARIABLE the behaviour of the trigger will be influenced. – miracle173 Jul 29 '15 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.