{"id":20562,"date":"2024-03-15T20:39:01","date_gmt":"2024-03-15T20:39:01","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/"},"modified":"2024-03-21T18:23:15","modified_gmt":"2024-03-21T18:23:15","slug":"how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/","title":{"rendered":"How can large amounts of data be exported to a CSV file in PL\/SQL?"},"content":{"rendered":"<p>In PL\/SQL, you can use the UTL_FILE package to export large amounts of data to a CSV file. Here is an example code that queries data from a table and exports the results to a CSV file.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">DECLARE<\/span>\r\n  <span class=\"hljs-comment\">-- \u6587\u4ef6\u53e5\u67c4<\/span>\r\n  file_handle UTL_FILE.FILE_TYPE;\r\n  <span class=\"hljs-comment\">-- \u67e5\u8be2\u7ed3\u679c<\/span>\r\n  cursor_data SYS_REFCURSOR;\r\n  <span class=\"hljs-comment\">-- \u67e5\u8be2\u8bed\u53e5<\/span>\r\n  query_string VARCHAR2(<span class=\"hljs-number\">4000<\/span>) :<span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'SELECT * FROM your_table'<\/span>;\r\n  <span class=\"hljs-comment\">-- \u8f93\u51fa\u6587\u4ef6\u8def\u5f84<\/span>\r\n  file_path VARCHAR2(<span class=\"hljs-number\">100<\/span>) :<span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'your_file_path.csv'<\/span>;\r\n  <span class=\"hljs-comment\">-- \u6bcf\u6b21\u8bfb\u53d6\u7684\u884c\u6570<\/span>\r\n  chunk_size NUMBER :<span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">1000<\/span>;\r\n  <span class=\"hljs-comment\">-- \u7f13\u51b2\u533a<\/span>\r\n  buffer VARCHAR2(<span class=\"hljs-number\">32767<\/span>);\r\n<span class=\"hljs-keyword\">BEGIN<\/span>\r\n  <span class=\"hljs-comment\">-- \u6253\u5f00\u6587\u4ef6<\/span>\r\n  file_handle :<span class=\"hljs-operator\">=<\/span> UTL_FILE.FOPEN(<span class=\"hljs-string\">'YOUR_DIRECTORY'<\/span>, file_path, <span class=\"hljs-string\">'w'<\/span>, <span class=\"hljs-number\">32767<\/span>);\r\n  \r\n  <span class=\"hljs-comment\">-- \u6267\u884c\u67e5\u8be2<\/span>\r\n  <span class=\"hljs-keyword\">OPEN<\/span> cursor_data <span class=\"hljs-keyword\">FOR<\/span> query_string;\r\n  \r\n  <span class=\"hljs-comment\">-- \u9010\u884c\u8bfb\u53d6\u6570\u636e\u5e76\u5199\u5165\u6587\u4ef6<\/span>\r\n  LOOP\r\n    <span class=\"hljs-keyword\">FETCH<\/span> cursor_data BULK <span class=\"hljs-keyword\">COLLECT<\/span> <span class=\"hljs-keyword\">INTO<\/span> buffer LIMIT chunk_size;\r\n    \r\n    <span class=\"hljs-keyword\">FOR<\/span> i <span class=\"hljs-keyword\">IN<\/span> <span class=\"hljs-number\">1.<\/span>.buffer.COUNT LOOP\r\n      UTL_FILE.PUT_LINE(file_handle, buffer(i));\r\n    <span class=\"hljs-keyword\">END<\/span> LOOP;\r\n    \r\n    <span class=\"hljs-comment\">-- \u9000\u51fa\u5faa\u73af\u6761\u4ef6<\/span>\r\n    IF buffer.COUNT <span class=\"hljs-operator\">&lt;<\/span> chunk_size <span class=\"hljs-keyword\">THEN<\/span>\r\n      EXIT;\r\n    <span class=\"hljs-keyword\">END<\/span> IF;\r\n  <span class=\"hljs-keyword\">END<\/span> LOOP;\r\n  \r\n  <span class=\"hljs-comment\">-- \u5173\u95ed\u6587\u4ef6\u548c\u6e38\u6807<\/span>\r\n  UTL_FILE.FCLOSE(file_handle);\r\n  <span class=\"hljs-keyword\">CLOSE<\/span> cursor_data;\r\n<span class=\"hljs-keyword\">END<\/span>;\r\n<\/code><\/pre>\n<p>In the code above, you need to replace the following part with actual values:<\/p>\n<ol>\n<li>Table_name: Name of the table from which the data needs to be exported.<\/li>\n<li>The file path where the data will be exported: your_file_path.csv.<\/li>\n<li>The name of the directory object that contains the exported files: YOUR_DIRECTORY.<\/li>\n<\/ol>\n<p>After running the above code, a CSV file containing the query results will be generated in the specified file path. Make sure you have write permissions to the specified directory object and that the file path is valid.<\/p>\n<p>Please note that this example reads a certain number of rows from the cursor each time and writes them to a file. This helps prevent performance issues caused by reading and writing a large amount of data all at once. You can adjust the value of chunk_size as needed to optimize performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In PL\/SQL, you can use the UTL_FILE package to export large amounts of data to a CSV file. Here is an example code that queries data from a table and exports the results to a CSV file. DECLARE &#8212; \u6587\u4ef6\u53e5\u67c4 file_handle UTL_FILE.FILE_TYPE; &#8212; \u67e5\u8be2\u7ed3\u679c cursor_data SYS_REFCURSOR; &#8212; \u67e5\u8be2\u8bed\u53e5 query_string VARCHAR2(4000) := &#8216;SELECT * FROM your_table&#8217;; [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-20562","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How can large amounts of data be exported to a CSV file in PL\/SQL? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can large amounts of data be exported to a CSV file in PL\/SQL?\" \/>\n<meta property=\"og:description\" content=\"In PL\/SQL, you can use the UTL_FILE package to export large amounts of data to a CSV file. Here is an example code that queries data from a table and exports the results to a CSV file. DECLARE -- \u6587\u4ef6\u53e5\u67c4 file_handle UTL_FILE.FILE_TYPE; -- \u67e5\u8be2\u7ed3\u679c cursor_data SYS_REFCURSOR; -- \u67e5\u8be2\u8bed\u53e5 query_string VARCHAR2(4000) := &#039;SELECT * FROM your_table&#039;; [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T20:39:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T18:23:15+00:00\" \/>\n<meta name=\"author\" content=\"William Carter\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"William Carter\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\"},\"author\":{\"name\":\"William Carter\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\"},\"headline\":\"How can large amounts of data be exported to a CSV file in PL\/SQL?\",\"datePublished\":\"2024-03-15T20:39:01+00:00\",\"dateModified\":\"2024-03-21T18:23:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\"},\"wordCount\":199,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\",\"name\":\"How can large amounts of data be exported to a CSV file in PL\/SQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T20:39:01+00:00\",\"dateModified\":\"2024-03-21T18:23:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can large amounts of data be exported to a CSV file in PL\/SQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\",\"name\":\"William Carter\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"caption\":\"William Carter\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can large amounts of data be exported to a CSV file in PL\/SQL? - Blog - Silicon Cloud","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":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/","og_locale":"en_US","og_type":"article","og_title":"How can large amounts of data be exported to a CSV file in PL\/SQL?","og_description":"In PL\/SQL, you can use the UTL_FILE package to export large amounts of data to a CSV file. Here is an example code that queries data from a table and exports the results to a CSV file. DECLARE -- \u6587\u4ef6\u53e5\u67c4 file_handle UTL_FILE.FILE_TYPE; -- \u67e5\u8be2\u7ed3\u679c cursor_data SYS_REFCURSOR; -- \u67e5\u8be2\u8bed\u53e5 query_string VARCHAR2(4000) := 'SELECT * FROM your_table'; [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T20:39:01+00:00","article_modified_time":"2024-03-21T18:23:15+00:00","author":"William Carter","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"William Carter","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/"},"author":{"name":"William Carter","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0"},"headline":"How can large amounts of data be exported to a CSV file in PL\/SQL?","datePublished":"2024-03-15T20:39:01+00:00","dateModified":"2024-03-21T18:23:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/"},"wordCount":199,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/","name":"How can large amounts of data be exported to a CSV file in PL\/SQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T20:39:01+00:00","dateModified":"2024-03-21T18:23:15+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-large-amounts-of-data-be-exported-to-a-csv-file-in-pl-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can large amounts of data be exported to a CSV file in PL\/SQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0","name":"William Carter","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","caption":"William Carter"},"url":"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/20562","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=20562"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/20562\/revisions"}],"predecessor-version":[{"id":54368,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/20562\/revisions\/54368"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=20562"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=20562"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=20562"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}