{"id":19830,"date":"2024-03-15T19:30:38","date_gmt":"2024-03-15T19:30:38","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/"},"modified":"2024-03-21T16:38:15","modified_gmt":"2024-03-21T16:38:15","slug":"how-can-pl-sql-export-table-data","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/","title":{"rendered":"How can PL\/SQL export table data?"},"content":{"rendered":"

In PL\/SQL, you can export table data using the following methods:<\/p>\n

    \n
  1. Create a new table by selecting and inserting data in one statement.<\/li>\n<\/ol>\n
    DECLARE<\/span>\r\n  -- \u58f0\u660e\u53d8\u91cf<\/span>\r\n  v_data VARCHAR2(4000<\/span>);\r\n  v_file UTL_FILE.FILE_TYPE;\r\nBEGIN<\/span>\r\n  -- \u6253\u5f00\u6587\u4ef6<\/span>\r\n  v_file :=<\/span> UTL_FILE.FOPEN('DIRECTORY_PATH'<\/span>, 'filename.txt'<\/span>, 'W'<\/span>);\r\n\r\n  -- \u67e5\u8be2\u6570\u636e\u5e76\u5bfc\u51fa\u5230\u53d8\u91cf\u4e2d<\/span>\r\n  SELECT<\/span> column1 ||<\/span> ','<\/span> ||<\/span> column2 ||<\/span> ','<\/span> ||<\/span> column3 -- \u5c06\u9700\u8981\u5bfc\u51fa\u7684\u5217\u8fde\u63a5\u4e3a\u4e00\u4e2a\u5b57\u7b26\u4e32<\/span>\r\n  INTO<\/span> v_data\r\n  FROM<\/span> your_table;\r\n\r\n  -- \u5c06\u53d8\u91cf\u7684\u503c\u5199\u5165\u5230\u6587\u4ef6\u4e2d<\/span>\r\n  UTL_FILE.PUT_LINE(v_file, v_data);\r\n\r\n  -- \u5173\u95ed\u6587\u4ef6<\/span>\r\n  UTL_FILE.FCLOSE(v_file);\r\nEND<\/span>;\r\n\/<\/span>\r\n<\/code><\/pre>\n
      \n
    1. output messages from the database management system<\/li>\n<\/ol>\n
      SET<\/span> SERVEROUTPUT ON<\/span>; -- \u6253\u5f00\u8f93\u51fa<\/span>\r\n\r\nBEGIN<\/span>\r\n  -- \u67e5\u8be2\u6570\u636e\u5e76\u5bfc\u51fa\u5230\u63a7\u5236\u53f0<\/span>\r\n  FOR<\/span> rec IN<\/span> (SELECT<\/span> *<\/span> FROM<\/span> your_table) LOOP\r\n    DBMS_OUTPUT.PUT_LINE(rec.column1 ||<\/span> ','<\/span> ||<\/span> rec.column2 ||<\/span> ','<\/span> ||<\/span> rec.column3); -- \u5c06\u9700\u8981\u5bfc\u51fa\u7684\u5217\u8fde\u63a5\u4e3a\u4e00\u4e2a\u5b57\u7b26\u4e32<\/span>\r\n  END<\/span> LOOP;\r\nEND<\/span>;\r\n\/<\/span>\r\n<\/code><\/pre>\n
      Please choose the appropriate method based on actual needs and make necessary adjustments as needed. Also, please modify the table names, column names, directory paths, and file names in the sample code according to the actual situation.<\/p>\n","protected":false},"excerpt":{"rendered":"
      In PL\/SQL, you can export table data using the following methods: Create a new table by selecting and inserting data in one statement. DECLARE — \u58f0\u660e\u53d8\u91cf v_data VARCHAR2(4000); v_file UTL_FILE.FILE_TYPE; BEGIN — \u6253\u5f00\u6587\u4ef6 v_file := UTL_FILE.FOPEN(‘DIRECTORY_PATH’, ‘filename.txt’, ‘W’); — \u67e5\u8be2\u6570\u636e\u5e76\u5bfc\u51fa\u5230\u53d8\u91cf\u4e2d SELECT column1 || ‘,’ || column2 || ‘,’ || column3 — \u5c06\u9700\u8981\u5bfc\u51fa\u7684\u5217\u8fde\u63a5\u4e3a\u4e00\u4e2a\u5b57\u7b26\u4e32 INTO v_data FROM […]<\/p>\n","protected":false},"author":14,"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-19830","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow can PL\/SQL export table data? - 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-pl-sql-export-table-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can PL\/SQL export table data?\" \/>\n<meta property=\"og:description\" content=\"In PL\/SQL, you can export table data using the following methods: Create a new table by selecting and inserting data in one statement. DECLARE -- \u58f0\u660e\u53d8\u91cf v_data VARCHAR2(4000); v_file UTL_FILE.FILE_TYPE; BEGIN -- \u6253\u5f00\u6587\u4ef6 v_file := UTL_FILE.FOPEN('DIRECTORY_PATH', 'filename.txt', 'W'); -- \u67e5\u8be2\u6570\u636e\u5e76\u5bfc\u51fa\u5230\u53d8\u91cf\u4e2d SELECT column1 || ',' || column2 || ',' || column3 -- \u5c06\u9700\u8981\u5bfc\u51fa\u7684\u5217\u8fde\u63a5\u4e3a\u4e00\u4e2a\u5b57\u7b26\u4e32 INTO v_data FROM […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\" \/>\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-15T19:30:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T16:38:15+00:00\" \/>\n<meta name=\"author\" content=\"Noah Thompson\" \/>\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=\"Noah Thompson\" \/>\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-pl-sql-export-table-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\"},\"author\":{\"name\":\"Noah Thompson\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/2e83cc6ab9f60d36921c2d0f9f280f4a\"},\"headline\":\"How can PL\/SQL export table data?\",\"datePublished\":\"2024-03-15T19:30:38+00:00\",\"dateModified\":\"2024-03-21T16:38:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\"},\"wordCount\":75,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\",\"name\":\"How can PL\/SQL export table data? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T19:30:38+00:00\",\"dateModified\":\"2024-03-21T16:38:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can PL\/SQL export table data?\"}]},{\"@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\/2e83cc6ab9f60d36921c2d0f9f280f4a\",\"name\":\"Noah Thompson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/350e537e1530ede2762ee0237e877d6693f4f7163ab4f303202cc9a6b27b6cb4?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/350e537e1530ede2762ee0237e877d6693f4f7163ab4f303202cc9a6b27b6cb4?s=96&d=mm&r=g\",\"caption\":\"Noah Thompson\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/noahthompson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can PL\/SQL export table data? - 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-pl-sql-export-table-data\/","og_locale":"en_US","og_type":"article","og_title":"How can PL\/SQL export table data?","og_description":"In PL\/SQL, you can export table data using the following methods: Create a new table by selecting and inserting data in one statement. DECLARE -- \u58f0\u660e\u53d8\u91cf v_data VARCHAR2(4000); v_file UTL_FILE.FILE_TYPE; BEGIN -- \u6253\u5f00\u6587\u4ef6 v_file := UTL_FILE.FOPEN('DIRECTORY_PATH', 'filename.txt', 'W'); -- \u67e5\u8be2\u6570\u636e\u5e76\u5bfc\u51fa\u5230\u53d8\u91cf\u4e2d SELECT column1 || ',' || column2 || ',' || column3 -- \u5c06\u9700\u8981\u5bfc\u51fa\u7684\u5217\u8fde\u63a5\u4e3a\u4e00\u4e2a\u5b57\u7b26\u4e32 INTO v_data FROM […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T19:30:38+00:00","article_modified_time":"2024-03-21T16:38:15+00:00","author":"Noah Thompson","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Noah Thompson","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/"},"author":{"name":"Noah Thompson","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/2e83cc6ab9f60d36921c2d0f9f280f4a"},"headline":"How can PL\/SQL export table data?","datePublished":"2024-03-15T19:30:38+00:00","dateModified":"2024-03-21T16:38:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/"},"wordCount":75,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/","name":"How can PL\/SQL export table data? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T19:30:38+00:00","dateModified":"2024-03-21T16:38:15+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-pl-sql-export-table-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can PL\/SQL export table data?"}]},{"@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\/2e83cc6ab9f60d36921c2d0f9f280f4a","name":"Noah Thompson","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/350e537e1530ede2762ee0237e877d6693f4f7163ab4f303202cc9a6b27b6cb4?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/350e537e1530ede2762ee0237e877d6693f4f7163ab4f303202cc9a6b27b6cb4?s=96&d=mm&r=g","caption":"Noah Thompson"},"url":"https:\/\/www.silicloud.com\/blog\/author\/noahthompson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19830","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\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=19830"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19830\/revisions"}],"predecessor-version":[{"id":53593,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19830\/revisions\/53593"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=19830"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=19830"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=19830"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}