<< Hotmail via Outlook? Ora si paga | Home | Oracle Schema To XML >>

Example C#: Export Oracle Schema To XML

App.Config
 

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

      <appSettings>

            <add key="ConnectionString" value="User ID=;Password=;Data Source=" />

            <add key="OracleSchema" value="" />

      </appSettings>

</configuration>

 

Main.cs

 

using System;

using System.Data;

using System.Data.OracleClient;

using System.Configuration;

 

namespace ExportOracleSchema

{

      class ExportOracleSchemaConsole

      {

            /// <summary>

            /// The main entry point for the application.

            /// </summary>

            [STAThread]

            static void Main(string[] args)

            {

                  //

                  OracleConnection cnn = new OracleConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"));

                  OracleCommand cmd = new OracleCommand();

                  OracleDataReader readerTables;

                  OracleDataReader readerColumns;

                  OracleDataReader readerComments;

                  OracleString comment;

                  OracleNumber precision;

                  OracleNumber length;

                  //

                  cnn.Open();

                  cmd.Connection = cnn;

                  //

                  cmd.CommandText = "select table_name  from all_all_tables where owner = '" + ConfigurationSettings.AppSettings.Get("OracleSchema") + "' order by table_name";

                  cmd.CommandType = CommandType.Text;

                  readerTables = cmd.ExecuteReader();

                  //

                  Console.WriteLine("<?xml version='1.0'?>");

                  Console.WriteLine("<?xml-stylesheet type=\"text/xsl\" href=\"oraschema.xslt\"?>");

                  Console.WriteLine("<tables>");

                  //

                  while (readerTables.Read())

                  {

                        //

                        string tablename = readerTables.GetString(0);

                        //

                        Console.WriteLine("<table id=\"" + tablename + "\" >");

                        Console.WriteLine("<columns>");

                        //

                        //

                        cmd.CommandText = " select ALL_TAB_COLUMNS.COLUMN_NAME,  COLUMN_ID,  " +

                             "DATA_DEFAULT, NULLABLE,  " +

                             " DATA_TYPE,  DATA_PRECISION, " +

                             " COMMENTS, DATA_LENGTH " +

                             " from ALL_TAB_COLUMNS, All_Col_Comments  " +

                             " where  ALL_TAB_COLUMNS.OWNER = ALL_COL_COMMENTS.Owner AND " +

                             " ALL_TAB_COLUMNS.TABLE_NAME = ALL_COL_COMMENTS.TABLE_NAME AND " +

                             "  ALL_TAB_COLUMNS.COLUMN_NAME = all_col_comments.column_name and " +

                             " ALL_TAB_COLUMNS.OWNER  = '" + ConfigurationSettings.AppSettings.Get("OracleSchema") + "'  and ALL_TAB_COLUMNS.TABLE_NAME = '" + tablename + "' ";

                        readerColumns = cmd.ExecuteReader();

                        //

                        while( readerColumns.Read() )

                        {

                             //

                             comment = readerColumns.GetOracleString(6);

                             precision = readerColumns.GetOracleNumber(5);

                             length = readerColumns.GetOracleNumber(7);

                             Console.WriteLine("<column>");

                             Console.WriteLine("<name>" + readerColumns.GetString(0) + "</name>");

                             Console.WriteLine("<type>" + readerColumns.GetString(4) + "</type>");

                             Console.WriteLine("<length>" + (precision.IsNull?length.ToString():precision.ToString()) + "</length>");

                             Console.WriteLine("<nullable>" + readerColumns.GetString(3) + "</nullable>");

                             Console.WriteLine("<comment>" + (comment.IsNull?"":comment.ToString()) + "</comment>");

                             Console.WriteLine("</column>");

                             //

                        }

                        //

                        Console.WriteLine("</columns>");

                        //

                        readerColumns.Close();

                        //

                        cmd.CommandText = "select COMMENTS from all_tab_comments where owner = '" + ConfigurationSettings.AppSettings.Get("OracleSchema") + "' and all_tab_comments.table_name='" +  tablename + "'";

                        readerComments = cmd.ExecuteReader();

                        while( readerComments.Read() )

                        {

                             //

                             comment = readerComments.GetOracleString(0);

                             Console.WriteLine("<comments>");

                             Console.WriteLine(comment.IsNull?"":comment.ToString());

                             Console.WriteLine("</comments>");

                             //

                        }

                        //

                        readerComments.Close();

                        //

                        Console.WriteLine("</table>");

                        //

                  }

                  //

                  Console.WriteLine("</tables>");

                  //

                  readerTables.Close();

                  cnn.Close();

                  //

            }

      }

}

 

oraschema.xslt

 

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:template match="/">

            <html>

                  <body>

                          <h1>Oracle Schema</h1>

                          <xsl:apply-templates />

                  </body> 

            </html>

    </xsl:template>

    <xsl:template match="table">

            <p>

                  <h2>Tabella: <xsl:value-of select="@id" /></h2>

                  <table>

                        <tr>

                             <td>Name</td>

                             <td>Type</td>

                             <td>Length</td>

                             <td>Nullable</td>

                             <td>Comment</td>

                        </tr>

                        <xsl:apply-templates />

              </table>

            </p>

            <p>

                  <xsl:value-of select="comments" />

            </p>

    </xsl:template>

    <xsl:template match="column">

         <tr>

                  <td>

                        <xsl:value-of select="name" />

                  </td>

                  <td>

                        <xsl:value-of select="type" />

                  </td>

                  <td>

                        <xsl:value-of select="length" />

                  </td>

                  <td>

                        <xsl:value-of select="nullable" />

                  </td>

                  <td>

                        <xsl:value-of select="comment" />

                  </td>

         </tr>

    </xsl:template>

    <xsl:template match="comments">

    </xsl:template>

</xsl:stylesheet>

Print | posted on venerdì 1 ottobre 2004 23:58

Comments on this post

# Oracle Schema To XML

Requesting Gravatar...
Left by Beyond the Good and Evil on ott 01, 2004 10:12

# re: Example C#: Export Oracle Schema To XML

Requesting Gravatar...
Hi,

I am a developer trying to trying export oracle schema to xml. I see comments above are divided into three sections: App.Config, Main.cs, and oraschema.xslt. I understand in App.Config section I must key="ConnectionString" value="User ID=;Password=;Data Source=". Do you have instruction on how to execute this script.
Left by Curtis Rich on mag 13, 2009 8:09
Comments have been closed on this topic.