snipt

Ctrl+h for KB shortcuts

PL/pgSQL

Actualizar las secuencias en plsql/Oracle

declare

next_val NUMBER;

new_next_val NUMBER;

incr NUMBER;

max_key NUMBER;

v_code NUMBER;

v_errmsg VARCHAR2(64);
.
BEGIN

SAVEPOINT start_transaction;

-- get the max PK from the table that's using the sequence

select max(library_document_key) into max_key from library_documents;



-- then read nextval from the sequence

EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' into next_val;

DBMS_OUTPUT.PUT_LINE('ld2_seq next_val ' || next_val);

-- calculate the desired next increment for the sequence

incr := max_key - next_val + 1;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by ' || incr;

EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' into new_next_val;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by 1';

DBMS_OUTPUT.PUT_LINE('ld2_seq new_next_val ' || new_next_val);
commit;

EXCEPTION

WHEN OTHERS THEN
ROLLBACK to start_transaction;
DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errmsg);   
end; 
https://snipt.net/embed/86d04779daec71cc7a6629cd3ff3ce7e/
/raw/86d04779daec71cc7a6629cd3ff3ce7e/
86d04779daec71cc7a6629cd3ff3ce7e
plpgsql
PL/pgSQL
50
2019-08-24T16:55:15
True
False
False
Oct 09, 2012 at 08:22 AM
/api/public/snipt/48298/
actualizar-las-secuencias-en-plsqloracle
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">declare</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="n">next_val</span> <span class="n">NUMBER</span><span class="p">;</span> </span><span id="L-4"><a name="L-4"></a> </span><span id="L-5"><a name="L-5"></a><span class="n">new_next_val</span> <span class="n">NUMBER</span><span class="p">;</span> </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a><span class="n">incr</span> <span class="n">NUMBER</span><span class="p">;</span> </span><span id="L-8"><a name="L-8"></a> </span><span id="L-9"><a name="L-9"></a><span class="n">max_key</span> <span class="n">NUMBER</span><span class="p">;</span> </span><span id="L-10"><a name="L-10"></a> </span><span id="L-11"><a name="L-11"></a><span class="n">v_code</span> <span class="n">NUMBER</span><span class="p">;</span> </span><span id="L-12"><a name="L-12"></a> </span><span id="L-13"><a name="L-13"></a><span class="n">v_errmsg</span> <span class="n">VARCHAR2</span><span class="p">(</span><span class="mf">64</span><span class="p">);</span> </span><span id="L-14"><a name="L-14"></a><span class="mf">.</span> </span><span id="L-15"><a name="L-15"></a><span class="k">BEGIN</span> </span><span id="L-16"><a name="L-16"></a> </span><span id="L-17"><a name="L-17"></a><span class="k">SAVEPOINT</span> <span class="n">start_transaction</span><span class="p">;</span> </span><span id="L-18"><a name="L-18"></a> </span><span id="L-19"><a name="L-19"></a><span class="c1">-- get the max PK from the table that&#39;s using the sequence</span> </span><span id="L-20"><a name="L-20"></a> </span><span id="L-21"><a name="L-21"></a><span class="k">select</span> <span class="n">max</span><span class="p">(</span><span class="n">library_document_key</span><span class="p">)</span> <span class="k">into</span> <span class="n">max_key</span> <span class="k">from</span> <span class="n">library_documents</span><span class="p">;</span> </span><span id="L-22"><a name="L-22"></a> </span><span id="L-23"><a name="L-23"></a> </span><span id="L-24"><a name="L-24"></a> </span><span id="L-25"><a name="L-25"></a><span class="c1">-- then read nextval from the sequence</span> </span><span id="L-26"><a name="L-26"></a> </span><span id="L-27"><a name="L-27"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="s1">&#39;select ld2_seq.nextval from dual&#39;</span> <span class="k">into</span> <span class="n">next_val</span><span class="p">;</span> </span><span id="L-28"><a name="L-28"></a> </span><span id="L-29"><a name="L-29"></a><span class="n">DBMS_OUTPUT</span><span class="mf">.</span><span class="n">PUT_LINE</span><span class="p">(</span><span class="s1">&#39;ld2_seq next_val &#39;</span> <span class="o">||</span> <span class="n">next_val</span><span class="p">);</span> </span><span id="L-30"><a name="L-30"></a> </span><span id="L-31"><a name="L-31"></a><span class="c1">-- calculate the desired next increment for the sequence</span> </span><span id="L-32"><a name="L-32"></a> </span><span id="L-33"><a name="L-33"></a><span class="n">incr</span> <span class="o">:=</span> <span class="n">max_key</span> <span class="o">-</span> <span class="n">next_val</span> <span class="o">+</span> <span class="mf">1</span><span class="p">;</span> </span><span id="L-34"><a name="L-34"></a> </span><span id="L-35"><a name="L-35"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="s1">&#39;ALTER SEQUENCE ld2_seq increment by &#39;</span> <span class="o">||</span> <span class="n">incr</span><span class="p">;</span> </span><span id="L-36"><a name="L-36"></a> </span><span id="L-37"><a name="L-37"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="s1">&#39;select ld2_seq.nextval from dual&#39;</span> <span class="k">into</span> <span class="n">new_next_val</span><span class="p">;</span> </span><span id="L-38"><a name="L-38"></a> </span><span id="L-39"><a name="L-39"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="s1">&#39;ALTER SEQUENCE ld2_seq increment by 1&#39;</span><span class="p">;</span> </span><span id="L-40"><a name="L-40"></a> </span><span id="L-41"><a name="L-41"></a><span class="n">DBMS_OUTPUT</span><span class="mf">.</span><span class="n">PUT_LINE</span><span class="p">(</span><span class="s1">&#39;ld2_seq new_next_val &#39;</span> <span class="o">||</span> <span class="n">new_next_val</span><span class="p">);</span> </span><span id="L-42"><a name="L-42"></a><span class="k">commit</span><span class="p">;</span> </span><span id="L-43"><a name="L-43"></a> </span><span id="L-44"><a name="L-44"></a><span class="k">EXCEPTION</span> </span><span id="L-45"><a name="L-45"></a> </span><span id="L-46"><a name="L-46"></a><span class="k">WHEN</span> <span class="n">OTHERS</span> <span class="k">THEN</span> </span><span id="L-47"><a name="L-47"></a><span class="k">ROLLBACK</span> <span class="k">to</span> <span class="n">start_transaction</span><span class="p">;</span> </span><span id="L-48"><a name="L-48"></a><span class="n">DBMS_OUTPUT</span><span class="mf">.</span><span class="n">PUT_LINE</span><span class="p">(</span><span class="s1">&#39;Error code &#39;</span> <span class="o">||</span> <span class="n">v_code</span> <span class="o">||</span> <span class="s1">&#39;: &#39;</span> <span class="o">||</span> <span class="n">v_errmsg</span><span class="p">);</span> </span><span id="L-49"><a name="L-49"></a><span class="k">end</span><span class="p">;</span> </span></pre></div> </td></tr></table>
oracle, pl/sql, sequence, update