{"id":4721,"date":"2023-02-16T14:37:44","date_gmt":"2023-02-16T05:37:44","guid":{"rendered":"http:\/\/haisins.synology.me\/wordpress\/?p=4721"},"modified":"2023-02-16T14:37:44","modified_gmt":"2023-02-16T05:37:44","slug":"whenever-sqlerror-%ec%97%ac%eb%9f%ac-%ec%a4%84-%ec%9d%98-sql-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8-%eb%ac%b8%ec%9e%a5-%ec%88%98%ed%96%89-%ec%8b%9c-%ec%a4%91%ea%b0%84-%ec%97%90%ec%84%9c-%ec%97%90","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4721","title":{"rendered":"whenever sqlerror … \uc5ec\ub7ec \uc904 \uc758 sql \uc2a4\ud06c\ub9bd\ud2b8 \ubb38\uc7a5 \uc218\ud589 \uc2dc \uc911\uac04 \uc5d0\uc11c \uc5d0\ub7ec \ubc1c\uc0dd\ud558\uba74 rollback \ucc98\ub9ac \ud560 \uc218 \uc788\uac8c \ud558\ub294 \uae30\ub2a5"},"content":{"rendered":"

sqlplus \uc5d0\uc11c \uc5ec\ub7ec \uc904\uc758 *.sql \ubb38\uc744 \uc218\ud589 \uc2dc\ud0ac \ub54c \uc624\ub798 \uac78\ub9ac\uac70\ub098 \uc904 \uc774 \ub9ce\uc744 \uacbd\uc6b0 \uc911\uac04 \uc904 \uc5d0 SQL\ubb38 \uc218\ud589 \uc2e4\ud328 \uc2dc \uc790\ub3d9 \ub864 \ubc31 \ud574\uc8fc\ub294 \uae30\ub2a5\uc774 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

WHENEVER OSERROR EXIT<\/strong><\/span><\/pre>\n
WHENEVER SQLERROR EXIT<\/strong><\/span>\r\n\r\n\r\n\ucd9c\ucc98 :<\/pre>\n
https:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14357\/ch12052.htm<\/a><\/p>\n
———————————————————————————————————-<\/p>\n
\n
\n
\n
\n
\ud655\uc7a5\uc790 *.sql \ubb38\uc7a5 \uc2dc\uc791 \ubd80\ubd84\uc5d0\u00a0 \ub9e8 \uccab\uc904 \uacfc \ub458\uc9f8 \uc904\uc5d0 \uae30\uc220\ud558\uc5ec \uc900\ub2e4.<\/span><\/p>\n
<\/span><\/p>\n
whenever sqlerror exit rollback<\/b><\/span><\/p>\n
whenever sqlerror exit commit<\/b><\/span><\/p>\n
<\/span><\/p>\n
select * from err_test ;<\/span><\/p>\n
<\/span><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\"\"<\/a><\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
<\/span><\/p>\n
truncate table err_test ;<\/span><\/p>\n
<\/span><\/p>\n
<sqlerror_test.sql><\/span><\/p>\n
<\/span><\/p>\n
whenever sqlerror exit rollback<\/b><\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘A’,’AA’,1,1,’AA’)<\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘B’,’BB’,2,2,’BB’)<\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘C’,’CC’,3,33,’CC’)<\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘D’,’DDD’,4,4,’DD’)<\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘E’,’EE’,5,5,’EE’)<\/span><\/p>\n
<\/span><\/p>\n
commit;<\/span><\/p>\n
<\/span><\/p>\n
<< test00 \uc720\uc800\ub85c \ub85c\uadf8\uc778 \ud558\uc5ec sqlerror_test.sql \uc744 \uc2e4\ud589 >><\/span><\/p>\n
\uc5d0\ub7ec \ubc1c\uc0dd\uc73c\ub85c \uc778\ud558\uc5ec \uc815\uc0c1\uc801\uc778 \ub370\uc774\ud130 \uc870\ucc28\ub3c4 insert \ub418\uc9c0 \ubabb\ud558\uace0 rollback \ub428\uacfc \ub3d9\uc2dc\uc5d0 sqlplus \ub97c exit \ud55c\ub2e4<\/span><\/b><\/p>\n
\ub2e4\uc2dc sqlplus test00 \uc720\uc800\ub85c \ub85c\uadf8\uc778 \ud558\uc5ec err_test \ud14c\uc774\ube14\uc744 \uc870\ud654\ud558\uc5ec\ubcf4\uba74 \ub370\uc774\ud130\uac00 \uc5c6\uc74c\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n
<\/span><\/p>\n
PS D:\\RMAN_BACKUP> sqlplus test00\/test00<\/span><\/p>\n
SQL*Plus: Release 11.2.0.1.0 Production on \uc77c 5\uc6d4 5 09:18:31 2019<\/span><\/p>\n
Copyright (c) 1982, 2010, Oracle. All rights reserved.<\/span><\/p>\n
<\/span><\/p>\n
\ub2e4\uc74c\uc5d0 \uc811\uc18d\ub428:<\/span><\/p>\n
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production<\/span><\/p>\n
With the Partitioning, OLAP, Data Mining and Real Application Testing options<\/span><\/p>\n
<\/span><\/p>\n
ORCL@TEST00> @sqlerror_test<\/span><\/p>\n
1 \uac1c\uc758 \ud589\uc774 \ub9cc\ub4e4\uc5b4\uc84c\uc2b5\ub2c8\ub2e4.<\/span><\/p>\n
1 \uac1c\uc758 \ud589\uc774 \ub9cc\ub4e4\uc5b4\uc84c\uc2b5\ub2c8\ub2e4.<\/span><\/p>\n
<\/span><\/p>\n
insert into err_test(c1,c2,c3,c4,c5) values(‘C’,’CC’,3,33,’CC’)<\/span><\/p>\n
*<\/span><\/p>\n
1\ud589\uc5d0 \uc624\ub958:<\/span><\/p>\n
ORA-01438: \uc774 \uc5f4\uc5d0 \ub300\ud574 \uc9c0\uc815\ub41c \uc804\uccb4 \uc790\ub9bf\uc218\ubcf4\ub2e4 \ud070 \uac12\uc774 \ud5c8\uc6a9\ub429\ub2c8\ub2e4.<\/span><\/p>\n
<\/span><\/p>\n
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production<\/span><\/p>\n
With the Partitioning, OLAP, Data Mining and Real Application Testing options\uc5d0\uc11c \ubd84\ub9ac\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/span><\/p>\n
PS D:\\RMAN_BACKUP><\/span><\/p>\n
<\/span><\/p>\n
PS D:\\RMAN_BACKUP> sqlplus test00\/***<\/span><\/p>\n
<\/span><\/p>\n
SQL> select * from err_test ;<\/span><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\"\"<\/a><\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
0 rows selected.<\/span><\/p>\n
<\/span><\/p>\n
<\/span>whenever <condition> <action><\/b><\/span><\/p>\n
<\/b><\/span><\/p>\n
<condition><\/b><\/span><\/p>\n
1) SQLWARNING<\/span><\/p>\n
2) SQLERROR<\/span><\/p>\n
3) NOT FOUND — fetch, into \uc808\uc5d0\uc11c \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ud589\uc774 \ubc1c\uacac\ub418\uc9c0 \uc54a\uc558\uc744 \uacbd\uc6b0<\/span><\/p>\n
<action><\/b><\/span><\/p>\n
1) continue — \uc544\ubb34\ub7f0 \ub300\uc751 \uc5c6\uc774 \uadf8\ub0e5 \ub2e4\uc74c\uc73c\ub85c \uc9c4\ud589, whenever\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc740 \uac83 \uacfc \ub3d9\uc77c<\/span><\/p>\n
2) do \ud568\uc218 \uba85( ) — \uc624\ub958\uc5d0 \ub300\uc751\ud558\ub294 \ud568\uc218\ub97c \ud638\ucd9c<\/span><\/p>\n
, \uc774 \ud568\uc218\uac00 \uc885\ub8cc\ud558\uba74 \uc624\ub958\uac00 \ubc1c\uc0dd\ud55c \ub2e4\uc74c\uc73c\ub85c \uc81c\uc5b4\uac00 \uc9c4\ud589\ub428<\/span><\/p>\n
3) do break — \ubc18\ubcf5\ubb38\uc758 break<\/span><\/p>\n
4) do continue — \ubc18\ubcf5\ubb38\uc758 continue<\/span><\/p>\n
5) goto label name<\/span><\/p>\n
6) stop — \ud504\ub85c\uadf8\ub7a8 \uc911\uc9c0, \ud2b8\ub79c\uc7ad\uc158 \ub864\ubc31<\/span><\/p>\n
<\uc120\uc5b8 \uc608><\/span><\/p>\n
exec sql whenever sqlerror continue ;<\/span><\/p>\n
exec sql whenever sqlwarning continue ;<\/span><\/p>\n
exec sql whenever sqlwarning goto warning_label ;<\/span><\/p>\n
warning_label;<\/span><\/p>\n
dbms_output.put_line(“SQL \uacbd\uace0 \ubc1c\uc0dd”) ;<\/span><\/p>\n
<Shell \ub85c \uc2e4\ud589\uc2dc><\/b><\/span><\/p>\n
sqlplus -s test00\/test00 << EOF<\/span><\/p>\n
spool spool_123.out<\/span><\/p>\n
set echo on<\/span><\/p>\n
whenever sqlerror exit rollback<\/b><\/span><\/p>\n
whenever sqlerror exit 1<\/span><\/b><\/p>\n
whenever oserror exit 1<\/span><\/b><\/p>\n
. . . . .<\/span><\/p>\n
EOF<\/span><\/p>\n
<crontab> \ub4f1\ub85d — \ub9e4\uc77c \uc0c8\ubcbd 2 \uc2dc\uc5d0 \uc2e4\ud589<\/span><\/p>\n
0 2 * * * su – oracle -c \/ORA_JOB\/test01.sh<\/span><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
sqlplus \uc5d0\uc11c \uc5ec\ub7ec \uc904\uc758 *.sql \ubb38\uc744 \uc218\ud589 \uc2dc\ud0ac \ub54c \uc624\ub798 \uac78\ub9ac\uac70\ub098 \uc904 \uc774 \ub9ce\uc744 \uacbd\uc6b0 \uc911\uac04 \uc904 \uc5d0 SQL\ubb38 \uc218\ud589 \uc2e4\ud328 \uc2dc \uc790\ub3d9 \ub864 \ubc31 \ud574\uc8fc\ub294 \uae30\ub2a5\uc774 \uc788\uc2b5\ub2c8\ub2e4.   WHENEVER OSERROR EXIT WHENEVER SQLERROR EXIT \ucd9c\ucc98 : https:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14357\/ch12052.htm ———————————————————————————————————- \ud655\uc7a5\uc790 *.sql \ubb38\uc7a5 \uc2dc\uc791 \ubd80\ubd84\uc5d0\u00a0 \ub9e8 \uccab\uc904 \uacfc \ub458\uc9f8 \uc904\uc5d0 \uae30\uc220\ud558\uc5ec \uc900\ub2e4. whenever sqlerror exit rollback […]<\/p>\n","protected":false},"author":1,"featured_media":4522,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[1279,1280,1276,1277,1278,1275],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2021\/11\/troubleshoot.png?fit=680%2C345","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4721"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4721"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4721\/revisions"}],"predecessor-version":[{"id":4722,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4721\/revisions\/4722"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/4522"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4721"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}