Connecting to an oracle Database and Mysql

I am having a hard time trying to connect to an oracle database to retrieve some data in a table and then export to a file. Can anybody provide me a basic script that shows how to connect to an oracle database providing the hostname,DB name and username and password and the Table to select from and then export the result to a file.

Is it the same way to connect to an oracle DB and mysql?

Thanks

 

Parents
No Data
Reply
  • I totally don't understand Oracle, but I installed the Express version and was able to connect using the following two methods. The first, I was able to connect to localhost using the .NET client

    $connectionString = "Data Source=localhost;User Id=test;Password=test;Integrated Security=no;"
    $queryString = "SELECT * FROM ALL_USERS"

    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
    $connection = New-Object System.Data.OracleClient.OracleConnection $connectionString
    $command = New-Object System.Data.OracleClient.OracleCommand $queryString, $connection
    $connection.Open()
    $datatable = New-Object System.Data.DataTable
    $datatable.load($command.ExecuteReader())
    $connection.Close()
    $datatable | Export-Csv -NoTypeInformation C:\temp\table.txt

    When attempting to connect from a remote machine, however, I ran into issues. I don't know enough about Oracle to know if it was just because the .NET client has been deprecated, but I ended up downloading the odp.net client, installing it to the GAC, then I used this technet post as a reference.

    $connectionString = "Data Source=remoteoracleserver;User Id=test;Password=test;"
    $queryString = "SELECT * FROM ALL_USERS"

    [System.Reflection.Assembly]::LoadWithPartialName("Oracle.ManagedDataAccess") | Out-Null
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection $connectionString
    $command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand $queryString, $connection
    $connection.Open()
    $datatable = New-Object System.Data.DataTable
    $datatable.load($command.ExecuteReader())
    $connection.Close()
    $datatable | Export-Csv -NoTypeInformation C:\temp\table.txt

    As for databases, check out the above referenced technet post. They mention connecting to database endpoints by changing the Data source (ex. localhost/XE)

Children
No Data