Question
Arbetsförmedlingen
SE
Last activity: 30 Jul 2025 9:05 EDT
Connecting to an external Oracle database and
Hi,
I'm working on time sensitive delivery, which need to connect to an Oracle database and perform multiple read and write operations. However, I'm struggling to connect to the database. I tried AI buddy and previous posts and documentation, etc but they didn't help. I still wonder what should be the connection string. Also if there is any documentation or knowledge article that could clearly describe how to connect, how to pass query with dynamic parameters as inputs, etc would be greatly appreciated, Thanks!
BR\ Rajesh
Pegasystems Inc.
US
@RajeshS17304030You should use the OracleQuery component. If you have the Oracle drivers installed, then when you click the ConnectionString property, there should be three dots. The wizard should walk you through connecting to the database. You need to be using the Oracle client version 8.1.7 or greater (basically whatever they'd require you to install to query the database from your machine using any other tools). If not, this might explain why you cannot connect to it.
Pegasystems Inc.
US
@ThomasSasnett As for parameters, I believe you define them in the command text by using a colon. Once you have the drivers installed and can connect to the database, let me know if you are still having issues with parameters. You can supply a sample project, and I can try to edit it. I do not have an Oracle database easily accessible to me, but I bet we can figure it out with code you can test.
Updated: 29 Jul 2025 18:45 EDT
Arbetsförmedlingen
SE
Thank you @ThomasSasnett
I'm using ODBC configuration, installed ODBC drivers through instantclient_19_27. I do not have Oracle client installed on remote machines, not sure what it the approach here. But with ODBC configuration, the test connection was successful, I took ODBCQuery activity from tool box and when I double clicked on the query in Globals it shows query executed successfully. But when I dragged the Query->Execute method or Query->GetTable to the automation, and connecting it to the string variable to fetch results, but its not working. Not sure what I'm missing.
Is it mandatory to use Oracle Query? If yes, do I need to install database client? or Can I manage with ODBC configuration and ODBCQuery activity? If yes, how can I extract results to a variables? BR\ Rajesh
Pegasystems Inc.
US
@RajeshS17304030 If you are using ODBC, then no. Use the ODBC Query. It looks like you need to call Execute first. Does your query return rows or a value? If it returns rows, then after calling Execute, you'd call GetTable to get those rows out.