Struct oracle::Connection

pub struct Connection { /* private fields */ }

Connection to an Oracle database

Implementations

impl Connection

pub fn connect<U, P, C>( username: U, password: P, connect_string: C ) -> Result<Connection>

where U: AsRef<str>, P: AsRef<str>, C: AsRef<str>,

Connects to an Oracle server using username, password and connect string.

If you need to connect the server with additional parameters such as SYSDBA privilege, use Connector instead.

Examples

Connect to a local database.

let conn = Connection::connect("scott", "tiger", "")?;

Connect to a remote database specified by easy connect naming.

let conn = Connection::connect("scott", "tiger",
                               "server_name:1521/service_name")?;

pub fn close(&self) -> Result<()>

Closes the connection before the end of lifetime.

This fails when open statements or LOBs exist.

pub fn close_with_mode(&self, mode: CloseMode<'_>) -> Result<()>

pub fn statement<'conn, 'sql>( &'conn self, sql: &'sql str ) -> StatementBuilder<'conn, 'sql>

Creates StatementBuilder to create a Statement

Examples

Executes a SQL statement with different parameters.

let mut stmt = conn.statement("insert into emp(empno, ename) values (:id, :name)").build()?;

let emp_list = [
    (7369, "Smith"),
    (7499, "Allen"),
    (7521, "Ward"),
];

// insert rows using positional parameters
for emp in &emp_list {
   stmt.execute(&[&emp.0, &emp.1])?;
}

let emp_list = [
    (7566, "Jones"),
    (7654, "Martin"),
    (7698, "Blake"),
];

// insert rows using named parameters
for emp in &emp_list {
   stmt.execute_named(&[("id", &emp.0), ("name", &emp.1)])?;
}

Query methods in Connection allocate memory for 100 rows by default to reduce the number of network round trips in case that many rows are fetched. When 100 isn’t preferable, use StatementBuilder::fetch_array_size to customize it.

// fetch top 10 rows.
let mut stmt = conn
    .statement("select empno, ename from emp order by empno fetch first 10 rows only")
    .fetch_array_size(10)
    .build()?;
for row_result in stmt.query_as::<(i32, String)>(&[])? {
    let (empno, ename) = row_result?;
    println!("empno: {}, ename: {}", empno, ename);
}

By default, a maximum of 2 rows are returned when the query is first executed. To modify this, use StatementBuilder::prefetch_rows to customize it. For more information on the difference between this and fetch_array_size, see this writeup or this description.

// fetch top 10 rows.
let mut stmt = conn
    .statement("select empno, ename from emp order by empno fetch first 10 rows only")
    .prefetch_rows(11)  // add one to avoid a round-trip to check for end-of-fetch
    .build()?;
for row_result in stmt.query_as::<(i32, String)>(&[])? {
    let (empno, ename) = row_result?;
    println!("empno: {}, ename: {}", empno, ename);
}

pub fn batch<'conn, 'sql>( &'conn self, sql: &'sql str, max_batch_size: usize ) -> BatchBuilder<'conn, 'sql>

Creates BatchBuilder

See Batch.

pub fn query( &self, sql: &str, params: &[&dyn ToSql] ) -> Result<ResultSet<'static, Row>>

Executes a select statement and returns a result set containing Rows.

See Query Methods.

pub fn query_named( &self, sql: &str, params: &[(&str, &dyn ToSql)] ) -> Result<ResultSet<'static, Row>>

Executes a select statement using named parameters and returns a result set containing Rows.

See Query Methods.

pub fn query_as<T>( &self, sql: &str, params: &[&dyn ToSql] ) -> Result<ResultSet<'static, T>>

where T: RowValue,

Executes a select statement and returns a result set containing RowValues.

See Query Methods.

pub fn query_as_named<T>( &self, sql: &str, params: &[(&str, &dyn ToSql)] ) -> Result<ResultSet<'static, T>>

where T: RowValue,

Executes a select statement using named parameters and returns a result set containing RowValues.

See Query Methods.

pub fn query_row(&self, sql: &str, params: &[&dyn ToSql]) -> Result<Row>

Gets one row from a query using positoinal bind parameters.

See Query Methods.

pub fn query_row_named( &self, sql: &str, params: &[(&str, &dyn ToSql)] ) -> Result<Row>

Gets one row from a query using named bind parameters.

See Query Methods.

pub fn query_row_as<T>(&self, sql: &str, params: &[&dyn ToSql]) -> Result<T>

where T: RowValue,

Gets one row from a query as specified type.

See Query Methods.

pub fn query_row_as_named<T>( &self, sql: &str, params: &[(&str, &dyn ToSql)] ) -> Result<T>

where T: RowValue,

Gets one row from a query with named bind parameters as specified type.

See Query Methods.

pub fn execute(&self, sql: &str, params: &[&dyn ToSql]) -> Result<Statement>

Creates a statement, binds values by position and executes it in one call. It will retunrs Err when the statemnet is a select statement.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// execute a statement without bind parameters
conn.execute("insert into emp(empno, ename) values (113, 'John')", &[])?;

// execute a statement with binding parameters by position
conn.execute("insert into emp(empno, ename) values (:1, :2)", &[&114, &"Smith"])?;

pub fn execute_named( &self, sql: &str, params: &[(&str, &dyn ToSql)] ) -> Result<Statement>

Creates a statement, binds values by name and executes it in one call. It will retunrs Err when the statemnet is a select statement.

The bind variable names are compared case-insensitively.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// execute a statement with binding parameters by name
conn.execute_named("insert into emp(empno, ename) values (:id, :name)",
                   &[("id", &114),
                     ("name", &"Smith")])?;

pub fn commit(&self) -> Result<()>

Commits the current active transaction

pub fn rollback(&self) -> Result<()>

Rolls back the current active transaction

pub fn autocommit(&self) -> bool

Gets autocommit mode. It is false by default.

pub fn set_autocommit(&mut self, autocommit: bool)

Enables or disables autocommit mode. It is disabled by default.

pub fn break_execution(&self) -> Result<()>

Cancels execution of running statements in the connection

Examples

// Wrap conn with Arc to be share it with threads.
let conn = Arc::new(conn);

let now = Instant::now();
let range = Duration::from_secs(3)..=Duration::from_secs(20);

// Start a thread to cancel a query
let cloned_conn = conn.clone();
let join_handle = thread::spawn(move || {
  sleep(Duration::from_secs(3));
  cloned_conn.break_execution()
});

// This query is canceled by break_execution.
let result = conn.query_row_as::<u64>("select count(*) from all_objects, all_objects, all_objects, all_objects, all_objects", &[]);
assert!(result.is_err());
let elapsed = now.elapsed();
assert!(range.contains(&elapsed), "cancel: {:?}, {:?}", elapsed, result.unwrap_err());

pub fn object_type(&self, name: &str) -> Result<ObjectType>

Gets an object type information from name

let objtype = conn.object_type("SDO_GEOMETRY")?;
assert_eq!(objtype.schema(), "MDSYS");
assert_eq!(objtype.name(), "SDO_GEOMETRY");

Note that the object type is cached in the connection. However when “CREATE TYPE”, “ALTER TYPE” or “DROP TYPE” is executed, the cache clears.

pub fn clear_object_type_cache(&self) -> Result<()>

Clear the object type cache in the connection.

See also object_type.

pub fn server_version(&self) -> Result<(Version, String)>

Gets information about the server version

NOTE: if you connect to Oracle Database 18 or higher with Oracle client libraries 12.2 or lower, it gets the base version (such as 18.0.0.0.0) instead of the full version (such as 18.3.0.0.0).

Examples

let (version, banner) = conn.server_version()?;
println!("Oracle Version: {}", version);
println!("--- Version Banner ---");
println!("{}", banner);
println!("---------------------");

pub fn change_password( &self, username: &str, old_password: &str, new_password: &str ) -> Result<()>

Changes the password for the specified user

pub fn ping(&self) -> Result<()>

Pings the connection to see if it is still alive.

It checks the connection by making a network round-trip between the client and the server.

See also Connection::status.

pub fn status(&self) -> Result<ConnStatus>

Gets the status of the connection.

It returns Ok(ConnStatus::Closed) when the connection was closed by Connection::close. Otherwise see bellow.

Oracle client 12.2 and later:

It checks whether the underlying TCP socket has disconnected by the server. There is no guarantee that the server is alive and the network between the client and server has no trouble.

For example, it returns Ok(ConnStatus::NotConnected) when the database on the server-side OS stopped and the client received a FIN or RST packet. However it returns Ok(ConnStatus::Normal) when the server-side OS itself crashes or the network is in trouble.

Oracle client 11.2 and 12.1:

It returns Ok(ConnStatus::Normal) when the last network round-trip between the client and server went through. Otherwise, Ok(ConnStatus::NotConnected). There is no guarantee that the next network round-trip will go through.

See also Connection::ping.

pub fn last_warning(&self) -> Option<Error>

Get the warning when connecting to the database or executing SQL statements.

This is available to check the following two cases.

1. The user account status is in grace period of password expiration. See Password Change Life Cycle.
2. A stored procedure is created with PL/SQL compilation errors.

let conn = Connection::connect(username, password, connect_string)?;

// Check warning when connecting to the database.
// This must be done before any SQL execution. Otherwise it is cleared.
if let Some(Error::OciError(dberr)) = conn.last_warning() {
  if dberr.code() == 28002 {
     println!("{}", dberr.message()); // => "the password will expire within n days."
  }
}

// create a procedure with compilation error
let sql = "create or replace procedure my_proc is begin; null; end;";
conn.execute(sql, &[])?;
match conn.last_warning() {
    Some(Error::OciError(dberr)) if dberr.code() == 24344 => (),
    warn => panic!("Unexpected last warning: {:?}", warn),
}

// create a procedure without compilation error
let sql = "create or replace procedure my_proc is begin null; end;";
conn.execute(sql, &[])?;
// The last warning is cleared when a SQL statement is executed successfully without any warning.
match conn.last_warning() {
    None => (),
    warn => panic!("Unexpected last warning: {:?}", warn),
}

pub fn stmt_cache_size(&self) -> Result<u32>

Gets the statement cache size

See also Connector::stmt_cache_size

pub fn set_stmt_cache_size(&self, size: u32) -> Result<()>

Sets the statement cache size

See also Connector::stmt_cache_size

pub fn call_timeout(&self) -> Result<Option<Duration>>

Gets the current call timeout used for round-trips to the database made with this connection. None means that no timeouts will take place.

pub fn set_call_timeout(&self, dur: Option<Duration>) -> Result<()>

Sets the call timeout to be used for round-trips to the database made with this connection. None means that no timeouts will take place.

The call timeout value applies to each database round-trip individually, not to the sum of all round-trips. Time spent processing in rust-oracle before or after the completion of each round-trip is not counted.

• If the time from the start of any one round-trip to the completion of that same round-trip exceeds call timeout, then the operation is halted and an exception occurs.

• In the case where an rust-oracle operation requires more than one round-trip and each round-trip takes less than call timeout, then no timeout will occur, even if the sum of all round-trip calls exceeds call timeout.

• If no round-trip is required, the operation will never be interrupted.

After a timeout is triggered, rust-oracle attempts to clean up the internal connection state. The cleanup is allowed to take another duration.

If the cleanup was successful, an exception DPI-1067 will be raised but the application can continue to use the connection.

For small values of call timeout, the connection cleanup may not complete successfully within the additional call timeout period. In this case an exception ORA-3114 is raised and the connection will no longer be usable. It should be closed.

Examples

// Set timeout three seconds.
conn.set_call_timeout(Some(Duration::from_millis(3_000)))?;

let now = Instant::now();
let range = Duration::from_millis(2_900)..=Duration::from_millis(20_000);

// This query is canceled by timeout.
let result = conn.query_row_as::<u64>("select count(*) from all_objects, all_objects, all_objects, all_objects, all_objects", &[]);
assert!(result.is_err());
let elapsed = now.elapsed();
assert!(range.contains(&elapsed), "cancel: {:?}, {:?}", elapsed, result.unwrap_err());

pub fn current_schema(&self) -> Result<String>

Gets current schema associated with the connection

pub fn set_current_schema(&self, current_schema: &str) -> Result<()>

Sets current schema associated with the connection

conn.set_current_schema("MDSYS") has same effect with the following SQL.

ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

Examples

// Get the username and sid of connection 1.
let (username, sid) = conn1.query_row_as::<(String, i32)>("select user, sys_context('userenv', 'sid') from dual", &[])?;

// Create a closure to get the schema name of connection 1 in the database side using connection 2.
let mut stmt = conn2.statement("select schemaname from v$session where sid = :1").build()?;
let mut schema_name = move || { stmt.query_row_as::<String>(&[&sid]) };

// The default current schema is same with the username.
assert_eq!(schema_name()?, username);

// Change the current schema of connection 1.
let new_schema_name = "MDSYS";
conn1.set_current_schema(new_schema_name)?;

// The current schema of connection 1 in the database side has not been changed yet.
assert_eq!(schema_name()?, username);

// Call a function sending packets to the database server.
// The new schema name is prepended to the packets.
let _ = conn1.query_row_as::<i32>("select 1 from dual", &[]);

// The current schema of connection 1 in the database side is changed.
assert_eq!(schema_name()?, new_schema_name);

pub fn edition(&self) -> Result<String>

Gets edition associated with the connection

pub fn external_name(&self) -> Result<String>

Gets external name associated with the connection

pub fn set_external_name(&self, external_name: &str) -> Result<()>

Sets external name associated with the connection

pub fn internal_name(&self) -> Result<String>

Gets internal name associated with the connection

pub fn set_internal_name(&self, internal_name: &str) -> Result<()>

Sets internal name associated with the connection

pub fn set_module(&self, module: &str) -> Result<()>

Sets module associated with the connection

This is same with calling DBMS_APPLICATION_INFO.SET_MODULE but without executing a statement. The module name is piggybacked to the server with the next network round-trip.

pub fn set_action(&self, action: &str) -> Result<()>

Sets action associated with the connection

This is same with calling DBMS_APPLICATION_INFO.SET_ACTION but without executing a statement. The action name is piggybacked to the server with the next network round-trip.

pub fn set_client_info(&self, client_info: &str) -> Result<()>

Sets client info associated with the connection

This is same with calling DBMS_APPLICATION_INFO.SET_CLIENT_INFO but without executing a statement. The client info is piggybacked to the server with the next network round-trip.

pub fn set_client_identifier(&self, client_identifier: &str) -> Result<()>

Sets client identifier associated with the connection

This is same with calling DBMS_SESSION.SET_IDENTIFIER but without executing a statement. The client identifier is piggybacked to the server with the next network round-trip.

pub fn set_db_op(&self, db_op: &str) -> Result<()>

Sets name of the database operation to be monitored in the database. Sets to '' if you want to end monitoring the current running database operation.

This is same with calling DBMS_SQL_MONITOR.BEGIN_OPERATION but without executing a statement. The database operation name is piggybacked to the server with the next network round-trip.

See Monitoring Database Operations in Oracle Database SQL Tuning Guide

pub fn startup_database(&self, modes: &[StartupMode]) -> Result<()>

Starts up a database

This corresponds to sqlplus command startup nomount. You need to connect the databas as system privilege in prelim_auth mode in advance. After this method is executed, you need to reconnect the server as system privilege without prelim_auth and executes alter database mount and then alter database open.

Examples

Connect to an idle instance as sysdba and start up a database

// connect as sysdba with prelim_auth mode
let conn = Connector::new("sys", "change_on_install", "")
    .privilege(Privilege::Sysdba)
    .prelim_auth(true)
    .connect()?;

// start the instance
conn.startup_database(&[])?;
conn.close()?;

// connect again without prelim_auth
let conn = Connector::new("sys", "change_on_install", "")
    .privilege(Privilege::Sysdba)
    .connect()?;

// mount and open a database
conn.execute("alter database mount", &[])?;
conn.execute("alter database open", &[])?;

Start up a database in restricted mode

...
conn.startup_database(&[StartupMode::Restrict])?;
...

If the database is running, shut it down with mode ABORT and then start up in restricted mode

...
conn.startup_database(&[StartupMode::Force, StartupMode::Restrict])?;
...

pub fn shutdown_database(&self, mode: ShutdownMode) -> Result<()>

Shuts down a database

When this method is called with ShutdownMode::Default, ShutdownMode::Transactional, ShutdownMode::TransactionalLocal or ShutdownMode::Immediate, execute “alter database close normal” and “alter database dismount” and call this method again with ShutdownMode::Final.

When this method is called with ShutdownMode::Abort, the database is aborted immediately.

Examples

Same with shutdown immediate on sqlplus.

// connect as sysdba
let conn = Connector::new("sys", "change_on_install", "")
    .privilege(Privilege::Sysdba)
    .connect()?;

// begin 'shutdown immediate'
conn.shutdown_database(ShutdownMode::Immediate)?;

// close and dismount the database
conn.execute("alter database close normal", &[])?;
conn.execute("alter database dismount", &[])?;

// finish shutdown
conn.shutdown_database(ShutdownMode::Final)?;

Same with shutdown abort on sqlplus.

// connect as sysdba
let conn = Connector::new("sys", "change_on_install", "")
    .privilege(Privilege::Sysdba).connect()?;

// 'shutdown abort'
conn.shutdown_database(ShutdownMode::Abort)?;

// The database is aborted here.

pub fn tag(&self) -> &str

Gets the tag of the connection that was acquired from a connection pool. It is "" if the connection is a standalone one or not tagged.

Examples

// standalone connection
let conn = Connection::connect(username, password, connect_string)?;
assert_eq!(conn.tag(), "");
assert_eq!(conn.tag_found(), false);

let pool = PoolBuilder::new(username, password, connect_string)
    .build()?;
let opts = PoolOptions::new().tag("NAME=VALUE");

// No connections with tag "NAME=VALUE" exist in the pool at first.
let conn = pool.get_with_options(&opts)?;
assert_eq!(conn.tag(), "");
assert_eq!(conn.tag_found(), false);
// Close the connection with setting a new tag.
conn.close_with_mode(conn::CloseMode::Retag("NAME=VALUE"))?;

// One connection with tag "NAME=VALUE" exists in the pool now.
let conn = pool.get_with_options(&opts)?;
assert_eq!(conn.tag_found(), true);
assert_eq!(conn.tag(), "NAME=VALUE");

pub fn tag_found(&self) -> bool

Gets true when the connection is a standalone one or it is a connection with the specified tag by PoolOptions::tag.

Sea also Connection::tag.

pub fn is_new_connection(&self) -> bool

Returns true when the connection is a standalone one or a newly created one by a connection pool.

Examples

let conn = Connection::connect(username, password, connect_string)?;
assert!(conn.is_new_connection(), "standalone connection");

let pool = PoolBuilder::new(username, password, connect_string)
    .build()?;

let conn = pool.get()?; // Get a newly created connection
assert!(conn.is_new_connection(), "new connectoin from the pool");
conn.close()?; // Back the connection to the pool

let conn = pool.get()?; // Get a connection cached in the pool
assert!(!conn.is_new_connection(), "cached connectoin in the pool");
conn.close()?;

pub fn oci_attr<T>( &self ) -> Result<<<T::DataType as DataType>::Type as ToOwned>::Owned>

where T: OciAttr, T::HandleType: ConnHandle, T::Mode: ReadMode,

Gets an OCI handle attribute corresponding to the specified type parameter See the oci_attr module for details.

pub fn set_oci_attr<T>( &mut self, value: &<T::DataType as DataType>::Type ) -> Result<()>

where T: OciAttr, T::HandleType: ConnHandle, T::Mode: WriteMode,

Sets an OCI handle attribute corresponding to the specified type parameter See the oci_attr module for details.

Trait Implementations

impl Debug for Connection

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl Drop for Connection

fn drop(&mut self)

Executes the destructor for this type.