Page History

"""Example of a select query to an Oracle Database"""
import cx_Oracle

# Establish the database connection
dbh = cx_Oracle.connect(user='AIS_RPC',
                        password='********',
                        dsn="hydra2"
                        )

# Obtain a cursor and execute query
num_N_SUBJECT_ID = 100
with dbh.cursor() as cursor:
    cursor.execute("""
        SELECT VC_SUBJ_TYPE_NAME, VC_CODE
        FROM SI_V_SUBJECTS
        WHERE N_SUBJECT_ID = :num_N_SUBJECT_ID
    """, [num_N_SUBJECT_ID])

    # Fetch column names and data as an associative array
    columns = [i[0] for i in cursor.description]
    data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    print(data)

# Print data
for row in data:
    print(f"VC_SUBJ_TYPE_NAME: {row['VC_SUBJ_TYPE_NAME']}")
    print(f"VC_CODE: {row['VC_CODE']}")

import java.sql.*;


public class Oracle {
    public static void main(String[] args) {
        String dsn = "hydra2";
        String login = "AIS_RPC";
        String password = "********";

        try {
            // Establish the database connection
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + dsn, login, password);
            // Call exec method
            Oracle.select(conn);
            // Close the database connection
            conn.close();
        } catch (SQLException e) {
            System.err.println("Connection error!");
            e.printStackTrace();
        }
    }

    public static void execselect(Connection conn) {
        // Select query
        String query = """
			SELECT VC_SUBJ_TYPE_NAME, VC_CODE 
			FROM SI_V_SUBJECTS WHERE N_SUBJECT_ID = 100
		""";

        try {
            // Create an Oracle statement for execution
            Statement stmt = conn.createStatement();
            // Execute query
            ResultSet rs = stmt.executeQuery(query);
            // Get and output the result.
            while (rs.next()) {
                String vcSubjTypeName = rs.getString("VC_SUBJ_TYPE_NAME");
                String vcCode = rs.getString("VC_CODE");
                System.out.println("VC_SUBJ_TYPE_NAME: " + vcSubjTypeName);
                System.out.println("VC_CODE: " + vcCode);
            }
        } catch (SQLException e) {
            System.err.println("Select error!");
            e.printStackTrace();
        }
    }
}