Simple OracleBulkCopy example erroneously prefixes my already qualified target table name with my own schema name

Question

I am trying to do a simple Oracle Bulk Insert into the following table.

CREATE TABLE TARGETSCHEMA.JUNK1
   (COL1 VARCHAR2(255));

I am getting the following error when I execute the Bulk Insert's WriteToServer method.

ORA-39831: Direct path load failed, (MYSCHEMA.TARGETSCHEMA.JUNK1) is not a table. https://docs.oracle.com/error-help/db/ora-39831/

When I connect to the Oracle database using Oracle.ManagedDataAccess.Core and C#, the connection string is of the following format:

user id=MySchema;password=MyPassword;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=servername.sys.mycompany.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mydb_adhoc.mycompany.com)))

Here is the code, which for this example, starts with the CallingProcedure:

public static void CallingProcedure()
{
    string targetTableName = "TARGETSCHEMA.JUNK1"; 
    DataTable UploadDataTable = GetDataTableForTable(targetTableName);
    UploadDataTable.Rows.Add("1");
    BulkInsert(UploadDataTable, targetTableName);
}               
                
                
public static DataTable GetDataTableForTable(string tableName)
{
    OracleConnection sqlConnection = (OracleConnection)GetConnection(); 
    sqlConnection.Open();

    OracleCommand sqlCommand = new OracleCommand
    {
        Connection = sqlConnection,
        CommandText = $"SELECT * FROM {tableName} WHERE 1 = 0",
        CommandType = CommandType.Text
    };

    DataSet dataSet = new DataSet();

    using (var sqlDataAdapter = new OracleDataAdapter { SelectCommand = sqlCommand })
    {
        sqlDataAdapter.Fill(dataSet);
    }

    DataTable dataTable = dataSet.Tables[0];
    dataTable.TableName = tableName;
    return dataTable;
}               

public static void BulkInsert(DataTable cvrgTypData, string targetTableName)
{
    var oracleConnection = (OracleConnection)GetConnection();
    oracleConnection.Open();

    using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oracleConnection))
    {
        bulkCopy.DestinationTableName = targetTableName;
        bulkCopy.ColumnMappings.Add("COL1", "COL1");
        bulkCopy.WriteToServer(cvrgTypData);
    }

    oracleConnection.Close();
    oracleConnection.Dispose();
}

I considered changing my default schema to the target schema and not qualifying the bulkCopy.DestinationTableName property with the schema name but I feel like this is clunky and shouldn't be necessary. I also am concerned about whether or not the default setting will be cleared since I am opening a new connection every time I call GetConnection, replying on the database using connection pooling.

I expected that bulkCopy.DestinationTableName should be set to the fully qualified table name and that my default schema wouldn't be appended top the already fully qualified table name, making it doubly qualified.

We are on Oracle 19.

How can I stop this behavior from occurring?

Answer 1

The Oracle 19c documentation only shows the DestinationTableName property you are already using. That does not seem to support qualified identifiers (as SqlBulkCopy seems to.

The 21c documentation as add a DestinationSchemaName property:

Property Value

A string value that identifies the destination schema name.

Remarks

By default, this property is set to the schema or the user associated with the connection used by the OracleBulkCopy object. This default can be modified by setting this property to a different destination schema or user name.

Curiously there is a broken entry for that property in the 19.3 ODAC documentation, and it doesn't seem to be mentioned in any of the "changes in this release" notes, so it's a little unclear when it actually appeared.

But from your comments it does seem to be present in whichever version of 19c you are using, so either it was there and undocumented or has been quietly back-ported. (Either way it's still not documented for your release, so use with caution, I suppose...)

So you should be able to do:

        bulkCopy.DestinationSchemaName = "TARGETSCHEMA";
        bulkCopy.DestinationTableName = "JUNK1";

---

If everything you access is in that other schema then it might still be simpler to do alter session set current_schema=TARGETSCHEMA so you never have to worry about it or include a fixed schema in your code (though you may be getting that as a property anyway). You could have a wrapper around getConnection() that always issues that statement; or you may be able to make it part of the data source definition. I'm not familiar with .net, but when defining a JDBC connection pool in WebLogic/WildFly/Tomcat etc. you can specify SQL to run when each physical connection is initialised, so you may be able to do something similar.