Подключение к oracle из powershell

Обновлено: 06.07.2024

Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей конкретного узла.

Предварительные требования

Поддерживаемые версии Oracle:

Oracle Server 9 и более поздних версий
Клиент доступа к данным Oracle (ODAC) — программное обеспечение версии 11.2 и более поздней

Прежде чем можно будет подключиться к базе данных Oracle с помощью Power Query, необходимо установить клиентское программное обеспечение Oracle версии 8.1.7 или выше на компьютере. Чтобы установить 32-разрядное клиентское программное обеспечение Oracle, перейдите на страницу 32-разрядной версии Oracle Data Access Components (ODAC) с Oracle Developer Tools для Visual Studio (12.1.0.2.4). Чтобы установить 64-разрядную версию клиента Oracle, перейдите на страницу 64-разрядной версии ODAC 12c Release 4 (12.1.0.2.4) Xcopy для 64-разрядных версий Windows.

Для подключения к базе данных Oracle с помощью локального шлюза данных необходимо установить правильное клиентское программное обеспечение Oracle на компьютере, где выполняется шлюз. Используемое клиентское программное обеспечение Oracle зависит от версии сервера Oracle, но всегда будет соответствовать 64-разрядному шлюзу. Дополнительные сведения см. в статье Управление своим источником данных — Oracle.

Поддерживаемые возможности

Используя режим Import
DirectQuery

Расширенные параметры

Время ожидания команды в минутах
Инструкция SQL
Включить столбцы отношений
Навигация с помощью полной иерархии

Подключение базы данных Oracle из Power Query Desktop

Чтобы установить соединение, сделайте следующее:

Выберите параметр база данных Oracle в выбранном соединителе.

Укажите сервер Oracle для подключения к серверу. Если требуется идентификатор безопасности, укажите его с помощью формата ServerName/SID, где SID — уникальное имя базы данных. Если формат имя_сервера/SID не подходит, попробуйте использовать формат имя_сервера/имя_службы, где имя_службы — это псевдоним, используемый при подключении.

Если вы используете локальную базу данных или автономные подключения к базе данных, вам может потребоваться поместить имя сервера в кавычки, чтобы избежать ошибок подключения.

при подключении из Power BI Desktop выберите режим подключения к данным импорта или DirectQuery . В остальных шагах примера используется режим подключения с импортом данных. Дополнительные сведения о DirectQuery см. в статье Использование DirectQuery в Power BI Desktop.

Если вы подключаетесь к этой базе данных Oracle впервые, выберите тип проверки подлинности, который вы хотите использовать, а затем введите свои учетные данные. Дополнительные сведения о проверке подлинности см. в статье Проверка подлинности с помощью источника данных.

В навигаторе выберите необходимые данные, а затем нажмите кнопку загрузить , чтобы загрузить данные или преобразовать данные для преобразования данных.

Подключение базы данных Oracle из Power Query в сети

Чтобы установить соединение, сделайте следующее:

Выберите параметр база данных Oracle в области Выбор источников данных.

В появившемся диалоговом окне база данных Oracle укажите сервер Oracle для подключения к серверу. Если требуется идентификатор безопасности, укажите его с помощью формата ServerName/SID, где SID — уникальное имя базы данных. Если формат имя_сервера/SID не подходит, попробуйте использовать формат имя_сервера/имя_службы, где имя_службы — это псевдоним, используемый при подключении.

Выберите имя локального шлюза данных.

Необходимо выбрать локальный шлюз данных для этого соединителя, будь то база данных Oracle в локальной сети или на веб-сайте.

Если вы подключаетесь к этой базе данных Oracle в первый раз, выберите тип учетных данных для подключения в поле тип проверки подлинности. Если планируется использовать учетную запись, созданную в Oracle, а проверку подлинности Windows, выберите вариант Базовый.

Введите свои учетные данные.

В навигаторе выберите нужные данные, а затем выберите преобразовать данные для преобразования данных в Power Query редакторе.

Подключение с помощью дополнительных параметров

Power Query Desktop предоставляет набор дополнительных параметров, которые можно добавить в запрос при необходимости.

В следующей таблице перечислены все дополнительные параметры, которые можно задать в Power Query Desktop.

Дополнительный параметр	Описание
Время ожидания команды в минутах	Если подключение длится дольше 10 минут (время ожидания по умолчанию), можно ввести другое значение в минутах, чтобы соединение было открыто дольше. Этот параметр доступен только в Power Query Desktop.
Инструкция SQL	Дополнительные сведения см. в подразделе Импорт данных из базы данных с помощью собственного запроса к базе данных.
Включить столбцы отношений	Если флажок установлен, включает столбцы, которые могут иметь связи с другими таблицами. Если этот флажок снят, эти столбцы отображаться не будут.
Навигация с помощью полной иерархии	Если флажок установлен, навигатор отображает полную иерархию таблиц в базе данных, к которой выполняется подключение. Если флажок не установлен, навигатор отображает только те таблицы, столбцы и строки которых содержат данные.

Выбрав нужные дополнительные параметры, нажмите кнопку ОК в Power Query рабочем столе, чтобы подключиться к базе данных Oracle.

Устранение неполадок

Если синтаксис именования неверен или не настроен должным образом, в Oracle может возникнуть любая из нескольких ошибок.

ORA-12154: TNS: не удалось разрешить указанный идентификатор подключения.
ORA-12514: TNS: прослушиватель в настоящее время не знает о службе, запрошенной в дескрипторе подключения.
ORA-12541: TNS: нет прослушивателя.
ORA-12170: TNS: истекло время ожидания подключения.
ORA-12504: TNS: прослушивателю не предоставлены SERVICE_NAME в CONNECT_DATA.

Эти ошибки могут произойти, если клиент Oracle не установлен или неправильно настроен. Если он установлен, убедитесь, что файл tnsnames. ORA настроен правильно и вы используете правильный net_service_name. кроме того, необходимо убедиться, что net_service_name между компьютером, на котором используется Power BI Desktop, и компьютером, на котором выполняется шлюз. Дополнительные сведения см. в разделе Необходимые условия.

Кроме того, могут возникнуть проблемы совместимости между версией сервера Oracle и версией клиента доступа к данным Oracle. Как правило, необходимо, чтобы эти версии совпадали, так как некоторые сочетания несовместимы. Например, ODAC 12. x не поддерживает Oracle Server версии 9.

Скачайте Power BI Desktop из Центра загрузки вместо магазина Microsoft Store.

Если вы используете Сервер отчетов Power BI, см. сведения о типе соединения Oracle.

Что такое свойства действий пользовательского интерфейса в UiPath

У меня проблемы с подключением к базе данных Oracle из Powershell с помощью Oracle.ManagedDataAccess.dll .

Я следил за этим руководством по Technet и получил такой код:

К сожалению, я получаю такую ошибку:

Я проверил, что строка подключения верна (работает для <?php tnsping )
Имя пользователя и пароль также верны
Я заменил все, что нельзя раскрывать, на XXXX
Версия базы данных: Oracle Database 12c выпуска 12.1.0.1.0
Ошибка одинакова для 32-разрядных и 64-разрядных экземпляров Powershell.
Перед первым запуском Oracle.ManagedDataAccess.dll не загружается, после запуска он остается загруженным, пока я не закрою эту оболочку

К сожалению, я не Oracle и не эксперт Powershell (я предпочитаю MySQL и Python), поэтому я был бы очень признателен за любые идеи и / или идеи, которые могут у вас возникнуть.

Помогает ли этот ответ stackoverflow? Я не верю, что это ошибка вашего кода; возможно, окружающая среда или предварительное условие.
Какая версия вашей базы данных Oracle?
1 @WernfriedDomscheit Я добавил в список - 12c Release 12.1.0.1.0 @ gms0ulman Упомянутое gacutil похоже, не установлен в моей системе - я исследую.
Ошибка предполагает, что проблема в сборке. На этом этапе даже нельзя сомневаться в строке подключения. Есть ли уже загруженные версии, которые могут конфликтовать? Вы запускаете это из 32-битной или 64-битной оболочки? Вы пробовали другой?
@Matt Я только что попробовал 32-битную и 64-битную версии - ошибки совпадают. Как определить, загружены ли другие версии? Я закрыл все экземпляры PowerShell и снова открыл их - такое же поведение.

Я не уверен, что это технически решение - я бы классифицировал его как обходной путь, но у меня это сработало.

Вот схема решения, которое сработало для меня:

что такое источник данных?
Если я правильно помню, это имя, которое вы настроили в своем TNSNAMES.ORA

Используйте синтаксис server: port / service.

У меня была точно такая же проблема, и я переключился с ODAC версии 12.2.0.1.0 на версию 11.2.0.3, и она работала как прелесть для открытия соединения и чтения данных. Надеюсь это поможет. Спасибо SK

23.03.2020

PowerShell, SQL Server

Один комментарий

В этой статье мы рассмотрим все рабочие способы подключения к SQL Server и выполнения SQL запросов из PowerShell. Обычно такая задача стоит перед системными администраторами, которые занимаются написанием скриптов и автоматизацией на PowerShell.

Есть много разных способов работы с SQL Server через PowerShell, и глядя на статьи в интернете в них легко запутаться, потому что в разных статьях описаны разные способы, и даже у опытного администратора может возникнуть вопросы.

T-SQL запросы в PowerShell через System.Data.OleDb

Пример PowerShell скрипта с использованием System.Data.OleDb. Выполним SELECT запрос к таблице в базе данных MS SQL:

$dataSource = “server\instance”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString

$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

Пример PowerShell скрипта для выполнения INSERT/UPDATE/DELETE запроса к базе MSSQL:

$dataSource = “server\instance”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

Переменная $rowsAffected содержит в себе количество добавленных или измененных строк. Чтобы выполнить update или delete запрос, нужно просто изменить строку SQL запроса в переменной $sql.

SQL запрос в PowerShell к MSSQL через System.Data.SqlClient

Пример INSERT/DELETE/UPDATE запроса:

Заметка. Обратите внимание, что код с классами SqlClient очень похож на код с OleDB. У этих классов схожий принцип работы:

Создается объект соединения с MSSQL сервером;
Создается объект с SQL запросом, и ему присваивается объект соединения;
Затем в случае выполнения SELECT запроса создается объект адаптера и в контексте этого адаптера выполняется запрос;
В случае выполнения INSERT/UPDATE/DELETE запроса объект с запросом (уже содержащий в себе объект соединения) выполняет метод ExecuteNonQuery() .

SQL запрос в PowerShell через модуль SQL Server Management Studio

Для использования классов Microsoft.SqlServer.Smo (SMO), в системе должна быть установлена SQL Server Management Studio.

Загружаем модуль SMO и создаём новый объект сервера, затем выполняем SELECT запрос:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "devsrv\devsrv"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) $nbsp;echo $res
>

Для insert/update/delete запрос выполняем ExecuteNonQuery:

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('Test555')")

Заметка. SMO библиотеки также можно установить через пакетный менеджер NuGet.

add-type –Path "C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll"

После этого классы SMO станут доступны для использования.

Командлет Invoke-Sqlcmd из модуля SQLServer для PowerShell

Для работы с командлетом Invoke-Sqlcmd нужно установить модуль SqlServer для PowerShell. Запустите PowerShell с правами администратора и введите

Install-Module -Name SqlServer

(Несколько раз примите уведомления инсталлятора, нажав Y и enter).

После установки можно проверить что модуль корректно установился, набрав:

Get-Module SqlServer -ListAvailable

Командлет Invoke-Sqlcmd более простой и интуитивный в использовании чем другие способы подключения к SQL Server. Invoke-Sqlcmd использует один и тот же синтаксис для SELECT и INSERT/UPDATE/DELETE запросов.

Пример SELECT запроса:

Invoke-Sqlcmd -ServerInstance "server\instance " -Query "sp_who"

Пример INSERT запроса:

Invoke-Sqlcmd -ServerInstance "server\instance" -Database "test" -Query "insert into test_table (test_col) Values (‘Test123’)"

В отличие от других способов, запрос в Invoke-Sqlcmd всегда задается в параметре –Query .

Какой вариант подключения к SQL выбрать?

Выбирать между oledb/smo/sqlclient/invoke-sqlcmd нужно с учетом задачи которая перед вами стоит, и в зависимости от окружения, где планируется выполнять скрипт.

Если вы собираетесь распространять скрипт (например, ваш скрипт локально собирает данные для мониторинга) на множество серверов, то варианты c использованием SMO и плагина SqlServer (invoke-sqlcmd) стоит рассматривать в последнюю очередь, так как для отработки скрипта нужно будет устанавливать дополнительные пакеты в систему, чего хотелось бы избежать, при большом количестве серверов.

Подключение к базе данных из скрипта PowerShell и выполнение запроса к базе можно сделать несколькими способами. Приведу 2 из них, которые на мой взгляд самые простые.

1-й способ. Подключение к базе данных MS SQL Server с помощью класса SqlConnection

Подключение к MS SQL Server с учетными данными пользователя, вошедшего в Windows, т.е. так называемая Windows-авторизация, выполняется так:

Подключение к MS SQL Server со стандартной авторизацией, т.е. по логину и паролю в базе данных:

Выполнить sql-запрос и обработать результаты теперь можно, например, таким способом:

Выполнить sql-запрос на изменение/добавление/удаление данных можно следующим образом:

В последней строке "Out-Null" используется для подавления вывода результатов выполнения запроса. Дело в том, что для запросов типа INSERT, UPDATE, DELETE команда ExecuteNonQuery возвращает количество обработанных записей, а в остальных случаях возвращает "-1". Поэтому, если происходит массовое изменение данных, то вывод лучше подавить.

Закрыть подключение к базе можно командой:

Как видите, всё довольно просто.

2-й способ. Подключение к базе данных MS SQL Server с помощью библиотеки ADO

Библиотека ADO (Microsoft ActiveX Data Object) позволяет выполнять подключение не только к базам MS SQL Server, но и ко множеству других видов баз данных. Она очень удобна в использовании и универсальна. Написав программу для работы с одним типом базы данных, вам не придется переделывать всю программу для другой базы, а всего-лишь сделать другую строку подключения к базе. Приведу несколько примеров работы с базой данных MS SQL Server в PowerShell.

Подключение к базе данных:

Выполнение sql-запроса и обработка результатов:

Выполнение sql-запроса на изменение/добавление данных:

Думаю, приведенных 2-х способов вполне достаточно, чтобы начать работать с БД SQL Server из скриптов PowerShell, читать данные, менять их и добавлять. В следующей статье будет рассмотрено подключение к базе данных Access.

Читайте также: