Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagepy
"""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
cursor = dbh.cursor()
cursor.execute("""SELECT VC_SUBJ_TYPE_NAME, VC_CODE
FROM SI_V_SUBJECTS
WHERE N_SUBJECT_ID = 100""")

# 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
for row in data:
    print(f"VC_SUBJ_TYPE_NAME: {row['VC_SUBJ_TYPE_NAME']}")
    print(f"VC_CODE: {row['VC_CODE']}")


# Close the database connection
dbh.close()


PROCEDURE

Code Block
languagepy
"""Example of a procedure call to an Oracle database"""
import cx_Oracle

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

# Obtain a cursor
cursor = dbh.cursor()

# Prepare variables
num_N_SUBJECT_ID = cursor.var(cx_Oracle.NUMBER)

# Call procedure
cursor.execute("""BEGIN
SI_PERSONS_PKG.SI_PERSONS_PUT(
num_N_FIRM_ID => 100,
num_N_SUBJECT_ID => :num_N_SUBJECT_ID,
vch_VC_SURNAME => 'Ivanov',
vch_VC_FIRST_NAME => 'Ivan',
vch_VC_SECOND_NAME => 'Ivanovich');
END;""", num_N_SUBJECT_ID=num_N_SUBJECT_ID)

# Print value of a created basic subject
print(f"Created basic subject {num_N_SUBJECT_ID.getvalue()}")

# Commit changes
dbh.commit()

# Close the database connection
dbh.close()


PHP

Работа с СУБД выполняется с использованием OCI8

SELECT

Code Block
languagephp
<?php
// Database sid
$dsn = 'hydra2';
// Database login
$login = 'AIS_RPC';
// Database password
$password = '********';
// Select query
$query = 'SELECT VC_SUBJ_TYPE_NAME, VC_CODE FROM SI_V_SUBJECTS WHERE N_SUBJECT_ID = 100';

// Establish the database connection
$dbh = oci_connect($login, $password, $dsn);
if (!$dbh) {
    $e = oci_error();
    exit(1);
}

// Prepare and execute query
$sth = oci_parse($dbh, $query);
oci_execute($sth);

// Get and output the result.
while (($row = oci_fetch_assoc($sth)) !== false) {
    echo "VC_SUBJ_TYPE_NAME: " . $row['VC_SUBJ_TYPE_NAME'] . "\n";
    echo "VC_CODE: " . $row['VC_CODE'] . "\n";
}

// Free prepared statement
oci_free_statement($sth);

// Close the database connection
oci_close($dbh);


PROCEDURE

Code Block
languagephp
<?php
// Database sid
$dsn = 'hydra2';
// Database login
$login = 'AIS_RPC';
// Database password
$password = '********';
// Procedure
$procedure = <<< EOT
BEGIN
SI_PERSONS_PKG.SI_PERSONS_PUT(
            num_N_FIRM_ID => 100,
            num_N_SUBJECT_ID => :num_N_SUBJECT_ID,
            vch_VC_SURNAME => 'Ivanov',
            vch_VC_FIRST_NAME => 'Ivan',
            vch_VC_SECOND_NAME => 'Ivanovich'
        );
END;
EOT;

// Output variable
$num_N_SUBJECT_ID = null;

// Establish the database connection
$dbh = oci_connect($login, $password, $dsn);
if (!$dbh) {
    $e = oci_error();
    exit(1);
}

// Prepare on Oracle statement for execution
$sth = oci_parse($dbh, $procedure);
// Bind variables
oci_bind_by_name($sth, ':num_N_SUBJECT_ID', $num_N_SUBJECT_ID, 32);
// Call procedure
oci_execute($sth, OCI_NO_AUTO_COMMIT);

// Print value of a created basic subject
echo "Created basic subject " . $num_N_SUBJECT_ID . "\n";

// Commit changes
oci_commit($dbh);

// Free prepared statement
oci_free_statement($sth);

// Close the database connection
oci_close($dbh);