Tag Archives: Oracle

Oracle Database Queries in PowerShell, Script Examples

Below are some code examples to help get your data out of an Oracle database into a PowerShell object quickly! The logic to manipulate data is the same regardless of vendor: load any required libraries, define the connection string, setup the connection object, use that connection object for subsequent queries and finally close the connection.

Oracle Data Access Components (ODAC) allows you to run an Oracle complied binary within the .NET framework including PowerShell. When you are working with Oracle queries in PowerShell, it is required to download and install the Oracle Data Access Components prior to accessing a database. Download the components here, bit.ly/1t2W790. Select the appropriate architecture (x86/x86-64) and ensure the correct PowerShell program architecture is being executed with the corresponding Oracle component’s architecture. Failure to do so will lead to binary related errors while loading the assembly in PowerShell.

Load the System.Data.OracleClient Assembly:

### try to load assembly, fail otherwise ###
$Assembly = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

if ( $Assembly ) {
    Write-Host "System.Data.OracleClient Loaded!"
}
else {
    Write-Host "System.Data.OracleClient could not be loaded! Exiting..."
    Exit 1
}

Setup the connection string and open a database connection:

### connection string ###
$OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.domain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracledb.domain.com)));uid=user;pwd=password;"

### open up oracle connection to database ###
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
$OracleConnection.Open()

Stored procedure example with parameters example:

try {

    ### create object ###
    $GetHostIDCommand = New-Object System.Data.OracleClient.OracleCommand;
    $GetHostIDCommand.Connection = $OracleConnection
    $GetHostIDCommand.CommandText = "GET_HOST_ID"
    $GetHostIDCommand.CommandType = [System.Data.CommandType]::StoredProcedure

    ### add storeprocedure paramaters ###
    $GetHostIDCommand.Parameters.Add("p_hostname", [System.Data.OracleClient.OracleType]::VarChar).Value = $Computer.ToUpper();
    $GetHostIDCommand.Parameters.Add("p_host_id", [System.Data.OracleClient.OracleType]::Number).Direction = [System.Data.ParameterDirection]::Output

    ### execute storedprocedure ###
    $GetHostIDCommand.ExecuteNonQuery() | Out-Null

    ### get the data from output defined by the storedprocedure ###
    $HostID = $GetHostIDCommand.Parameters["p_host_id"].Value

    ### dispose of object ###
    $GetHostIDCommand.Dispose()

}
catch { Write-Host "$Computer : Failed to GET_HOST_ID" }

SQL select query text example:

try {

    ### sql query command ###
    $OracleSQLQuery = "SELECT * FROM HOSTS"

    ### create object ###
    $SelectCommand = New-Object System.Data.OracleClient.OracleCommand;
    $SelectCommand.Connection = $OracleConnection
    $SelectCommand.CommandText = $OracleSQLQuery
    $SelectCommand.CommandType = [System.Data.CommandType]::Text

    ### create datatable and load results into datatable ###
    $SelectDataTable = New-Object System.Data.DataTable
    $SelectDataTable.Load($SelectCommand.ExecuteReader())

}
catch {

    Write-Host "Error while retrieving data!"

}

SQL update command text example:
(same would be for an insert command with no expected output)

try {

    ### sql update command query ###
    $HostUpdateHostSQL = "UPDATE HOSTS h SET h.PHYSICAL = '$(Convert-Boolean($HW_PHYSICAL))' WHERE h.ID = '$HostID'"

    $HostUpdateCommand = New-Object System.Data.OracleClient.OracleCommand;
    $HostUpdateCommand.Connection = $OracleConnection
    $HostUpdateCommand.CommandType = [System.Data.CommandType]::Text
    $HostUpdateCommand.CommandText = $HostUpdateHostSQL

    ### execute update command query ###
    $HostUpdateCommand.ExecuteNonQuery() | Out-Null

}
catch {

    ### output exception to screen ###
    Write-Host "$Hostname : ERROR! $HostUpdateHostSQL"
    Write-Host "Dump : $($_.Exception.ToString())"

}
### dispose regardless ###
finally { $HostUpdateCommand.Dispose() }
Advertisements
Tagged , , , , ,

Installing XAMPP + Xdebug on Oracle Linux 6.4 x86

This guide will show you how to install XAMPP with Xdebug (compiled) on an RedHat/Oracle Linux 6.4 x86 installation in a few simple steps.

Login in as root or su over to root to start with. Let’s start by making sure we have all the development tools that are necessary to compile the Xdebug library for XAMPP in addition bringing everything the system most up-to-date.

yum update -y
yum groupinstall "Development Tools" -y

Grab the download links from ApacheFriends for XAMPP and use wget to get XAMPP and the Development Packages.

cd ~
wget http://www.apachefriends.org/download.php?xampp-linux-1.8.1.tar.gz
wget http://www.apachefriends.org/download.php?xampp-linux-devel-1.8.1.tar.gz

Extract XAMPP and move to it’s permanent location.

tar -xzvf xampp-linux-1.8.1.tar.gz
mv lampp/ /opt

Extract XAMPP Development libraries and copy the include directory into the base of the lampp directory for use with the compiler.

tar -xzvf xampp-linux-devel-1.8.1.tar.gz
cp -r lampp/include /opt/lampp/.

Using PECL install Xdebug which will invoke the process to compile it the extension.

/opt/lampp/bin/pecl update-channels
/opt/lampp/bin/pecl install Xdebug

Edit the php.ini file to add the newly compiled Xdebug.

vi /opt/lampp/etc/php.ini

Add in the following lines at the end of the php.ini configration file.

zend_extension = "/opt/lampp/lib/php/extensions/no-debug-non-zts-20100525/xdebug.so"
xdebug.remote_enable = 1
xdebug.remote_handler = "dbgp"
xdebug.remote_host = "localhost"
xdebug.remote_port = 9000

Start/Restart XAMPP. Browse to the http://host/xampp/phpinfo.php page to ensure Xdebug was loaded properly.

Tagged , , , , , , , , ,