{"id":246,"date":"2017-09-13T14:02:55","date_gmt":"2017-09-13T12:02:55","guid":{"rendered":"http:\/\/giveback.danielmenzel.de\/?p=246"},"modified":"2017-09-13T14:12:28","modified_gmt":"2017-09-13T12:12:28","slug":"tabelle-einer-oracle-datenbank-in-csv-format-exportieren","status":"publish","type":"post","link":"http:\/\/giveback.danielmenzel.de\/?p=246","title":{"rendered":"Tabelle einer Oracle-Datenbank in CSV-Format exportieren"},"content":{"rendered":"<pre>set pagesize 10000 linesize 32000 trimspool on feedback off echo off longchunksize 8000 heading off long 100000 termout off\r\n\r\nspool Exportdatei.csv\r\n\r\nselect '\"' || FELD1 ||'\";\"' || FELD2 ||'\";\"' || FELD3 ||'\";\"'||FELD4||'\"' from Tabellenname;\r\n\r\n<\/pre>\n<p><!--more--><\/p>\n<p>Ich habe ohne Erfolg auch andere Methoden probiert ohne dass man jedes einzelne Feld der Tabelle angeben muss. Das hat nie 100% funktioniert (Man kann einen Separator angeben und nachher Leerzeichen entfernen. Wie das gehen soll findet man ziemlich oft im Internet).<\/p>\n<p>Lange hat mich auch das Abschneiden der Ausgabe nach 80 Zeichen besch\u00e4ftigt.<\/p>\n<p>Abhilfe fand ich schlie\u00dflich mit &#8222;set long 10000&#8220; und &#8222;set longchunksize 8000&#8220;<\/p>\n<p>Die Werte sind nat\u00fcrlich variabel. Man kann div. Werte mit &#8222;show all&#8220; in sqlplus abfragen.<\/p>\n<p>Mit den in der SQL-Abfrage angegebenen Separatoren geht die CSV-Datei direkt ohne Probleme in einem deutschen Excel-Programm auf.<\/p>\n<p><strong>Aufruf einer SQL-Querydatei:<\/strong><\/p>\n<p>$BINORACLEDIR\/sqlplus $ORACLE_USERNAME\/$ORACLE_PASSWORD@$ORACLE_SID @SQL-Skriptname<\/p>\n<p><strong>Ausgaben aller Tabellen:<\/strong><\/p>\n<p>SELECT table_name<br \/>\nFROM all_tables 2 ;<\/p>\n<p><strong>Tabellenstruktur ausgeben:<\/strong><\/p>\n<p>DESCRIBE Tabellenname<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>truncate 80 Chars terminal csv oracle export table database<\/p>\n","protected":false},"excerpt":{"rendered":"<p>set pagesize 10000 linesize 32000 trimspool on feedback off echo off longchunksize 8000 heading off long 100000 termout off spool Exportdatei.csv select &#8218;&#8220;&#8218; || FELD1 ||'&#8220;;&#8220;&#8218; || FELD2 ||'&#8220;;&#8220;&#8218; || FELD3 ||'&#8220;;&#8220;&#8218;||FELD4||'&#8220;&#8218; from Tabellenname;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,13,18],"tags":[],"class_list":["post-246","post","type-post","status-publish","format-standard","hentry","category-datenbank","category-excel","category-oracle"],"_links":{"self":[{"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/posts\/246","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=246"}],"version-history":[{"count":8,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/posts\/246\/revisions"}],"predecessor-version":[{"id":254,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=\/wp\/v2\/posts\/246\/revisions\/254"}],"wp:attachment":[{"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=246"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/giveback.danielmenzel.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}