Tagged “oracle”

1. Fast Inserts to Oracle with C#

2022 Jul 01 c# oracle

Recently I found myself having to do bulk record inserts to an Oracle table on an API I created with .Net 6. I was using EntityFrameworkCore for this and read that even though I was using AddRange, the inserts actually happen one record at a time and would end up taking a very long time to complete.

After some research and experimentation, I found that OracleBulkCopy was able to do the job for me. Actually, there was a the Z.EntityFramework.Extensions.EFCore NuGet package that would have done the work for me very, very easily, but the license price was a bit too steep for me at the moment. For a large company with the budget, though, I would recommend it especially to keep your code simpler.

For my solution, you need to first make sure you have Oracle.ManagedDataAccess installed from NuGet. You can use the Core version instead, if you are using a .Net core project.

Make sure to include the using statement:

using Oracle.ManagedDataAccess.Client;

This method does require your records to be in a DataTable, though, but otherwise here's some example code on how to use OracleBulkCopy:

// Your records need to be in a DataTable.
var records = <your_records>;

using OracleConnection connection = new (<your_connection_string>);
connection.Open();

using OracleBulkCopy bulk = new(connection);
bulk.DestinationTableName = "<destination_table>";
bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping { DestinationColumn = "<destination_column>", SourceColumn = "<source_column>" }; // You can add more columns as needed
bulk.WriteToServer(records);

You'll probably also want to wrap the code from the connection to the WriteToServer in a try/catch block.

---

2. Identity Column Gotcha in Oracle 19c

2021 Feb 24 oracle pl/sql

I recently created a new table in a database that I maintain and decided to use the identity column feature to have unique IDs in my table. In the past when I was using an older version of Oracle, this feature was not available, so I had to create a sequence and a trigger each time I wanted to have a table with an auto-generated ID. So I created that table with the identity column to always generate an ID. Then to fill the table with data, I used an insert all statement. To my surprise, all the IDs for all the rows were the same. Turns out you need to insert one row at a time for the Identity column to work correctly.

An example to replicate:

-- Create the table
create table userlist 
(id int generated always as identity,
username varchar2(10) not null);
 
-- Insert the values
insert all
into userlist (username) values ('Phil')
into userlist (username) values ('Bob')
into userlist (username) values ('Charlie')
select * from dual;
commit;
 
-- Query the table
-- You will see here that all the users have the same ID.
select * from userlist;

The resulting table will look something like this:

ID	USERNAME
1	Phil
1	Bob
1	Charlie

To prevent this from happening, you just have to add PRIMARY KEY to the definition of the ID column. That way when you come across this issue you will get an error that won’t let you add those rows instead. In older versions, primary keys were defined by adding a constraint, but this way is easier.

create table userlist 
(id int generated always as identity primary key,
username varchar2(10) not null);

---

3. Get Bad Login Attempts in Oracle

2020 Jun 19 oracle pl/sql

If you want to audit your database to see when and where bad login attempts are happening, you can run the query below:

select *
from dba_audit_trail
where returncode = 1017;

Here are a few other return codes you can check for:

Return Codes #

Return Code	Description
0	Success
1017	Bad login attempt
28000	Account is locked

---

4. Get the Session and Database Timezone Offset in Oracle

2020 Jun 15 oracle pl/sql

If you are scheduling a procedure to query a TIMESTAMP WITH LOCAL TIME ZONE column, you may have noticed that when the database runs the query it returns a different time than when you run that same query yourself. That’s because the database is set to a different timezone than your session.

Returning the Timezone #

To see the database’s timezone:

select dbtimezone from dual;

To see your session’s timezone:

select sessiontimezone from dual;

Altering the Timezone #

You can alter your session’s timezone offset by the name of the timezone or by adding or subtracting the hours yourself.

alter session set time_zone = '-5:0';

alter session set time_zone = 'America/New_York';

If you want to temporarily set the timezone within a procedure, you will need to wrap the alter session statement in an execute immediate statement:

execute immediate 'alter session set time_zone = ''America/New_York''';

View All Available Timezones Names #

Oracle has a list of all the timezone names here:

https://docs.oracle.com/cd/B19306_01/server.102/b14225/applocaledata.htm#i637736

You can also view the list of available timezones by querying your database:

select *
from v$timezone_names;

---

5. ORA-01722 invalid number in Parameterized Query

2020 May 15 c# oracle

If you are receiving the ORA-01722 (invalid number) error in an Oracle query even though you are using named parameters, you may want to check to make sure that BindByName has been set to true.

For example, the code below will fail because the parameters aren’t truly bound by name so the parameters should be added in the same order that they appear in the query unless you use the BindByName option.

string oradb = "your connection string";
 
var connection = new OracleConnection(oradb);
 
connection.Open();
 
string query = @"select *
                  from table
                  where numeric_field = :numeric_field
                  and character_field :character_field";
 
var command = new OracleCommand(query, connection);
command.CommandType = CommandType.Text;
 
command.Parameters.Add(new OracleParameter("character_field", "some value"));
command.Parameters.Add(new OracleParameter("numeric_field", 123));
 
/* If you uncomment the line below, the code will work even though the parameters aren't added in the same order
  as they appear in the query because they will be truly bound by name. */
// command.BindByName = true;
 
var dataAdapter = new OracleDataAdapter(command);
 
var dataTable = new DataTable();
 
try
{
  dataAdapter.Fill(dataTable);
}
catch (OracleException ex)
{
  Console.WriteLine($"Exception: {ex.Message}");
}
 
connection.Close();
connection.Dispose();

---

6. Inserting Multiple Records into Oracle with OleDB and Binding in C#

2019 Dec 13 c# oracle

While there is a way to do this with an Oracle driver, but in case you have highly restricted access to your PC and can’t look at or configure the LDAP settings, you can get by using OleDB.

First, be sure to include this statement up top:

using System.Data.OleDb;

In the example below, we are adding 10 new usernames to a table that are named BOT01 – BOT10. It’s a pretty basic example that you can edit to suit your needs.

string connectionString = "Provider=OraOLEDB.Oracle;Data Source=<your_data_source>;User ID=<user_id>;Password=<password>;";
string insertStatement = $@"INSERT INTO <your_table>
                            (username, effective_date)
                            VALUES
                            (:username, to_date('1-JAN-2019', 'DD-MON-YYYY')";
 
try
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        using (OleDbCommand command = new OleDbCommand(insertStatement, connection))
        {
            // For single inserts, you can just do this:
            // command.Parameters.Add(new OleDbParameters("username", "<your_username>"));
            // command.ExecuteNonQuery();
 
            // Otherwise for multiple inserts
            command.Parameters.Add(new OleDbParameters("username", OleDbType.VarChar));
            for (int i = 1; i <= 10; i++)
            {
                string botUsername = $"BOT{i.ToString("D2")}";
                Console.WriteLine($"Inserting {botUsername} record...");
                command.Parameters["username"].Value = botUsername;
                command.ExecuteNonQuery();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Exception: {ex.Message}");
}
Console.WriteLine("Done");
Console.ReadLine();

---

7. Finding the Unique Column in an Oracle Table

2019 Dec 11 sql pl/sql oracle

If you ever find yourself looking for the unique column of a table in an Oracle database, but don’t see a primary key assigned and you don’t want to run a query against all the records in the table because it is too large, you can try querying against DBA_TABLES or ALL_TABLES to get the number of rows in that table.

SELECT NUM_ROWS
FROM DBA_TABLES --you can also use ALL_TABLES
WHERE TABLE_NAME = 'your_table_name';

Once you have the number of rows, query DBA_TAB_COLUMNS or ALL_TAB_COLUMNS to see which column has the same distinct number of rows (NUM_DISTINCT) as the NUM_ROWS from the previous query.

SELECT COLUMN_NAME, NUM_DISTINCT
FROM DBA_TAB_COLUMNS --you can also use ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'your_table_name'
ORDER BY NUM_DISTINCT DESC;

Source: https://stackoverflow.com/a/4010826/991383

---

See all tags.