OracleLoader and global temporary table

[syl74]

Is it possible to load a global temporary table with OracleLoader ?
I tried but it doesn't load.... no error.

[Shalex]

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();
    }