{"id":427,"date":"2021-04-13T11:52:37","date_gmt":"2021-04-13T02:52:37","guid":{"rendered":"http:\/\/dev.e-luda.com\/dev-note\/?p=636"},"modified":"2022-04-22T14:20:15","modified_gmt":"2022-04-22T05:20:15","slug":"oracle-dml-%eb%ac%b8%ec%9e%a5-%ec%83%9d%ec%84%b1-1","status":"publish","type":"post","link":"http:\/\/dev.e-luda.com\/dev-note\/?p=427","title":{"rendered":"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1"},"content":{"rendered":"\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;mode&quot;:&quot;sql&quot;,&quot;mime&quot;:&quot;text\/x-sql&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:false,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;language&quot;:&quot;SQL&quot;,&quot;modeName&quot;:&quot;sql&quot;}\">WITH tempTbl AS (\n    SELECT a.COLUMN_ID AS colId \n         , a.COLUMN_NAME AS colName\n         , LOWER(SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),1,1))||SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),2,LENGTH(a.COLUMN_NAME)-1) AS camel \n         , ('\/*'||c.COMMENTS || ','|| a.DATA_TYPE ||'('||a.DATA_LENGTH ||')'||'*\/') AS cmt\n         , ('\/*'||c.COMMENTS ||'*\/') AS cmt2\n         , c.COMMENTS AS orgCmt\n         , a.DATA_TYPE AS dataType\n         , a.DATA_LENGTH AS dataLen\n      FROM ALL_TAB_COLUMNS a\n      LEFT OUTER JOIN ALL_COL_COMMENTS c\n        ON a.OWNER = c.OWNER\n       AND a.TABLE_NAME = c.TABLE_NAME\n       AND a.COLUMN_NAME = c.COLUMN_NAME\n     WHERE a.OWNER = 'SEMA_ERP'\n       AND a.TABLE_NAME IN ('AM_CMMN_COSTPT_D') \/*\uc870\ud68c\ud560 Table\uba85*\/\n--       AND a.COLUMN_NAME IN ('COMPANY_CODE','BANK_NO_SEQ','BANK_NO','BANK_DIV','CUST_CODE','DEPOSIT_OWNER','USE_YN','DEPT_CODE','APL_STR_DTE'\n--                            ,'APL_END_DTE','FUND_CHK_YN','FUND_CHK_NOTE','FUND_CHK_YN_B','BANK_RES_CODE','BANK_RES_DESC','INPUT_DATE','INPUT_DUTY_ID'\n--                            ,'CHG_DATE','CHG_DUTY_ID'\n--       )\n)\nSELECT MAX(aa.ommType) AS ommType\n     , MAX(aa.colId) AS colId\n     , aa.ommName\n     , MAX(aa.ommDescription) AS ommDescription\n     , MAX(aa.ommLength) AS ommLength\n     , (MAX(aa.ommType)||' '||aa.ommName||'&lt;length='||MAX(aa.ommLength)||' description=&quot;'||MAX(aa.ommDescription)||'&quot;&gt;;') AS ommGenStr\n     , MAX(aa.dmlStr) AS dmlStr\n     , MAX(aa.dmlStr2) AS dmlStr2\n  FROM (\n        SELECT CASE WHEN a.dataType IN ('VARCHAR2') THEN 'String' \n                    WHEN a.dataType IN ('DATE') THEN 'Date'\n                    WHEN a.dataType IN ('NUMBER') AND a.dataLen &lt; 10 THEN 'Integer'\n                    WHEN a.dataType IN ('NUMBER') AND a.dataLen &gt;= 10 THEN 'BigDecimal'\n               END ommType\n             , a.camel AS ommName\n             , a.orgCmt AS ommDescription\n             , a.dataLen AS ommLength\n             , a.colId \n             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' AS '||RPAD(a.camel, b.maxLenCmt,' ')||' '||a.cmt) AS dmlStr\n             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' '||a.cmt2) AS dmlStr2\n          FROM tempTbl a\n         OUTER APPLY( SELECT MAX(LENGTHB(colName)) AS maxLenCol\n                           , MAX(LENGTHB(camel)) AS maxLenCmt \n                        FROM tempTbl\n                    ) b  \n       ) aa\n GROUP BY aa.ommName\n-- ORDER BY aa.ommName\n ORDER BY MAX(aa.colId)\n-- ORDER BY MAX(aa.ommName)<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-427","post","type-post","status-publish","format-standard","hentry","category-oracle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v18.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/dev.e-luda.com\/dev-note\/?p=427\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8\" \/>\n<meta property=\"og:url\" content=\"http:\/\/dev.e-luda.com\/dev-note\/?p=427\" \/>\n<meta property=\"og:site_name\" content=\"\ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8\" \/>\n<meta property=\"article:published_time\" content=\"2021-04-13T02:52:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-22T05:20:15+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"wizardlee\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/#website\",\"url\":\"http:\/\/dev.e-luda.com\/dev-note\/\",\"name\":\"\ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8\",\"description\":\"\ub2e4\ub978 \uc6cc\ub4dc\ud504\ub808\uc2a4 \uc0ac\uc774\ud2b8\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/dev.e-luda.com\/dev-note\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/?p=427#webpage\",\"url\":\"http:\/\/dev.e-luda.com\/dev-note\/?p=427\",\"name\":\"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8\",\"isPartOf\":{\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/#website\"},\"datePublished\":\"2021-04-13T02:52:37+00:00\",\"dateModified\":\"2022-04-22T05:20:15+00:00\",\"author\":{\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/#\/schema\/person\/7e381f3b46b7824adbe15ff43b53c42c\"},\"breadcrumb\":{\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/?p=427#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/dev.e-luda.com\/dev-note\/?p=427\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/?p=427#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/dev.e-luda.com\/dev-note\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1\"}]},{\"@type\":\"Person\",\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/#\/schema\/person\/7e381f3b46b7824adbe15ff43b53c42c\",\"name\":\"wizardlee\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"http:\/\/dev.e-luda.com\/dev-note\/#personlogo\",\"inLanguage\":\"ko-KR\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6eaa1dedfb9710f350bbf8ccb317e7cfa2a5a277b535c6558bc57169262ec430?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6eaa1dedfb9710f350bbf8ccb317e7cfa2a5a277b535c6558bc57169262ec430?s=96&d=mm&r=g\",\"caption\":\"wizardlee\"},\"sameAs\":[\"http:\/\/dev.e-luda.com\/dev-note\"],\"url\":\"http:\/\/dev.e-luda.com\/dev-note\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/dev.e-luda.com\/dev-note\/?p=427","og_locale":"ko_KR","og_type":"article","og_title":"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8","og_url":"http:\/\/dev.e-luda.com\/dev-note\/?p=427","og_site_name":"\ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8","article_published_time":"2021-04-13T02:52:37+00:00","article_modified_time":"2022-04-22T05:20:15+00:00","twitter_card":"summary_large_image","twitter_misc":{"Written by":"wizardlee","Est. reading time":"2\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"http:\/\/dev.e-luda.com\/dev-note\/#website","url":"http:\/\/dev.e-luda.com\/dev-note\/","name":"\ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8","description":"\ub2e4\ub978 \uc6cc\ub4dc\ud504\ub808\uc2a4 \uc0ac\uc774\ud2b8","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/dev.e-luda.com\/dev-note\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"ko-KR"},{"@type":"WebPage","@id":"http:\/\/dev.e-luda.com\/dev-note\/?p=427#webpage","url":"http:\/\/dev.e-luda.com\/dev-note\/?p=427","name":"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1 - \ub9c8\ubc95\uc0ac\ub9ac\uc758\uac1c\ubc1c\ub178\ud2b8","isPartOf":{"@id":"http:\/\/dev.e-luda.com\/dev-note\/#website"},"datePublished":"2021-04-13T02:52:37+00:00","dateModified":"2022-04-22T05:20:15+00:00","author":{"@id":"http:\/\/dev.e-luda.com\/dev-note\/#\/schema\/person\/7e381f3b46b7824adbe15ff43b53c42c"},"breadcrumb":{"@id":"http:\/\/dev.e-luda.com\/dev-note\/?p=427#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["http:\/\/dev.e-luda.com\/dev-note\/?p=427"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/dev.e-luda.com\/dev-note\/?p=427#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/dev.e-luda.com\/dev-note"},{"@type":"ListItem","position":2,"name":"oracle dml \ubb38\uc7a5 \uc0dd\uc131 1"}]},{"@type":"Person","@id":"http:\/\/dev.e-luda.com\/dev-note\/#\/schema\/person\/7e381f3b46b7824adbe15ff43b53c42c","name":"wizardlee","image":{"@type":"ImageObject","@id":"http:\/\/dev.e-luda.com\/dev-note\/#personlogo","inLanguage":"ko-KR","url":"https:\/\/secure.gravatar.com\/avatar\/6eaa1dedfb9710f350bbf8ccb317e7cfa2a5a277b535c6558bc57169262ec430?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6eaa1dedfb9710f350bbf8ccb317e7cfa2a5a277b535c6558bc57169262ec430?s=96&d=mm&r=g","caption":"wizardlee"},"sameAs":["http:\/\/dev.e-luda.com\/dev-note"],"url":"http:\/\/dev.e-luda.com\/dev-note\/?author=1"}]}},"_links":{"self":[{"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/posts\/427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=427"}],"version-history":[{"count":1,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/posts\/427\/revisions"}],"predecessor-version":[{"id":543,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=\/wp\/v2\/posts\/427\/revisions\/543"}],"wp:attachment":[{"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=427"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dev.e-luda.com\/dev-note\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}