How to connect to Oracle Database using DatabaseLibrary

[Christiano ronaldo]

Hi All,

            Please could you get the code in robotframework to connect to Oracle Database using robotframework Library  - DatabaseLibrary.

1. I need the code to get the connection string.

2. I need the code to retrieve data using select query

3. I need the code to update  a column in the table...

Thanks in advance 

baros

[Vikas Kumar]

4. Oracle Client for Database Connection: 

1. Pip install cx_Oracle 

2. Pip install robotframework-databaselibrary 

3. Download the Oracle Client and place it on local machine with  

Link to download the zip file https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html 

Extract the file and place it in the C drive for example : 

Location of the client file should be added in the System Variables: 

Script:

*** Settings ***
Documentation       All Testcases Related to The User Profile Test Will be Placed in the File.
Resource            ../Resources/Common/TFNR_DB_Utilities.robot

*** Variables ***

*** Test Cases ***
SAMPLE Execute DB Select Query And Return Value
    @{Value}    Execute DB Select Query And Return Value    select CNTC_NM,CNTC_NB from ${DATABASE_SCHEMA}.USER_CNTL where USER_LGON_ID Like '${VALID USER}'
    ${name}     fetch from left  ${Value}[0][0]  ${SPACE}
    log     ${name}
    Log   ${Value}[0][0]
    log     ${Value}[0][1]

-----------------------

*** Settings ***
Documentation       Keyword Related to Common Database Connection.
Library             DatabaseLibrary
Library             String

Resource        ../../Environment_Data/Environment_Data.robot
*** Variables ***

*** Keywords ***

Initialize TFNR Database Connection
    [Arguments]  ${DB_DRIVER}=${DATABASE_DRIVER}    ${user}=${DATABASE_USER}    ${password}=${DATABASE_PWD}
     ...    ${host}=${DATABASE_HOST}    ${port}=${DATABASE_PORT}    ${ServiceName}=${DATABASE_SERVICE_NAME}    ${schema_sql}=${DATABASE_SCHEMA}
    ${connection_string_oracle}=    Set Variable    '${user}/${password}@${host}:${port}/${ServiceName}'
    Run Keyword If    '${DB_DRIVER}'=='cx_Oracle'  Connect To Database Using Custom Params    ${DB_DRIVER}    ${connection_string_oracle}

Close Database Connection
    Disconnect From Database

Execute DB Select Query And Return Value
    [Documentation]  Executes any select query and returns value.Suited for queries returning scalar value(1 row - 1 Column)
    [Arguments]  ${sql_string}
    Initialize TFNR Database Connection  ${DATABASE_DRIVER}     ${DATABASE_USER}    ${DATABASE_PWD}    ${DATABASE_HOST}    ${DATABASE_PORT}    ${DATABASE_SERVICE_NAME}    ${DATABASE_SCHEMA}
    @{query_results}=  query  ${sql_string}
    Close Database Connection
    [Return]  @{query_results}

---------------------------------------------------

# Environment  Database Details for Connection and Validations of Data
${DB_TYPE}                                  oracle
${DATABASE_DRIVER}                          cx_Oracle   # For oracle use   cx_Oracle  and for SQL  pymssql
${DATABASE_USER}                                    # User Name for the DB access
${DATABASE_PWD}                                     # Password for the User in Oracle Database
${DATABASE_HOST}                                    # Databse Host of the Server for DBMS server
${DATABASE_PORT}                            1521
${DATABASE_SERVICE_NAME}                    # Service for the Oracle DB
${DATABASE_SID}                             ${DATABASE_SERVICE_NAME}
${DATABASE_SCHEMA}                          # Schema To be used for the Tables Access.
${connectionstring}                         ${DATABASE_USER}/${DATABASE_PWD}@${DATABASE_HOST}/${DATABASE_SERVICE_NAME}

[Christiano ronaldo]

Vikas,

Will the script run in remote machine as we are manually setting the environmental variable... ? can it be done on red hat linux ?

--
You received this message because you are subscribed to the Google Groups "robotframework-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to robotframework-u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/robotframework-users/0d6d1313-969e-4dcc-ac38-95cc4a4e35b9%40googlegroups.com.

[Vikas Kumar]

Yes, you can do that.

[Christiano ronaldo]

Hi All,

Please show me a way to call the oracle client from the code instead of setting the environment variable. Appreciate if it could be running on both windows and linux machine.

Regards

baros