Подключение к oracle из powershell
Microsoft Scripting Guy, Ed Wilson, is here. It is early in the morning in Frankfurt, Germany, and the Scripting Wife and I just returned from Prague where we had a wonderful time visiting with one of the new Windows PowerShell MVPs David Moravec. David is a super nice person, and he took the time to show us around his lovely city. I must have taken nearly 500 pictures—won’t know until I upload them to my portable USB hard drive. Anyway, we feel really blessed to have had the opportunity to spend the day with such an interesting person.
And this brings our three-week tour to a close (in just a few hours). We have some time in the Frankfurt airport before our flights. This is a good thing because the Frankfurt airport has lots of things to do and to see. It is like a shopping mall with airplanes attached to it. Because I knew that things would be a bit confused today, I asked my good friend and PowerShell GURU Klaus Schulte to supply a guest post for today. With no further ado, here is Klaus.
Подключение в автономную базу данных Oracle
В настоящее время можно подключиться к автономной базе данных Oracle из Excel, Power BI Desktop, служба Power BI и Power Apps с помощью процедур, описанных в этом разделе.
Чтобы подключиться к автономной базе данных Oracle, вам потребуются следующие учетные записи и приложения:
Настройка учетных данных Oracle ADB
На компьютере Windows перейдите в папку, в которой вы скачали учетные данные Oracle ADB из скачивания учетных данных клиента.
Распакуйте учетные данные в папку. В этом примере учетные данные извлекаются в каталог c:\data\wallet\wallet_contosomart.
Файл tnsnames.ora используется для подключения к автономным базам данных Oracle.
Откройте sqlnet.ora в редакторе, например Блокнот.
В разделе WALLET_LOCATION измените путь к папке кошелька в разделе "Каталог". В данном примере:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="c:\data\wallet\Wallet_ContosoMart")))
Сохраните и закройте файл sqlnet.ora.
Откройте файл tnsnames.ora в папке wallets. Файл содержит список имен чистых служб ADB, к которым можно подключиться. В этом примере имена : contosomart_high, contosomart_low и contosomart_medium. Имена чистых служб ADB будут отличаться.
5 Answers 5
Im not sure if this is technically a solution - I'd classify it more as a workaround, but it worked for me.
Here's an outline of the solution that worked for me:
I am pretty sure this Microsoft Oracle Client is a dead duck and even if it isn't, I would always favor the native Oracle client dlls for this.
@Allen Please provide a source for this claim and feel free to suggest an edit with a Caveat. If there is a better solution, please let me know - in this case I couldn't find a way solve my problem under the given constraints with the native DLLs. Should you find one, please leave it as an answer :-)
This Microsoft client is clearly no longer supported and should not be used IMHO. A search reveals: . "This types in System.Data.OracleClient are deprecated and will be removed in a future version of the .NET Framework. For more information, see Oracle and ODP.NET. " Furthermore, the Microsoft ODBC driver is also deprecated, if you have the misfortune to need to use it, and it is my understanding that all the Microsoft drivers for Oracle are somehow joined at the hip to the System.Data.OracleClient - also, these are not managed code, I believe.
Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей для конкретного узла.
How to use Windows PowerShell to query an Oracle database
By living in a networked world, with Internet access everywhere, information retrieval is often a snap. You don’t need to go to the living room and search through encyclopedias or dictionaries any more to find information—clicks on the touchscreen of your smartphone may do the job in seconds! Right, everything has changed, but behind the nice-colored icons, most of the information retrieval is still relying on some old-fashioned techniques—called databases. At the company I work for we definitely know that at least this will not change in the next couple of years.
In fact, it is crucial to get at some database information, even in scripts, to retrieve information regarding the current workflow, the status of individual tasks, and error messages indicating a flaw in the workflow.
But, nowadays, as most companies are migrating to 64-bit systems, another level of complexity has been added to the provider choice: You have to pay attention to the combination of installed Oracle clients and data providers on the machines your company is running because the Microsoft and the Oracle provider rely and cooperate with an installed Oracle client version on each workstation and this can be a 32-bit or a 64-bit client. You can’t easily provide an application that works in both worlds … you have to know which operating system you are running!
Some non-Microsoft products don’t need the Oracle client installed and can communicate directly with Oracle.
Note You can walk through this article using the unmanaged Oracle.DataAccess.dll without any changes (except for the name of the dll). And if you use the Microsoft System.Data.OracleClient you will also be able to follow me with very little effort! Even if you use another RDBMS, chances are that you can easily build an analogue scenario for your database.
Also, be aware that the standard Oracle Client may be a large piece of software! Depending on the functionality needed, you may have to dedicate over 500 MB on each workstation. The Instant Client is an improvement, which reduces the size of the client to about 100 MB as there are only a couple of binaries (DLL) delivered in this package, which can be copied to each workstation without the need of any further Setup. However, it only contains the most basic functionality where additional add-ons might be needed.
A major problem is that depending on your software mix, it might be necessary to install the 32-bit and the 64-bit versions of the client, which is a challenge on its own, if you have to make both versions available on one machine.
The good news is:
The bad news is:
These issues should be gone with the production version that should be available next year.
Now we want to execute some SQL Server commands against database.
Note If you don’t have an Oracle database handy, you can download the free XE version (similar to SQL Server Express) and use the example schema hr, which is installed as part of the Setup procedure of the XE database. Download the Oracle 11g R2 Database Express Edition. (You have to create an Oracle account for free, if you haven’t already got one.)
Подключение в базу данных Oracle из Power Query Desktop
Чтобы установить соединение, сделайте следующее:
Выберите параметр базы данных Oracle в выборе соединителя.
Укажите Oracle Server для подключения к серверу. Если идентификатор безопасности является обязательным, укажите его с помощью формата ServerName/SID, где sid — уникальное имя базы данных. Если формат имя_сервера/SID не подходит, попробуйте использовать формат имя_сервера/имя_службы, где имя_службы — это псевдоним, используемый при подключении.
Если вы используете локальную базу данных или автономные подключения к базе данных, вам может потребоваться поместить имя сервера в кавычки, чтобы избежать ошибок подключения.
Если вы подключаетесь из Power BI Desktop, выберите режим подключения к данным Импорта или DirectQuery. В остальных шагах примера используется режим подключения с импортом данных. Дополнительные сведения о DirectQuery см. в статье Использование DirectQuery в Power BI Desktop.
Если вы впервые подключаетесь к этой базе данных Oracle, выберите нужный тип проверки подлинности и введите учетные данные. Дополнительные сведения о проверке подлинности см. в описании проверки подлинности с помощью источника данных.
В навигаторе выберите необходимые данные, а затем выберите "Загрузить ", чтобы загрузить данные или преобразовать данные для преобразования данных.
Encrypting Connection Strings
Ideally everything would use Windows authentication and you wouldn’t need to store password information. The reality is this simple isn’t the case especially with Oracle databases. Unfortunately there aren’t any native encryption cmdlets in Powershell (I’d love to see a cmdlet that would use certificates in order to avoid pass phrases), there are however a very nice and set of Powershell encryption functions created by Steven Hystad called Library-StringCrytpo.
To use the encryption functions download the Powershell script and source the library, then call the Write-EncryptedString function passing our connection string we want to encrypt with a passphrase. To decrypt the connection string call the Read-EncryptedString function with the encrypted string and passphrase.
The encrypt functions work well, but I like to do is then take the encrypted string and store it in a SQL Server table that is locked down. To do we’ll need to first create a table in SQL Server database as follows:
CREATE TABLE [dbo].[server_lku](
[server_name] [ varchar ]( 255 ) NOT NULL ,
[server_type] [ varchar ]( 25 ) NOT NULL ,
[connection_string] [ varchar ]( 2000 ) NOT NULL ,
[is_encrypted] [bit] NOT NULL ,
CONSTRAINT [PK_server_lku] PRIMARY KEY CLUSTERED
(
[server_name] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[server_lku] ADD CONSTRAINT [DF_server_lku_is_encrypted] DEFAULT (( 0 )) FOR [is_encrypted]
GO
Note: I’ve created the server_lku table on a server named win2k8r2sql2k8 in the database dbautility
In order to select and insert data into the server_lku table we’ll use the Get-SqlData and Set-SqlData functions from the LibrarySqlData script which I previously blogged about here.
Source LibrarySqlData , and insert the encrypted string along with few other pieces of information into our SQL table using the Set-SqlData function. To retrieve the encrypted connect string use the Get-SqlData function and then call the Read-EncryptedString function.
Just as in our previous example all rows from the DEPARTMENTS table in the HR schema are returned:
Querying Oracle databases from Powershell requires a little extra setup, but once you put these pieces in place you’re ready to start automating Oracle from Powershell!
Устранение неполадок
При неправильной или неправильной настройке синтаксиса именования может возникнуть любая из нескольких ошибок из 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 Data Access. Как правило, необходимо, чтобы эти версии совпадали, так как некоторые сочетания несовместимы. Например, ODAC 12.x не поддерживает Oracle Server версии 9.
Скачайте Power BI Desktop из Центра загрузки вместо магазина Microsoft Store.
Если вы используете Сервер отчетов Power BI, см. сведения о типе соединения Oracle.
У меня проблемы с подключением к базе данных Oracle из Powershell с помощью Oracle.ManagedDataAccess.dll .
Я следил за этим руководством по Technet и получил следующий код:
К сожалению, я получаю такую ошибку:
- Я проверил, что строка подключения верна (работает для tnsping )
- Имя пользователя и пароль также верны
- Я заменил все, что не следует раскрывать, на XXXX
- Версия базы данных: Oracle Database 12c Release 12.1.0.1.0
- Ошибка одинакова для 32-разрядных и 64-разрядных экземпляров Powershell.
- Перед первым запуском Oracle.ManagedDataAccess.dll он не загружается, после запуска он остается загруженным, пока я не закрою эту оболочку
К сожалению, я не Oracle и не эксперт Powershell (я предпочитаю MySQL и Python), поэтому я был бы очень признателен за любые идеи и / или идеи, которые могут у вас возникнуть.
Помогает ли этот ответ stackoverflow? Я не верю, что это ошибка вашего кода; возможно, окружающая среда или предварительное условие.
@WernfriedDomscheit Я добавил его в список - 12c Release 12.1.0.1.0 @ gms0ulman Упомянутое gacutil , похоже, не установлено в моей системе - я исследую.
Ошибка предполагает, что проблема в сборке. На этом этапе даже нельзя сомневаться в строке подключения. Есть ли уже загруженные версии, которые могут противоречить друг другу? Вы запускаете это из 32-битной или 64-битной оболочки? Вы пробовали другой?
@Matt Я только что попробовал 32-битную и 64-битную версии - ошибки совпадают. Как определить, загружены ли другие версии? Я закрыл все экземпляры PowerShell и снова открыл их - такое же поведение.
Я не уверен, что это технически решение - я бы классифицировал его больше как обходной путь, но у меня это сработало.
Вот схема решения, которое сработало для меня:
Я почти уверен, что этот Microsoft Oracle Client - мертвая утка, и даже если это не так, я всегда предпочел бы для этого собственные клиентские библиотеки Oracle.
@Allen. Укажите источник этой претензии и не стесняйтесь предлагать редактирование с предупреждением. Если есть лучшее решение, пожалуйста, дайте мне знать - в этом случае я не смог найти способ решить свою проблему при данных ограничениях с помощью собственных библиотек DLL. Если вы его найдете, оставьте его как ответ :-)
In part one we installed and configured the Oracle client software, in this post we will query an Oracle database from Powershell. In addition we’ll look at one way to handle storing sensitive password information.
Скачивание и установка среды выполнения компонентов доступа к данным Oracle
Если вы используете 64-разрядную версию Power BI Desktop, в 64-разрядной версии OUI ODAC выберите последнюю версию установщика ODAC, которая в настоящее время является 64-разрядной версией ODAC 19.3.1. Если вы используете 32-разрядную версию Power BI Desktop, перейдите на страницу скачивания для 32-разрядной версии из ссылки на странице Oracle. Рекомендуется использовать 64-разрядную версию Power BI Desktop. В примерах в этой статье используется 64-разрядная версия.
Вам будет предложено войти в учетную запись Oracle. Эта учетная запись может отличаться от учетной записи Oracle Cloud, поэтому обязательно введите правильное имя пользователя и пароль.
Затем zip-файл OUI OUI Oracle ODAC загружается в ваше Windows расположение загрузки по умолчанию.
Из расположения загрузки по умолчанию извлеките файлы из ZIP-файла и поместите их в удобную папку по своему усмотрению.
Откройте командную строку Windows (cmd.exe) в режиме администратора.
В командной строке перейдите в папку, в которой были извлечены файлы OUI ODAC.
В разделе "Укажите пользователя Oracle Home User" выберите "Использовать Windows встроенную учетную запись". Выберите Далее.
Затем установщик выполняет некоторые проверки готовности, чтобы убедиться, что система соответствует минимальным требованиям к установке и конфигурации. После завершения этой проверки нажмите кнопку "Далее".
Затем установщик представляет сводку действий, которые он собирается предпринять. Чтобы продолжить, щелкните Установить.
После завершения установки всех компонентов драйвера установщика нажмите кнопку "Закрыть".
Введите следующие команды:
OraProvCfg /action:gac /providerpath:"Oracle.DataAccess.dll"
OraProvCfg /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"Oracle.DataAccess.dll"
Скачивание учетных данных клиента
Первым шагом в настройке подключения к автономной базе данных Oracle является скачивание учетных данных клиента.
Чтобы скачать учетные данные клиента, выполните приведенные далее действия.
На странице сведений об автономной базе данных Oracle выберите "Подключение к базе данных".
Поддерживаемые возможности
- Используя режим Import
- DirectQuery
- Расширенные параметры
- Время ожидания команды в минутах
- Инструкция SQL
- Включить столбцы отношений
- Навигация с помощью полной иерархии
Querying and Oracle Database
To query an Oracle database we’ll use a function called Get-OLEDBData. The code listed below and is also available on PoshCode:
This will return all rows from the DEPARTMENTS table in HR schema:
As long as your Oracle client software is installed and configured correctly and you have a valid connection string, specifying a database user with sufficient rights the query works. One issue immediately apparent is the sensitive password information. This especially true if you intend to use this technique for automated batch jobs. To address the password issue we’ll need to encrypt the connection string and the store the password somewhere. Let’s a look at one solution…
Подключение в базу данных Oracle из Power Query Online
Чтобы установить соединение, сделайте следующее:
Выберите параметр базы данных Oracle в выборе источников данных.
В появившемся диалоговом окне базы данных Oracle укажите oracle Server для подключения к серверу. Если идентификатор безопасности является обязательным, укажите его с помощью формата ServerName/SID, где sid — уникальное имя базы данных. Если формат имя_сервера/SID не подходит, попробуйте использовать формат имя_сервера/имя_службы, где имя_службы — это псевдоним, используемый при подключении.
Выберите имя локального шлюза данных.
Для этого соединителя необходимо выбрать локальный шлюз данных независимо от того, находится ли база данных Oracle в локальной сети или на веб-сайте.
Если вы впервые подключаетесь к этой базе данных Oracle, выберите тип учетных данных для подключения в типе проверки подлинности. Если планируется использовать учетную запись, созданную в Oracle, а проверку подлинности Windows, выберите вариант Базовый.
Введите свои учетные данные.
В навигаторе выберите необходимые данные, а затем выберите "Преобразовать данные", чтобы преобразовать данные в Редактор Power Query.
Настройка переменных среды
Найдите переменные среды в параметре поиска Windows меню и выберите "Изменить системные переменные среды".
В области "Свойства системы" выберите "Переменные среды".
В разделе "Переменные среды" в разделе "Системные переменные" выберите "Создать".
Щелкните ОК.
В разделе "Переменные среды" нажмите кнопку "ОК".
В разделе "Свойства системы" нажмите кнопку "ОК".
Подключение с помощью дополнительных параметров
Power Query Desktop и Power Query Online предоставляют набор дополнительных параметров, которые можно добавить в запрос при необходимости.
В следующей таблице перечислены все дополнительные параметры, которые можно задать в Power Query Desktop и Power Query Online.
Дополнительный параметр Описание Время ожидания команды в минутах Если подключение длится более 10 минут (время ожидания по умолчанию), можно ввести другое значение в минутах, чтобы установить подключение дольше. Этот параметр доступен только в Power Query Desktop. Инструкция SQL Дополнительные сведения см. в описании импорта данных из базы данных с помощью собственного запроса к базе данных. Включить столбцы отношений Если этот флажок установлен, включает столбцы, которые могут иметь связи с другими таблицами. Если это поле снято, эти столбцы не будут отображаться. Навигация с помощью полной иерархии Если этот флажок установлен, навигатор отображает полную иерархию таблиц в базе данных, к которому вы подключаетесь. Если этот флажок снят, навигатор отображает только таблицы, столбцы и строки которых содержат данные. Выбрав необходимые дополнительные параметры, нажмите кнопку "ОК" в Power Query Desktop для подключения к базе данных Oracle.
Начальный тест подключения
Откройте Power BI Desktop.
Выберите Получить данные.
В получите данные выберите databaseOracle database >.
Если вы впервые входите на этот сервер из Power BI Desktop, вам будет предложено ввести свои учетные данные. Выберите "База данных", а затем введите имя пользователя и пароль для базы данных Oracle. Указанные здесь учетные данные — это имя пользователя и пароль для конкретной автономной базы данных Oracle, к которой вы хотите подключиться. В этом примере используются имя пользователя и пароль начального администратора базы данных. В этом случае выберите Подключиться.
На этом этапе откроется навигатор и отображаются данные подключения.
Вы также можете столкнуться с одной из нескольких ошибок, так как конфигурация не настроена должным образом. Эти ошибки рассматриваются в разделе "Устранение неполадок".
Одна ошибка, которая может возникнуть в этом первоначальном тесте, происходит в навигаторе, где база данных, как представляется, подключена, но не содержит данных. Вместо этого oracle: ORA-28759: ошибка при открытии файла отображается вместо данных.
Если эта ошибка возникает, убедитесь, что путь к папке кошелька, предоставленный в sqlnet.ora, является полным и правильным путем к папке кошелька.
Import the Oracle.ManageDataAccess.dll
First, import this library. If you unzipped the dll to “C:\Oracle” you can start with:
Add-Type -Path “C:\Oracle\Oracle.ManagedDataAccess.dll”
The next step is to create a connection to the database. Here you need to supply credentials to access the database and schema as well as a host and service name.
Unlock the hr account and supply the password hr by using this technique: Open a console window, type sqlplus / as sysdba to connect to the locally running XE database, and if you are connected, type: alter user hr identified by hr account unlock;
You should be able to connect by the connection string: “User Source=localhost/XE”
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection(“User Source=localhost/XE”)
Next, you check if the connection can be opened!
I'm having trouble connecting to an Oracle database from Powershell using the Oracle.ManagedDataAccess.dll .
I followed this tutorial on Technet and ended up with this code:
Unfortunately I'm getting this error:
- I have checked, that the connection string is correct (it works for tnsping )
- The Username and password are correct as well
- I replaced anything that shouldn't be disclosed with XXXX
- The database version is: Oracle Database 12c Release 12.1.0.1.0
- The error is the same for the 32-Bit and 64-Bit Powershell instances
- Before the first run the Oracle.ManagedDataAccess.dll isn't loaded, after the run it stays loaded until I close that shell
Unfortunately I'm neither an Oracle nor a Powershell Expert (I prefer MySQL and Python), therefore I would really appreciate any ideas and/or insights you might have.
Does this stackoverflow answer help? I don't believe it's an error with your code; probably the environment or a pre-requisite.
@WernfriedDomscheit I added it to the list - 12c Release 12.1.0.1.0 @gms0ulman The mentioned gacutil doesn't seem to be installed on my system - I will investigate.
Error suggests it is a problem with the assembly. The connection string cannot even be questioned at this point. Are there any versions of this already loaded that might be in conflict? Are you running this from a32bit or 64bit shell? Have you tried the other?
@Matt I just tried the 32-Bit and 64-Bit Versions - the error apperas to be the same. How do I determine if other versions are loaded? I closed all powershell instances and reopened them - same behavior.
Настройка шлюза
В служба Power BI щелкните значок шестеренки в правом верхнем углу, а затем выберите "Управление шлюзами".
В разделе "Добавление источника данных" выберите "Добавить источники данных", чтобы использовать шлюз.
В поле "Имя источника данных" введите имя, которое вы хотите использовать в качестве параметра источника данных.
В поле "Тип источника данных" выберите Oracle.
В поле "Сервер" введите имя сервера Автономной базы данных Oracle.
В разделе "Метод проверки подлинности" выберите "Базовый".
Введите имя пользователя и пароль для автономной базы данных Oracle. В этом примере используются имя пользователя и пароль администратора базы данных по умолчанию.
Выберите Добавить.
Предварительные требования
Поддерживаемые версии 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.
Читайте также: