diff options
| -rw-r--r-- | oracle-conversion/export-kapv.sql | 17 | ||||
| -rw-r--r-- | oracle-conversion/export-lessons.sql | 8 | ||||
| -rwxr-xr-x | oracle-conversion/fixml.sh | 7 | ||||
| -rw-r--r-- | oracle-conversion/grab.sh | 7 | ||||
| -rw-r--r-- | oracle-conversion/rowset-to-sql.xsl | 33 |
5 files changed, 72 insertions, 0 deletions
diff --git a/oracle-conversion/export-kapv.sql b/oracle-conversion/export-kapv.sql new file mode 100644 index 0000000..243936a --- /dev/null +++ b/oracle-conversion/export-kapv.sql @@ -0,0 +1,17 @@ +set pages 0 +set long 9999999 +set head off + +spool /tmp/export-kapv.xml +select dbms_xmlgen.getxml('select * from Adressen') from dual; +select dbms_xmlgen.getxml('select * from Kunden') from dual; +select dbms_xmlgen.getxml('select * from Produktlager') from dual; +select dbms_xmlgen.getxml('select * from Kunden_Profil') from dual; +select dbms_xmlgen.getxml('select * from Auftrag') from dual; +select dbms_xmlgen.getxml('select * from Rechnungsdaten') from dual; +select dbms_xmlgen.getxml('select * from Auftrag_hat_Positionen') from dual; +select dbms_xmlgen.getxml('select * from Kunde_hat_Adresse') from dual; +select dbms_xmlgen.getxml('select * from Vorlieben') from dual; +select dbms_xmlgen.getxml('select * from Kunde_hat_Vorlieben') from dual; +select dbms_xmlgen.getxml('select * from Zu_Vorlieben_gehoeren_Produkte') from dual; +spool off diff --git a/oracle-conversion/export-lessons.sql b/oracle-conversion/export-lessons.sql new file mode 100644 index 0000000..934f08a --- /dev/null +++ b/oracle-conversion/export-lessons.sql @@ -0,0 +1,8 @@ +set pages 0 +set long 9999999 +set head off + +spool /tmp/export-lesson.xml +select dbms_xmlgen.getxml('select * from lessons') from dual; +select dbms_xmlgen.getxml('select * from lesson_order') from dual; +spool off diff --git a/oracle-conversion/fixml.sh b/oracle-conversion/fixml.sh new file mode 100755 index 0000000..75bbcf8 --- /dev/null +++ b/oracle-conversion/fixml.sh @@ -0,0 +1,7 @@ +sed -e "s/ *$//" -e "s/^ *//" -e "/^$/d" $1 | \ + sed 's/^SQL.*from \([^ ]*\)'"'"'.*/<ROWSET NAME="\1">/' | \ + sed -e "/<ROWSET>/d" -e "/^SQL/d" -e '/^<?xml/d' | \ + sed -e '1i <DATABASE>' -e '$a </DATABASE>' | \ + sed -e :a -e 'N;s/\n//g' -e ba | \ + sed 's: *</:</:g' | \ + xmllint --format - diff --git a/oracle-conversion/grab.sh b/oracle-conversion/grab.sh new file mode 100644 index 0000000..e54ba24 --- /dev/null +++ b/oracle-conversion/grab.sh @@ -0,0 +1,7 @@ +#!/bin/sh + +sqlplus nils/secret < export-lessons.sql +sqlplus kapv/secret < export-kapv.sql + +./fixml.sh /tmp/export-lesson.xml > lesson.xml +./fixml.sh /tmp/export-kapv.xml | xsltproc rowset-to-sql.xsl - > kapv.sql diff --git a/oracle-conversion/rowset-to-sql.xsl b/oracle-conversion/rowset-to-sql.xsl new file mode 100644 index 0000000..7b2a90a --- /dev/null +++ b/oracle-conversion/rowset-to-sql.xsl @@ -0,0 +1,33 @@ +<?xml version="1.0" encoding="UTF-8"?> +<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> + +<xsl:output method="text"/> + + +<xsl:template match="ROWSET"> + <xsl:text>insert into </xsl:text> + <xsl:value-of select="@NAME"/> + <xsl:text> values</xsl:text> + <xsl:for-each select="ROW"> + <xsl:text> 	(</xsl:text> + <xsl:for-each select="*"> + <xsl:text>'</xsl:text> + <xsl:value-of select="."/> + <xsl:text>'</xsl:text> + <xsl:if test="position() != last()"> + <xsl:text>,</xsl:text> + </xsl:if> + </xsl:for-each> + <xsl:text>)</xsl:text> + <xsl:if test="position() != last()"> + <xsl:text>,</xsl:text> + </xsl:if> + </xsl:for-each> + <xsl:text>; </xsl:text> +</xsl:template> + +<xsl:template match="/"> + <xsl:apply-templates select="DATABASE/ROWSET" /> +</xsl:template> +</xsl:stylesheet> + |