OracleLoader and global temporary table

OracleLoader and global temporary table

Postby syl74 » Mon 19 Sep 2011 17:29

Is it possible to load a global temporary table with OracleLoader ?
I tried but it doesn't load.... no error.
syl74
 
Posts: 6
Joined: Tue 21 Oct 2008 13:59

Postby Shalex » Wed 21 Sep 2011 15:27

Yes, it is possible. Note that the data in a temporary table is private for the session that created it and can be session-specific or transaction-specific.
Please try the following code:
Code: Select all
    using (OracleConnection conn = new OracleConnection()) {
        conn.ConnectionString = "server=orcl1120;uid=***;pwd=***;Pooling=false;";
        conn.Open();

        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandText = "CREATE GLOBAL TEMPORARY TABLE my_temp_table (" +
                            "  column1  NUMBER" +
                            ") ON COMMIT PRESERVE ROWS";
        cmd.ExecuteNonQuery();

        OracleLoader loader = new OracleLoader();
        // Specify connection that OracleLoader will use for loading
        loader.Connection = conn;
        // Set table name that will be loaded into
        loader.TableName = "my_temp_table";
        // Populate Columns collection from table description
        loader.CreateColumns();
        // Prepare OracleLoader for loading
        loader.Open();
        for (int i = 0; i < 10; i++) {
            loader.SetValue("column1", i);
            loader.NextRow();
        }
        // Flush buffer and dispose internal OracleLoader structures
        loader.Close();

        cmd.CommandText = "select * from my_temp_table";
        OracleDataReader reader = cmd.ExecuteReader();
        while (reader.Read()) {
            Console.WriteLine(reader[0]);
        }
        conn.Close();
        conn.Open();
        cmd.CommandText = "drop table my_temp_table";
        cmd.ExecuteNonQuery();
        Console.ReadKey();
    }
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle