Establishing the connection between oracle and Katalon. Oracle installed in azure vm and katalon installed in remote windows server. how to integrate both?

Integrations
,
1

Establishing the connection between oracle and Katalon. Oracle installed in azure vm and katalon installed in remote windows server. how to integrate both?

2

To integrate Oracle Database hosted on an Azure VM with Katalon Studio on a remote Windows Server, follow these structured steps:

1. Network Configuration

  • Azure Network Security Group (NSG):
    • Add an inbound rule to allow TCP traffic on port 1521 (Oracle’s default) from the Katalon server’s IP.
    • (Optional) Restrict access to the Katalon server’s IP for security.
  • Windows Firewall on Azure VM:
    • Ensure port 1521 is open in the Windows Firewall for inbound connections.

2. Oracle Database Setup

  • Listener Configuration:
    • Edit listener.ora to bind the listener to the VM’s public IP or 0.0.0.0:
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) 
    ) 
  )
  • Restart the listener:
lsnrctl stop
lsnrctl start
  • TNS Configuration:
    • Update tnsnames.ora with the correct service name and host:
ORCL = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = <Azure_VM_Public_IP>)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ORCL) 
    ) 
  )
  • User Permissions:
    • Create a user with remote access privileges:
CREATE USER katalon_user IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO katalon_user;

3. Katalon Studio Configuration

  • JDBC Driver:
    1. Download the Oracle JDBC driver (e.g., ojdbc8.jar for Oracle 19c/12c) from Oracle’s site.
    2. Place the JAR file in Katalon’s Drivers directory.
    3. Refresh the project: Right-click project > Refresh.
  • Connection Script:
    Use Katalon’s Database keywords to connect:
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.db.DBManager

// JDBC URL format: jdbc:oracle:thin:@host:port/service_name
def url = 'jdbc:oracle:thin:@<Azure_VM_Public_IP>:1521/ORCL'
def user = 'katalon_user'
def password = '<password>'

// Establish connection
def conn = DBManager.getConnection(url, user, password)

// Execute query (example)
def rs = DBManager.executeQuery(conn, 'SELECT * FROM DUAL')
WebUI.comment(rs.toString())

// Close connection
DBManager.closeConnection(conn)

4. Connectivity Testing

  • From Katalon Server:
    • Use tnsping to test Oracle connectivity:
tnsping ORCL
  • Test with SQL*Plus:
sqlplus katalon_user/<password>@//<Azure_VM_Public_IP>:1521/ORCL

5. Troubleshooting

  • Common Errors:
    • ORA-12541: TNS No Listener: Check NSG/firewall rules and Oracle listener status.
    • ORA-01017: Invalid Credentials: Verify username/password.
    • Driver Not Found: Ensure the JDBC JAR is in Drivers and the project is refreshed.
  • Logs:
    • Check Katalon’s Console for detailed error messages.
    • Review Oracle’s alert.log and listener logs (listener.log).

6. Security Recommendations

  • VPN/Private Link: Use Azure VPN Gateway or Private Link for secure connectivity.
  • SSL Encryption: Configure Oracle for SSL/TLS to encrypt data in transit.