xmloracle-databasepowershellnclob

How to get NCLOB XML data from Oracle database


We use a Java-based application to deploy XML files, which are written to an Oracle database. The XML blobs in the db are stored as NCLOB data types. How would I go about getting the NCLOB XML data from the database and back into XML format? Would I need to use Java (I'm a complete Java noob, BTW), or could I use PowerShell here (my preference)? And depending on the method, how would I do this?

The reason I want to do this is mostly for pre/post deployment validation (compare the XML content before and after it's been deployed).

Thanks in advance, Keith


Solution

  • Here's what I've done in the past to read/write XML in a Oracle CLOB using ODAC. This should work for NCLOB with very little modification.

    # Load ODAC. This might fail if it is not installed or is the wrong bitness.
    $assembly = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
    
    # Connect to Oracle.
    $connStr = 'Data Source=hostname:1521/sidname; User Id=sys; Password=password; DBA Privilege=SYSDBA;'
    $conn = New-Object Oracle.DataAccess.Client.OracleConnection -ArgumentList $connStr
    $conn.Open()
    
    # Query the table.
    $q = "select MY_CLOB_FIELD from My_Table"
    $command = new-object Oracle.DataAccess.Client.OracleCommand($q, $conn)
    
    # Process records.
    $reader = $command.ExecuteReader()
    while ($reader.Read()) {
        # Read the CLOB field and cast to an XML document.
        $xmlDoc = [xml] $reader.getstring(0) # XML
    
        #... XML Processing Here ....
    
        # Commit the updated XML.
        $sql = "UPDATE My_Table SET MY_CLOB_FIELD = :1"
        $updateCmd = New-Object Oracle.DataAccess.Client.OracleCommand ($sql, $conn)
    
        $param = New-Object Oracle.DataAccess.Client.OracleParameter (
            "xml", #Name
            [Oracle.DataAccess.Client.OracleDbType]::Clob, #Type
            $xmlDoc.OuterXml, #Data
            'Input' #Direction
        )
    
        $newParam = $updateCmd.Parameters.Add($param) 
        $result = $updateCmd.ExecuteNonQuery()
    }