Elxis CMS Forum

Support => Database => Topic started by: muharihar on January 11, 2009, 05:49:05

Title: Oracle :: Fix Sequence Problem (Oracle PL/SQL Script)
Post by: muharihar on January 11, 2009, 05:49:05

hi guys...

This is a simple script to Fix Oracle Sequence Problem (but still in Oracle PL/SQL Script, php script is coming soon->i hope  ;D ) ).

After finishing your elxis installation process with oracle as your elxis database, try this step to fix oracle sequence problem.

1. login to your oracle database with user "sys", and then execute this query/script (replace "elxis_db_user" with your elxis oracle database user ):

Code: [Select]

grant select on dba_constraints to elxis_db_user;
grant select on dba_cons_columns to elxis_db_user;
grant execute on dbms_sql to elxis_db_user;
grant create procedure to elxis_db_user;

2. login to oracle with your Elxis Database User (in this case = elxis_db_user),and then create procedure FIX_SEQUENCE_PROBLEM by execute this script:

Code: [Select]

CREATE OR REPLACE PROCEDURE FIX_SEQUENCE_PROBLEM
IS
   CURSOR seq_cur
   IS SELECT   a.owner, a.table_name, a.constraint_name, a.constraint_type, b.column_name, b.POSITION, c.SEQUENCE_NAME
              FROM dba_constraints a, dba_cons_columns b,
              (select replace(SEQUENCE_NAME,'SEQ_','') seq_table, SEQUENCE_NAME from user_sequences) c
             WHERE a.constraint_name = b.constraint_name
               AND a.owner = 'ELXIS_DB_USER' /* replace "ELXIS_DB_USER" with your oracle user/schema, must Upper Case*/
               AND a.constraint_type = 'P'
               and a.table_name = c.seq_table
          ORDER BY a.owner, a.table_name, a.constraint_name, b.POSITION;
     
   seq_rec  seq_cur%ROWTYPE;
   iqnore integer;
   
   sql_max varchar2(4000);
   sql_max_cur integer;
   max_id_var number(10);
   
   sql_seq_next varchar(4000);
   sql_seq_next_cur integer;
   seq_last_var number(10);
BEGIN
   OPEN seq_cur;
   LOOP
      FETCH seq_cur INTO seq_rec;
      EXIT WHEN seq_cur%NOTFOUND;
     
      sql_max := 'select max(nvl('||seq_rec.column_name||',1)) as MAX_ID from '||seq_rec.table_name;     
      sql_max_cur := dbms_sql.open_cursor;
      dbms_sql.parse(sql_max_cur,sql_max,dbms_sql.native);
      dbms_sql.define_column(sql_max_cur,1,max_id_var);
      iqnore := dbms_sql.execute(sql_max_cur);
      IF DBMS_SQL.FETCH_ROWS(sql_max_cur)>0 THEN
            dbms_sql.column_value(sql_max_cur,1,max_id_var);
      end if;
     
      sql_seq_next := 'select '||seq_rec.SEQUENCE_NAME||'.NEXTVAL from dual';
      sql_seq_next_cur  := dbms_sql.open_cursor;
      dbms_sql.parse(sql_seq_next_cur,sql_seq_next,dbms_sql.native);
      dbms_sql.define_column(sql_seq_next_cur,1,seq_last_var);
      iqnore := dbms_sql.execute(sql_seq_next_cur);
      IF DBMS_SQL.FETCH_ROWS(sql_seq_next_cur)>0 THEN
            dbms_sql.column_value(sql_seq_next_cur,1,seq_last_var);
      end if;
     
      if (seq_last_var <= max_id_var) then
         
         while (seq_last_var <= max_id_var) loop
            if seq_last_var > max_id_var then
                exit;
            end if;
           
            sql_seq_next := 'select '||seq_rec.SEQUENCE_NAME||'.NEXTVAL from dual';
            sql_seq_next_cur  := dbms_sql.open_cursor;
            dbms_sql.parse(sql_seq_next_cur,sql_seq_next,dbms_sql.native);
            dbms_sql.define_column(sql_seq_next_cur,1,seq_last_var);
            iqnore := dbms_sql.execute(sql_seq_next_cur);
            IF DBMS_SQL.FETCH_ROWS(sql_seq_next_cur)>0 THEN
                dbms_sql.column_value(sql_seq_next_cur,1,seq_last_var);
            end if;
            if dbms_sql.is_open(sql_seq_next_cur) then
                dbms_sql.close_cursor(sql_seq_next_cur);
            end if;
           
         end loop;
      end if;
     
   END LOOP;
   CLOSE seq_cur;
   if dbms_sql.is_open(sql_max_cur) then
      dbms_sql.close_cursor(sql_max_cur);
   end if;
   if dbms_sql.is_open(sql_seq_next_cur) then
      dbms_sql.close_cursor(sql_seq_next_cur);
   end if;
EXCEPTION
   when others then
   begin
        CLOSE seq_cur;
        if dbms_sql.is_open(sql_max_cur) then
            dbms_sql.close_cursor(sql_max_cur);
        end if;
        if dbms_sql.is_open(sql_seq_next_cur) then
            dbms_sql.close_cursor(sql_seq_next_cur);
        end if;
   end;
END FIX_SEQUENCE_PROBLEM;

3. Execute procedure FIX_SEQUENCE_PROBLEM, by execute this script:

Code: [Select]

begin
  FIX_SEQUENCE_PROBLEM;
end;

Happy try....  ;D

---
oops forget for this script:
4. login to your oracle database with user "sys", and then execute this query/script (replace "elxis_db_user" with your elxis oracle database user ), only if you want to revoke step 1:

Code: [Select]

revoke select on dba_constraints from elxis_db_user;
revoke  select on dba_cons_columns from elxis_db_user;
revoke  execute on dbms_sql from elxis_db_user;
revoke  create procedure from elxis_db_user;