{"id":17113,"date":"2023-03-29T02:30:10","date_gmt":"2023-03-29T02:30:10","guid":{"rendered":"https:\/\/kiusi.com\/?p=17113"},"modified":"2023-03-30T19:54:02","modified_gmt":"2023-03-30T19:54:02","slug":"modificar-una-columna-de-varchar2-a-clob-en-una-tabla-en-oracle","status":"publish","type":"post","link":"https:\/\/kiusi.com\/en\/modificar-una-columna-de-varchar2-a-clob-en-una-tabla-en-oracle\/","title":{"rendered":"Modifying a column from VARCHAR2 to CLOB in a table in Oracle"},"content":{"rendered":"<p class=\"wp-block-paragraph\">Today I had to modify columns in a table of which I have them in VARCHAR2 and I want to move them to CLOB, for that reason I make this post because it may be that someone also needs it.  If someone knows a better method please share it!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First of all let's look at these two data types. In Oracle, VARCHAR2 and CLOB data types are different in terms of their storage capacity and their way of manipulation.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>VARCHAR2<\/strong> is a data type used to store variable length character strings in the Oracle database. The maximum length of a VARCHAR2 value in Oracle is 4000 bytes. If we need to store a longer string, we can use the CLOB data type.<\/li>\n\n\n\n<li><strong>CLOB<\/strong> is a data type used to store large amounts of character data in the Oracle database. CLOB values can store up to 128 terabytes of data. CLOBs can also store Unicode characters and allow the use of multi-byte characters. CLOB manipulation is performed by special functions such as DBMS_LOB that provide an API for accessing CLOB data.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Let's see what are some of the additional differences between VARCHAR2 and CLOB:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Storage size<\/strong>: VARCHAR2 is limited to 4000 bytes of storage, while CLOB can store large amounts of data (up to 128 terabytes).<\/li>\n\n\n\n<li><strong>Data manipulation<\/strong>: VARCHAR2 values can be manipulated by standard string operations such as SUBSTR, INSTR and LENGTH, while CLOBs require special data manipulation functions such as DBMS_LOB.<\/li>\n\n\n\n<li><strong>Indexes<\/strong>: VARCHAR2 can be indexed with standard indexes, while CLOB indexes are handled differently and special full-text indexes are used.<\/li>\n\n\n\n<li><strong>Performance<\/strong>: VARCHAR2 is generally faster than CLOB, especially in operations involving string searches and comparisons. CLOBs require more storage and processing resources.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The CLOB data type is not compatible with the VARCHAR2, that is why we cannot directly change the data type, but also we cannot change the type of a table column if that column has data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To do this, first of all we make a backup copy of the table we are going to work with :).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">These are the steps I have taken to achieve this:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>I have created a new column of type CLOB in the table<\/li>\n\n\n\n<li>Then I copied the values from the VARCHAR2 column to the new CLOB column and verified that the data is correct.<\/li>\n\n\n\n<li>Next, delete the original VARCHAR2 column.<\/li>\n\n\n\n<li>And finally I have renamed the new column CLOB with the name of the original column VARCHAR2<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Let's see an example of this operation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Add a new column of type CLOB \nALTER TABLE my_table ADD my_clob CLOB; \n\n-- Copy the values of the VARCHAR2 column to the new column \nCLOB UPDATE my_table SET my_clob = my_varchar2; \n\n-- Delete the original VARCHAR2 column \nALTER TABLE my_table DROP COLUMN my_varchar2; \n\n-- Rename the new column CLOB with the name of the original VARCHAR2 column \nALTER TABLE my_table RENAME COLUMN my_clob TO my_varchar2;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">It is important to note that this process can be slow and resource-intensive if the table is large. In addition, the table may need to be idle during the conversion process, which may affect system availability. Therefore, I recommend you to perform this process carefully and in a test environment before applying it in a production environment \ud83d\ude42<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I hope it will be useful!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Update<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">It turns out and happens that my desire to learn more and more every day has been heard! By publishing this post on Twitter my friend <a href=\"https:\/\/www.linkedin.com\/in\/techlevel\/\" data-type=\"URL\" data-id=\"https:\/\/www.linkedin.com\/in\/techlevel\/\">Javier Morales<\/a> he told me that there is a much better way to perform this conversion and even hot without leaving latencies because I am in a moment removing the column and renaming, so I want you to see this video where he tells me a spectacular way to perform this conversion from varchar2 to clob that will leave you with your eyes open!<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"750\" height=\"422\" src=\"https:\/\/www.youtube.com\/embed\/NAZOkihcAqU?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>","protected":false},"excerpt":{"rendered":"<p>Hoy me ha tocado modificar columnas en una tabla de las cuales las tengo en VARCHAR2 y las quiero pasar a CLOB, por ese motivo hago este post porque puede ser que a alguien tambi\u00e9n lo necesite.&nbsp; Si alguien sabe un m\u00e9todo mejor por favor que lo comparta! Ante todo veamos estos dos tipos de<a class=\"more-link\" href=\"https:\/\/kiusi.com\/en\/modificar-una-columna-de-varchar2-a-clob-en-una-tabla-en-oracle\/\">Continue reading <span class=\"screen-reader-text\">&#8220;Modificar una columna de VARCHAR2 a CLOB en una tabla en Oracle&#8221;<\/span><\/a><\/p>","protected":false},"author":132749711,"featured_media":17116,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","_crdt_document":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false},"categories":[121913,121890],"tags":[121903,122128,122129,121886,121968,122130],"class_list":["post-17113","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-lenguaje-sql","tag-apex","tag-base-de-datos-oracle","tag-columnas-de-tablas","tag-oracle","tag-sql","tag-tabla","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/kiusi.com\/wp-content\/uploads\/2023\/03\/De-Varchar2-a-CLOB.jpg?fit=2949%2C1735&ssl=1","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paMvMS-4s1","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/users\/132749711"}],"replies":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/comments?post=17113"}],"version-history":[{"count":4,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17113\/revisions"}],"predecessor-version":[{"id":17124,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17113\/revisions\/17124"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media\/17116"}],"wp:attachment":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media?parent=17113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/categories?post=17113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/tags?post=17113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}