3

I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle.

A client wishes to use Oracle, which I have limited experience of. I've got a local install of Oracle 11g r2.

I used a script to transfer data from MySql into Oracle, including the ID to keep the references.

But now when creating a record I get the error message : ORA-00001: unique constraint (SCHEMANAME.TABLENAME_ID_PK) violated

I'm guessing the sequences weren't updated when I inserted the ID's?

Is there an easy way to update all the table sequences to the last ID number used + 1 in each table?

Unless it's something else?

UPDATE : I can use this to get the last number in the sequence

SELECT sequence_name, last_number
FROM dba_sequences
WHERE sequence_owner = 'SCHEMA_NAME'
AND sequence_name LIKE 'V_%'

The last_number column has values but it looks like they are lower than the last id used in the table.

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle. Here is some Moodle/PHP code that will reset all the sequences:

$todb = moodle_database::get_driver_instance($dbtype, $dblibrary);
$todb->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
$totables = $todb->get_tables(false);
$tomgr = $todb->get_manager();

foreach ($totables as $table) {
    mtrace($table);
    try {
        $tomgr->reset_sequence($table);
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.
        mtrace($e->getMessage());
        continue;
    }
}

UPDATE 3 : This is basically what the reset sequence function does

$value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
$value++;

// Calculate a sequence name from the tablename.
$seqname = $this->getSequenceFromDB($xmldb_table);
if (!$seqname) {
    // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
    $seqname = $this->getNameForObject($table, 'id', 'seq');
}

$this->mdb->execute("DROP SEQUENCE $seqname");
$this->mdb->execute("CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");
  • Here's what you must do : 1. import the data, 2. adjust the sequence to the data. This is to be done for each table separately. After that, each INSERT should do the needed to use the correct sequence value. Do you have triggers already ? If no, the above should be best solution for you. You're asking US if the sequence is imported ? Well, do YOU import it ? – tvCa Dec 29 '14 at 17:17
  • check the php function $tomgr->reset_sequenceto find out what it actually does. – ora-600 Dec 30 '14 at 7:30
3

First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

  1. Get the max value from the table
  2. update/recreate the sequence

The script could look like this:

declare
  maxval number;
begin
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';
end;
/

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.

  • Super - can I get the sequence name for the table name? – Russell England Dec 29 '14 at 14:17
  • 2
    @Russell England : There is no "sequence name for table name" in Oracle. Sequence is not related directly to a table - you may use the same sequence to generate values in multiple tables or columns. Normally, you adopt some naming convention for sequences, say [table_name]_SEQ , so it's more readable. – a1ex07 Dec 29 '14 at 14:52
  • 2
    Yes, a1ex07 is right. To find out which sequence is related to which table, you have to check the database design which was created by the developers. But there might be an option. Check if the tables have triggers which populate the sequence controlled columns. If they do exist then use them to find out which sequence they get their value from. – ora-600 Dec 29 '14 at 15:50
1

Of course you need to find a way to match the sequence name with the table name, since there's not a connection or constraint enforced by the RDBMS.

When you have it, you may use this procedure to reset a sequence value:

create or replace procedure proc_reset_sequence
( p_seq_owner in varchar2,
  p_seq_name in varchar2,
  p_target_number in number
) AS
  v_save_cache   number;
  v_desired_val number;
  v_last_number number;
  v_last_increment number;

  v_sqlstr varchar2(2000);
BEGIN
  SELECT cache_size, last_number, increment_by into v_save_cache, v_last_number, v_last_increment
  from all_sequences
  where sequence_owner = p_seq_owner and sequence_name = p_seq_name;

  v_desired_val := p_target_number - v_last_number;
  v_sqlstr := 'alter sequence ' || p_seq_owner || '.' || p_seq_name || ' nocache' || ' increment by ' || to_char(v_desired_val);

  execute immediate v_sqlstr;

  v_sqlstr := 'begin select ' || p_seq_owner || '.' || p_seq_name || '.nextval into :1 from dual; end;';

  execute immediate v_sqlstr using out v_desired_val;

  v_sqlstr := 'alter sequence ' || p_seq_owner || '.' || p_seq_name  || 
    case when v_save_cache > 1 then 
      ' cache ' || to_char(v_save_cache) 
    else ' nocache' end 
    || ' increment by ' || to_char(v_last_increment);

  execute immediate v_sqlstr;

end;
/
-2
declare
  maxval number;
begin
  select max(id) + 1 into maxval from <tablename>;
  execute immediate 'DROP SEQUENCE  <sequencename> ';
  execute immediate 'CREATE SEQUENCE  <sequencename>  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH ' || maxval || ' CACHE 20 NOORDER  NOCYCLE ';
end;
  • 1
    How does that differ from existing answers? – mustaccio Mar 23 '17 at 18:07
  • I assume that you're trying to provide an answer using a sequence (which the OP did post about). However, this just shows how to create a sequence; and, it's not clear that it really needs to be based off max(id) - max(reference) might actually be more to the point. It might help the OP to see how to use the sequence from here. – RDFozz Mar 23 '17 at 19:26
-2
DECLARE
max_id     NUMBER;
sql_text   NVARCHAR2 (2500);
sql_info   NVARCHAR2 (1000);
BEGIN
DBMS_OUTPUT.enable (1000000);
FOR x
  IN (SELECT    'ALTER TABLE '
             || table_name
             || ' MODIFY '
             || column_name
             || '  GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH '
                q,
             owner,
             table_name,
             column_name
        FROM ALL_tab_columns
       WHERE owner = 'QA' AND identity_column = 'YES')
 LOOP
  sql_text :=
        'select MAX ('
     || x.column_name
     || ')+1  into :max_id from '
     || x.owner
     || '.'
     || x.table_name;
  EXECUTE IMMEDIATE sql_text INTO max_id;
  IF max_id > 0
  THEN
     sql_info := x.q || max_id || ')'; --get query for update auto generated 
   sequences value to themax(id) for all tables
     --sql_info := 'select '|| x.sequance_name || '.nextval  from dual'; -- 
  check next values for all sequances
     DBMS_OUTPUT.put_line (sql_info);
  ELSE
     sql_info := x.q || ' 1 )'; --if table is empty
     DBMS_OUTPUT.put_line (sql_info);
  END IF;
  END LOOP;
    END;

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.