conversion from oracle to sql server

Question

Post reply

conversion from oracle to sql server

subhasish ghosh

Valued Member

Points: 53
More actions
June 16, 2005 at 2:36 am

#88672

I have one package specification anf two triggers related. I am unable to think as to how I should convert to sql server. I am new in sql server..kindly help.

CREATE OR REPLACE PACKAGE pkg_uad_login
as
   type login_history_record is RECORD
       (txn_user     VARCHAR2(10),
     txn_date     DATE,
  txn_identify VARCHAR2(20),
  valid_user   VARCHAR2(1),
  failed_Count BINARY_INTEGER
    );
   type login_history_table IS TABLE OF login_history_record INDEX BY BINARY_INTEGER;
   g_login_history      login_history_table;
   g_count              binary_integer := 0;
END pkg_uad_login;
/
CREATE OR REPLACE TRIGGER uadulh_trig_bi
   BEFORE INSERT on uadulh_user_login_history
   REFERENCING NEW AS NEW
   FOR EACH ROW
DECLARE
   idx         BINARY_INTEGER;
   found_idx   BINARY_INTEGER := 0;
BEGIN
   IF :new.success = 'N' THEN
     pkg_uad_login.g_count := pkg_uad_login.g_count + 1;
IF pkg_uad_login.g_count = 1 THEN
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_user     := :new.txn_user;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_date     := :new.txn_date;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_identify := :new.txn_identify;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).valid_user   := :new.valid_user;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).failed_count := 1;
ELSE
        FOR idx IN 1..pkg_uad_login.g_count - 1 LOOP
     IF :new.TXN_USER = pkg_uad_login.g_login_history(idx).txn_user THEN
  found_idx := idx;
     END IF;
        END LOOP;
     IF found_idx != 0 THEN
  pkg_uad_login.g_count := pkg_uad_login.g_count - 1;
  pkg_uad_login.g_login_history(found_idx).txn_date     := :new.txn_date;
  pkg_uad_login.g_login_history(found_idx).txn_identify := :new.txn_identify;
  pkg_uad_login.g_login_history(found_idx).failed_count := pkg_uad_login.g_login_history(found_idx).failed_count + 1;
     ELSE
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_user     := :new.txn_user;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_date     := :new.txn_date;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).txn_identify := :new.txn_identify;
  pkg_uad_login.g_login_history(pkg_uad_login.g_count).failed_count := 1;
         pkg_uad_login.g_login_history(pkg_uad_login.g_count).valid_user   := :new.valid_user;
     END IF;
END IF;
   ELSE
     pkg_uad_login.g_count := 0;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER uadulh_trig_ai
   AFTER INSERT on uadulh_user_login_history
DECLARE
   idx         BINARY_INTEGER;
   cnt         BINARY_INTEGER;
BEGIN
   cnt := pkg_uad_login.g_count;
   FOR idx IN 1..cnt LOOP
      IF pkg_uad_login.g_login_history(idx).failed_count >= 3 THEN
     --pkg_uad_login.g_login_history(idx).failed_count := 0;
            pkg_uad_login.g_count := 0;
         BEGIN
    IF pkg_uad_login.g_login_history(idx).valid_user = 'Y' THEN
      UPDATE UADUSR_USER
   SET    ENABLED_FLAG = 'D'
   WHERE USER_ID = pkg_uad_login.g_login_history(idx).txn_user;
      INSERT INTO UADFLO_FAILED_LOGIN (txn_user, txn_date, txn_identify, valid_user)
      VALUES
      (
              pkg_uad_login.g_login_history(idx).txn_user,
              pkg_uad_login.g_login_history(idx).txn_date,
              pkg_uad_login.g_login_history(idx).txn_identify,
     'Y'
      );
    ELSE
      INSERT INTO UADFLO_FAILED_LOGIN (txn_user, txn_date, txn_identify, valid_user)
      VALUES
      (
              pkg_uad_login.g_login_history(idx).txn_user,
              pkg_uad_login.g_login_history(idx).txn_date,
              pkg_uad_login.g_login_history(idx).txn_identify,
       'N'
      );
    END IF;
         EXCEPTION
     WHEN OTHERS THEN
  NULL;
         END;
      END IF;
   END LOOP;
END;
/

Thanks in advance

subu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

subhasish ghosh Valued Member Points: 53 More actions · Answer 1

Nobody to answer????

Is there anything in sql server that defines variable which will persist in session.

In oracle it is pretty good . You define variable in package specification and that can be manipulated and exist for the session.

KIndly guide.

Also if I create global temporary table as for eg. ##table_name(double hash) in procedure, it is accessible for all the users. Is there something that will be accessible by the user only who actually executes the procedure.

#table_name(single hash) does not help as its life is within the procedure only.

thanks subu

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Hi Subu,

Don't think you can do that in SQL Server.

The concept of packgae does not exists.

Maybe you can achieve the same result doing it another way.

Regards,

Carl

subhasish ghosh Valued Member Points: 53 More actions · Answer 3

Yes carl I know there is no concept of package. But how should i go for it?? I could not find a way where i can implement this with sql server.

Rgds,

subu

sql92 SSC Enthusiast Points: 114 More actions · Answer 4

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx