I had to write an Oracle script template for work to kind of create a guideline for our developers since we are getting inputs from ourselves, DBA standards, and business requirements from our BA’s and customers.<\/p>\n
Anyway, one thing they wanted was batch commits . . . so I wrote this quick little piece:<\/p>\n
It creates a temp table, populates it, and puts a DBMS_OUTPUT line for whatever number I set the commit at . . . pretty self explanatory.<\/p>\n
CREATE\u00a0\u00a0 GLOBAL\u00a0 TEMPORARY TABLE temp_Cursor_Count\u00a0 ( DECLARE<\/span><\/p>\n curVariable Number;<\/span><\/p>\n cursorCount Number(10) := 0; CURSOR tt is begin<\/span><\/p>\n –populate temp table FOR record_tt IN tt LOOP cursorCount := cursorCount + 1;<\/span><\/p>\n select MOD(cursorCount,cursorCommit) into cursorMod from dual;<\/span><\/p>\n if cursorMod=0 then END LOOP; truncate table temp_Cursor_Count; I had to write an Oracle script template for work to kind of create a guideline for our developers since we are getting inputs from ourselves, DBA standards, and business requirements from our BA’s and customers. Anyway, one thing they wanted was batch commits . . . so I wrote this quick little piece: It […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/81"}],"collection":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=81"}],"version-history":[{"count":8,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":83,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions\/83"}],"wp:attachment":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\ntestNumber Number(10)
\n) ON COMMIT PRESERVE ROWS;
\ncommit;<\/span><\/p>\n
\ncursorCommit Number(10) := 1000;
\ncursorMod Number(10) := 1;<\/span><\/p>\n
\nselect * from temp_Cursor_Count;<\/span><\/p>\n
\nFor i in 1..9999
\nLoop
\ninsert into temp_Cursor_Count(testNumber) values (i);
\nend loop;
\ncommit;<\/span><\/p>\n
\n–INITIALIZE Cursor values
\ncurVariable\u00a0\u00a0\u00a0\u00a0\u00a0 :=record_tt.testNumber;<\/span><\/p>\n
\n–Normally this is where your COMMIT would occur.
\nDBMS_OUTPUT.PUT_LINE(cursorCount);
\nend if;<\/span><\/p>\n
\nend;<\/span><\/p>\n
\ncommit;
\ndrop table temp_Cursor_Count;
\ncommit;<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"