Oracle Database JDBC

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/
Admin
Oracle Database JDBC provides Java Database Connectivity (JDBC) drivers that enable Java...
Tokens:267,831
Snippets:2,032
Trust Score:9.1
Update:1 month ago
Show doc for...
Tokens:
Raw
### Database Startup and Shutdown Example (DBStartup)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-administration.html

Complete example from Example 33-1 that starts the database with NO_RESTRICTION, mounts and opens it via ALTER DATABASE statements, then shuts it down using IMMEDIATE followed by FINAL. Requires a password file for user "sys" and the REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE setting in init.ora before restarting the database.

```java
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
/**
 * To logon as sysdba, you need to create a password file for user "sys":
 *   orapwd file=/path/orapw password=password entries=300
 * and add the following setting in init.ora:
 *   REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 * then restart the database.
 */
public class DBStartup
{
  static final String DB_URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5221))"
+ "(CONNECT_DATA=(SERVICE_NAME=rdbms.devplmt.XYZ.com)))";

  public static void main(String[] argv) throws Exception
  {
// Starting up the database:
    OracleDataSource ds = new OracleDataSource();
    Properties prop = new Properties();
    prop.setProperty("user","sys");
    prop.setProperty("password","manager");
    prop.setProperty("internal_logon","sysdba");
    prop.setProperty("prelim_auth","true");
    ds.setConnectionProperties(prop);
    ds.setURL(DB_URL);
    OracleConnection conn = (OracleConnection)ds.getConnection();
    conn.startup(OracleConnection.DatabaseStartupMode.NO_RESTRICTION);
    conn.close();

// Mounting and opening the database
    OracleDataSource ds1 = new OracleDataSource();
    Properties prop1 = new Properties();
    prop1.setProperty("user","sys");
    prop1.setProperty("password","manager");
    prop1.setProperty("internal_logon","sysdba");
    ds1.setConnectionProperties(prop1);
    ds1.setURL(DB_URL);
    OracleConnection conn1 = (OracleConnection)ds1.getConnection();
    Statement stmt = conn1.createStatement();
    stmt.executeUpdate("ALTER DATABASE MOUNT");
    stmt.executeUpdate("ALTER DATABASE OPEN");
    stmt.close();
    conn1.close();

// Shutting down the database
    OracleDataSource ds2 = new OracleDataSource();
    Properties prop = new Properties();
    prop.setProperty("user","sys");
    prop.setProperty("password","manager");
    prop.setProperty("internal_logon","sysdba");
    ds2.setConnectionProperties(prop);
    ds2.setURL(DB_URL);
    OracleConnection conn2 = (OracleConnection)ds2.getConnection();
    conn2.shutdown(OracleConnection.DatabaseShutdownMode.IMMEDIATE);
    Statement stmt1 = conn2.createStatement();
    stmt1.executeUpdate("ALTER DATABASE CLOSE NORMAL");
    stmt1.executeUpdate("ALTER DATABASE DISMOUNT");
    stmt1.close();
    conn2.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);
    conn2.close();
  }
}

```

--------------------------------

### Start Database Instance with startup Method

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-administration.html

Starts a down database instance using the JDBC Thin driver. Requires connecting as SYSDBA with PRELIM_AUTH=true; only the startup method is allowed in this mode. The example sets the internal_logon property to sysdba and uses the startup method with NO_RESTRICTION mode.

```java
OracleDataSource ds = new OracleDataSource();
    Properties prop = new Properties();
    prop.setProperty("user","sys");
    prop.setProperty("password","manager");
    prop.setProperty("internal_logon","sysdba");
    prop.setProperty("prelim_auth","true");
    ds.setConnectionProperties(prop);
    ds.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XYZ.com)(PORT=5221))"
+ "(CONNECT_DATA=(SERVICE_NAME=rdbms.devplmt.XYZ.com)))");
    OracleConnection conn = (OracleConnection)ds.getConnection();
    conn.startup(OracleConnection.DatabaseStartupMode.NO_RESTRICTION);
    conn.close();
```

--------------------------------

### Oracle home name: net start command example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/preface.html

HOME_NAME represents the Oracle home name, which can be up to 16 alphanumeric characters. The only special character allowed is the underscore. This example shows starting a TNS listener service.

```text
C:\> net start OracleHOME_NAMETNSListener
```

--------------------------------

### Open Proxy Session Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/proxy-authentication.html

Example demonstrating how to configure properties and open a proxy session for a user with specific roles.

```java
    java.util.Properties prop = new java.util.Properties();
    prop.put(OracleConnection.PROXY_USER_NAME, "jeff");
    String[] roles = {"role1", "role2"};
    prop.put(OracleConnection.PROXY_ROLES, roles);
    conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);
```

--------------------------------

### Thin-style connection string example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-OCI-features.html

Example of a Thin-style connection string using the OCI driver.

```text
url="jdbc:oracle:oci:@example.com:5521:orcl"
```

--------------------------------

### Start the default connection pool using SQL*Plus

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-resident-connection-pooling.html

Run these commands as SYSDBA to start the SYS_DEFAULT_CONNECTION_POOL with default settings.

```sql
sqlplus /nolog
connect / as sysdba
execute dbms_connection_pool.start_pool();
```

--------------------------------

### SQL connection URL string example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-OCI-features.html

Example of a SQL connection URL string using the OCI driver.

```text
url="jdbc:oracle:oci:@//example.com:5521/orcl"
```

--------------------------------

### wallet_location parameter: old and new URL examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Shows the old DESCRIPTOR-based URL and the new Easy Connect Plus URL for specifying the wallet_location parameter. The new URL uses a question mark to start the parameter list and an ampersand to separate parameters.

```text
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)) 
```

```text
jdbc:oracle:thin:@tcps:salesserver1.com:1521/sales.us.example.com?wallet_location=/Users/jsmith/DBCloudService/wallet_JDBCTEST&oracle.net.ssl_server_cert_dn="CN=salesserver2.com.com,OU=Oracle                                     BMCS US,O=Oracle Corporation,L=Redwood                                     City,ST=California,C=US"
```

--------------------------------

### TRACE_DIRECTORY_SERVER=/oracle/traces example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-troubleshooting.html

Specifies the destination directory for the server trace file. The example sets the directory to /oracle/traces.

```plaintext
TRACE_DIRECTORY_SERVER=/oracle/traces
```

--------------------------------

### https_proxy and https_proxy_port parameters: old and new URL examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Shows the old DESCRIPTOR-based URL and the new Easy Connect Plus URL for specifying the https_proxy and https_proxy_port parameters. The new URL uses a question mark to start the parameter list and an ampersand to separate parameters.

```text
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1521) (https_proxy=www-proxy.mycompany.com)(https_proxy_port=80)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))
```

```text
jdbc:oracle:thin:@tcps:salesserver1:1521/sales.us.example.com?https_proxy=www-proxy.mycompany.com&https_proxy_port=80
```

--------------------------------

### Create AQ message and set payload (Example 25-1)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/advanced-queuing.html

Complete example from Example 25-1: creates AQMessageProperties, sets correlation, exception queue, sender agent, optional recipient list, creates AQMessage, and sets payload.

```java
AQMessageProperties msgprop = AQFactory.createAQMessageProperties();
    msgprop.setCorrelation("mycorrelation");
    msgprop.setExceptionQueue("MY_EXCEPTION_QUEUE");
    AQAgent ag = AQFactory.createAQAgent();
    ag.setName("MY_SENDER_AGENT_NAME");
    ag.setAddress("MY_SENDER_AGENT_ADDRESS");
    msgprop.setSender(ag);
    // handle multi consumer case:
    if(recipients != null)
      msgprop.setRecipientList(recipients);
    System.out.println(msgprop.toString());
    AQMessage mesg = AQFactory.createAQMessage(msgprop);
byte[] rawPayload = "Example_Payload".getBytes();
mesg.setPayload(new oracle.sql.RAW(rawPayload));
```

--------------------------------

### ssl_server_cert_dn parameter: old and new URL examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Shows the old DESCRIPTOR-based URL and the new Easy Connect Plus URL for specifying the ssl_server_cert_dn parameter. The new URL uses a question mark to start the parameter list and an ampersand to separate parameters.

```text
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1521)) (SECURITY= (SSL_SERVER_DN_MATCH=TRUE) (SSL_SERVER_CERT_DN=cn=sales,cn=OracleContext,dc=us,dc=example,dc=com)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))) 
```

```text
jdbc:oracle:thin:@tcps:salesserver1:1521/sales.us.example.com?ssl_server_cert_dn="cn=sales,cn=OracleContext,dc=us,dc=example,dc=com"
```

--------------------------------

### listener.ora configuration example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-OCI-features.html

Shows a sample listener.ora file with a listener address and SID list for a database server. This is used as a reference for the connection examples that follow.

```text
LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=5221)))

SID_LIST_LISTENER = (SID_LIST=
                     (SID_DESC=(SID_NAME=rdbms3)
                     (GLOBAL_DBNAME=rdbms3.server6.com)
                     (ORACLE_HOME=/home/dba/rdbms3/oracle)))
```

--------------------------------

### connect_timeout parameter: old and new URL examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Shows the old DESCRIPTOR-based URL and the new Easy Connect Plus URL for specifying the connect_timeout, transport_connect_timeout, and retry_count parameters. The new URL uses a question mark to start the parameter list and an ampersand to separate parameters.

```text
jdbc:oracle:thin:@(DESCRIPTION= (retry_count=3) (connect_timeout=60)(transport_connect_timeout=30) (ADDRESS=(PROTOCOL=tcp)(HOST=salesserver1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))
```

```text
jdbc:oracle:thin:@tcps:salesserver1:1521/sales.us.example.com?connect_timeout=60&                                     transport_connect_timeout=30&retry_count=3
```

--------------------------------

### Start queue with DBMS_AQADM.START_QUEUE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/advanced-queuing.html

Starts the queue HR.RAW_SINGLE_QUEUE. The source shows an incomplete statement (missing closing parenthesis and semicolon).

```sql
BEGIN
    DBMS_AQADM.START_QUEUE(
 'HR.RAW_SINGLE_QUEUE',
END;
```

--------------------------------

### Test JDBC and database connection with JdbcCheckup.java

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-getting-started.html

Use this sample to verify that the JDBC client and database connection are correctly installed. It prompts for user, password, and database TNSNAME, connects using the JDBC OCI driver, runs a query for 'Hello World', and prints the result. If the query prints without error, the Java and JDBC installations are correct.

```java
/*
 * This sample can be used to check the JDBC installation.
 * Just run it and provide the connect information. It will select
 * "Hello World" from the database.
 */

// You need to import the java.sql and JDBC packages to use JDBC
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup
{
  public static void main(String args[]) throws SQLException, IOException
  {

    // Prompt the user for connect information
    System.out.println("Please enter information to test connection to 
                          the database");
    String user;
    String password;
    String database;

    user = readEntry("user: ");
    int slash_index = user.indexOf('/');
    if (slash_index != -1)
    {
      password = user.substring(slash_index + 1);
      user = user.substring(0, slash_index);
    }
    else
      password = readEntry("password: ");
    database = readEntry("database(a TNSNAME entry): ");

    System.out.print("Connecting to the database...");
    System.out.flush();
    System.out.println("Connecting...");
    // Open an OracleDataSource and get a connection
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:oci:@" + database);
    ods.setUser(user);
    ods.setPassword(password);
    Connection conn = ods.getConnection();
    System.out.println("connected.");

    // Create a statement
    Statement stmt = conn.createStatement();

    // Do the SQL "Hello World" thing
    ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");

    while (rset.next())
      System.out.println(rset.getString(1));
    // close the result set, the statement and the connection
    rset.close();
    stmt.close();
    conn.close();
    System.out.println("Your JDBC installation is correct.");
  }

  // Utility function to read a line from standard input
  static String readEntry(String prompt)
  {
    try
    {
      StringBuffer buffer = new StringBuffer();
      System.out.print(prompt);
      System.out.flush();
      int c = System.in.read();
      while (c != '\n' && c != -1)
      {
        buffer.append((char)c);
        c = System.in.read();
      }
      return buffer.toString().trim();
    }
    catch(IOException e)
    {
      return "";
    }
  }
}

```

--------------------------------

### SQL query example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/preface.html

Example of a SQL query used to illustrate the conventions for code examples in Oracle documentation.

```sql
SELECT username FROM dba_users WHERE username = 'MIGRATE';
```

--------------------------------

### Start transaction branch with TMNOMIGRATE flag

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/distributed-transactions.html

Use this call to start a transaction branch with the TMNOMIGRATE flag to avoid the ORA-1002 fetch out of sequence error. The flags are Oracle extensions and must be used with the start method.

```java
start(xid, XAResource.TMSUSPEND | OracleXAResource.TMNOMIGRATE);
```

--------------------------------

### Enable Instant Client Light by replacing libociei.so with libociicus.so

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-OCI-features.html

Use when switching an Instant Client installation to Instant Client Light on UNIX. Requires the OCI Data Shared Library (libociei.so) to be removed and the Light library (libociicus.so) to be moved from the light subdirectory to the base directory. The directory must be on the LD_LIBRARY_PATH environment variable.

```shell
cd my_oraic_18_1
rm libociei.so
mv light/libociicus.so .

```

--------------------------------

### Complete Example: Using Transaction Guard APIs

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/transaction-guard.html

Complete Java example showing how to connect using OracleDataSource, retrieve the first LogicalTransactionId, and register a listener to receive subsequent LTXID updates via LogicalTransactionIdEventListener.

```java
      import oracle.jdbc.pool.OracleDataSource;
      import oracle.jdbc.OracleConnection;
      import oracle.jdbc.LogicalTransactionId;
      import oracle.jdbc.LogicalTransactionIdEvent;
      import oracle.jdbc.LogicalTransactionIdEventListener;
      
      public class transactionGuardExample
      {
            ...
            ...
            OracleDataSource ods = new OracleDataSource();
            ods.setURL(url);
            ods.setUser("user");
            ods.setPassword("password");
 
            OracleConnection oconn = (OracleConnection) ods.getConnection();
 
            // Getting the 1st LTXID after connecting
            LogicalTransactionId firstLtxid = oconn.getLogicalTransactionId();
 
            // The subsequent LTXID updates can be obtained via the listener
            oconn.addLogicalTransactionIdEventListener(this);
        }
 
      public class LtxidListenerImpl
        implements LogicalTransactionIdEventListener
      {
        ...
 
        public void onLogicalTransactionIdEvent(LogicalTransactionIdEvent ltxidEvent)
        {
          LogicalTransactionId newLtxid = ltxidEvent.getLogicalTransactionId();
          // process newLtxid ......
        }
      }
```

--------------------------------

### Example: Calling GET_LTXID_OUTCOME from Java

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/transaction-guard.html

Java example (Example 27-1) showing how to call the GET_LTXID_OUTCOME procedure using a CallableStatement to determine if an LTXID was committed and if the user call completed. Catches SQLException and prints failure details.

```java
    ...
    OracleConnection oconn = (OracleConnection) ods.getConnection();
    LogicalTransactionId ltxid = oconn.getLogicalTransactionId();
    boolean committed = false;
    boolean call_completed = false;
 
    try
    {
      CallableStatement cstmt = oconn.prepareCall(GET_LTXID_OUTCOME);
      cstmt.setObject(1, ltxid);
      cstmt.registerOutParameter(2, OracleTypes.BIT);
      cstmt.registerOutParameter(3, OracleTypes.BIT);
 
      cstmt.execute();
 
      committed = cstmt.getBoolean(2);
      call_completed = cstmt.getBoolean(3);
 
      System.out.println("LTXID committed ? " + committed);
      System.out.println("User call completed ? " + call_completed);
    }
    catch (SQLException sqlexc)
    {
      System.out.println("Calling GET_LTXID_OUTCOME failed");
      sqlexc.printStackTrace();
    }
```

--------------------------------

### Create OracleDataSource and connect

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Basic example of creating an OracleDataSource instance, setting its properties, and obtaining a connection. Requires vendor-specific, hard-coded property settings and does not use JNDI.

```java
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setServerName("localhost");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName(<database_name>);
ods.setPortNumber(5221);
ods.setUser("HR");
ods.setPassword("hr");
Connection conn = ods.getConnection();
```

--------------------------------

### Launch JDBC Application with -Djava.net.preferIPv4Stack=true

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-troubleshooting.html

Command to launch a JDBC application with the JVM property -Djava.net.preferIPv4Stack set to true, forcing IPv4 and resolving the connection refused error when the server supports both IPv4 and IPv6.

```bash
java -Djava.net.preferIPv4Stack=true jdbcTest

```

--------------------------------

### Example DRCP JDBC Connection String

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-resident-connection-pooling.html

Example of a DRCP-enabled JDBC thin connection string using the :POOLED suffix.

```text
jdbc:oracle:thin:@//localhost:5221/orcl:POOLED
```

--------------------------------

### Verify Java compiler and interpreter

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-getting-started.html

Run these commands in the samples directory under ORACLE_HOME/jdbc/demo to verify the Java compiler and interpreter work. Each command should display a list of options and parameters and then exit.

```text
javac

java
```

--------------------------------

### Configure Oracle Database for Kerberos (9.8.3)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/client-side-security.html

These are the seven sequential steps to configure Oracle Database for Kerberos authentication. They include connecting as system, creating an externally identified user, dismounting the database, setting OS_AUTHENT_PREFIX, restarting with a pfile, editing sqlnet.ora, and verifying with kinit/klist and sqlplus. The steps must be performed in the order shown.

```sqlplus
SQL> connect system
Enter password: password

```

```sqlplus
SQL> create user "CLIENT@US.ORACLE.COM" identified externally;
SQL> grant create session to "CLIENT@US.ORACLE.COM";

```

```sqlplus
SQL> connect / as sysdba
SQL> shutdown immediate;

```

```text
OS_AUTHENT_PREFIX=""

```

```sqlplus
SQL> startup pfile=t_init1.ora

```

```text
names.directory_path = (tnsnames)
#Kerberos
sqlnet.authentication_services = (beq,kerberos5)
sqlnet.authentication_kerberos5_service = dbji
sqlnet.kerberos5_conf = /home/Jdbc/Security/kerberos/krb5.conf
sqlnet.kerberos5_keytab = /home/Jdbc/Security/kerberos/dbji.oracleserver
sqlnet.kerberos5_conf_mit = true
sqlnet.kerberos_cc_name = /tmp/krb5cc_5088
# logging (optional):
trace_level_server=16 
trace_directory_server=/scratch/sqlnet/

```

```shell
> kinit client
> klist
     Ticket cache: FILE:/tmp/krb5cc_5088
     Default principal: client@US.ORACLE.COM
    
     Valid starting     Expires            Service principal
     06/22/06 07:13:29  06/22/06 17:13:29  krbtgt/US.ORACLE.COM@US.ORACLE.COM
    
    
     Kerberos 4 ticket cache: /tmp/tkt5088
     klist: You have no tickets cached
> sqlplus '/@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver.mydomain.com)(PORT=5221))
(CONNECT_DATA=(SERVICE_NAME=orcl)))'

```

--------------------------------

### Bequeath connection example for OCI

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html

Bequeath connection example for the OCI driver. The connection string is empty after the @ symbol, as noted in the source.

```java
"jdbc:oracle:oci:HR/hr/@"
```

--------------------------------

### Time Literal Example {t '05:10:45'}

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-reference-information.html

Example of a time literal in JDBC escape syntax. The driver converts it to the Oracle representation '05:10:45'.

```sql
{t '05:10:45'}
```

--------------------------------

### Setting command parameters and executing

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-Rowsets.html

Sets a SQL query with a bind parameter, sets the input parameter to 199, and executes the query to populate the RowSet with employee data.

```java
Copy
 ...
  rowset.setCommand("SELECT first_name, last_name, salary FROM employees WHERE employee_id = ?");
  // setting the employee number input parameter for employee named "Douglas"
  rowset.setInt(1, 199);
  rowset.execute();
  ...

```

--------------------------------

### Connect with Thin driver using URL with credentials

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-getting-started.html

Connects using the Thin driver to a database on host myhost with TCP/IP listener on port 5221 and service identifier orcl. The URL includes the user name and password and is the only input parameter. The user name and password in the URL override any specified in the URL.

```java
String URL = "jdbc:oracle:thin:HR/hr@//myhost:5221/orcl");
ods.setURL(URL);
Connection conn = ods.getConnection();

```

--------------------------------

### Continuous Query Notification example (DBChangeNotification)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/continuos-query-notification.html

Example 26-1 Continuous Query Notification. This code registers a table-level DCN registration, sets DCN_NOTIFY_ROWIDS and DCN_QUERY_CHANGE_NOTIFICATION properties, and requires the HR user to have the change notification privilege. The code is truncated in the source; the full example is not shown.

```java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
 
public class DBChangeNotification
{
  static final String USERNAME= "HR";
  static final String PASSWORD= "hr";
  static String URL;
  
  public static void main(String[] argv)
  {
    if(argv.length < 1)
    {
      System.out.println("Error: You need to provide the URL in the first argument.");
      System.out.println("  For example: > java -classpath .:ojdbc6.jar DBChangeNotification \"jdbc:oracle:thin:
@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhost.yourdomain.com)(PORT=5221))(CONNECT_DATA=
(SERVICE_NAME=orcl)))\"");
 
      System.exit(1);
    }
    URL = argv[0];
    DBChangeNotification demo = new DBChangeNotification();
    try
    {
      demo.run();
    }
    catch(SQLException mainSQLException )
    {
      mainSQLException.printStackTrace();
    }
  }
 
  void run() throws SQLException
  {
    OracleConnection conn = connect();
      
    // first step: create a registration on the server:
    Properties prop = new Properties();
    
    // if connected through the VPN, you need to provide the TCP address of the client.
    // For example:
    // prop.setProperty(OracleConnection.NTF_LOCAL_HOST,\"14.14.13.12\");
 
    // Ask the server to send the ROWIDs as part of the DCN events (small performance
    // cost):
    prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,\"true\");
// 
//Set the DCN_QUERY_CHANGE_NOTIFICATION option for query registration with finer granularity.
 prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,\"true\");
 
    // The following operation does a roundtrip to the database to create a new
    // registration for DCN. It sends the client address (ip address and port) that
    // the server will use to connect to the client and send the notification
    // when necessary. Note that for now the registration is empty (we haven't registered
    // any table). This also opens a new thread in the drivers. This thread will be
```
Tokens:
Raw