Query OR 'Execute Sql String' of Database Function returning connection details instead of data

[ChitraLekhaBHeemana]

Query OR 'Execute Sql String' Database Function returning connection details instead of data.

Query Statemenet: : Select Packagename.function from dual;

[jerry57]

Can you please send me a code example testsuite so that I can try to troubleshoot this? I use both of these keywords daily without issue. Please include your database connection string? And the actual error returned in the log.html.

[ChitraLekhaBHeemana]

Connect To Database Using Custom Params cx_Oracle user='${DB_USER}',password='${DB__PWD}',dsn='${DB__DSN}'

@{Query_String} Query select Pkg.function ( XXX , y ) from dual

NOTE: Regular "select

.." queries work fine. Have issue only with FUNCTION calls.

[jerry57]

I am not sure what the problem is here. Here is a testsuite I can use against MS SQL without error. I do not have access to an Oracle DB right now.

*** Settings ***
Library DatabaseLibrary
Library Collections
Suite Setup Connect To Database pymssql ${DBName} ${DBUser} ${DBPass} ${DBHost} ${DBPort}
Suite Teardown Disconnect From Database
Resource ../Global_Vars.robot

*** Test Cases ***
Call certificate.MOCStartYear passing in ABRID (Scalar Function)
[Tags] db unit
Set Test Variable ${ABRID} 55555
@{output} = Query SELECT certificate.MOCStartYear(${ABRID})
Log List ${output}
Log ${output}
Log Many @{output}
List Should Contain Value ${output[0]} ${2004}

Call exam.OLADemoUserRandom passing in DisciplineSpecialtyID (Table Function)
[Tags] db unit
Set Test Variable ${DisciplineSpecialtyID} 5
@{output} = Query SELECT * FROM exam.OLADemoUserRandom(${DisciplineSpecialtyID})
Log List ${output[0]}
Log ${output}
Log Many @{output}

Can you please send me a complete testsuite/testcases with the actual log.html output so that I can see the errors you are having?

[adrianyorke]

We need to see the full robot script for the Oracle version but could it be the missing equals sign (=) in the Oracle query?

Oracle query:
@{Query_String} Query select Pkg.function ( XXX , y ) from dual

SQL query:
@{output} = Query SELECT certificate.MOCStartYear(${ABRID})

[jerry57]

The "=" should not make a difference. You need 2+ spaces between @{output} and Query and SELECT. Can you please attach/send a sample testsuite for Oracle that is failing along with the log.html output file? Seeing the suite and output file would help a lot for me to troubleshoot this issue.

[ChitraLekhaBHeemana]

robot file:

*** Settings ***
Library DatabaseLibrary
Variables testdata.py

*** Test Cases ***

Connect To Database
Connect To Database Using Custom Params cx_Oracle user='${DB_USER}',password='${DB_PWD}',dsn='${DB_DSN}'

Verify Data
@{QUERY_STRING} Query select ${Package} from dual
log to console @{QUERY_STRING}
log to console ${QUERY_STRING}[0]

Disconnect DB
disconnect from database

O/p:
(<cx_Oracle.Cursor on <cx_Oracle.Connection to globals@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxx)))>>,)

[jerry57]

Do you have a database/server that I can connect to so that I may try and troubleshoot this right now?
If so, please let me know the connection information?

[ChitraLekhaBHeemana]

Sorry I cannot provide them.

[jerry57]

Can you run with DEBUG or TRACE log levels and send in the complete log then?

[ChitraLekhaBHeemana]

Uses the input selectStatement to query for the values that will be returned as a list of tuples. Set optional input sansTran to True to run command without an explicit transaction commit or rollback. Set optional input returnAsDict to True to return values as a list of dictionaries.
Start / End / Elapsed: 20191011 15:01:26.513 / 20191011 15:01:26.600 / 00:00:00.087
15:01:26.513 TRACE Arguments: [ "select zzz.pkgfff.yy(bbb ) from dual" ]
15:01:26.513 INFO Executing : Query | select zzz.pkgfff.yy(bbb) from dual
15:01:26.600 TRACE Return: [(<cx_Oracle.Cursor on <cx_Oracle.Connection to zzz@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ccc)))>>,)]
15:01:26.600 INFO @{QUERY_STRING} = [ (<cx_Oracle.Cursor on <cx_Oracle.Connection to zzz@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ccc)))>>,) ]

I do not see any additional log

[jerry57]

Have you been able to get this working? Connection appears to be fine and no errors returned. I can not duplicate this against other DBs and do not have an Oracle setup right now

[adrianyorke]

@jerry57: oracle have a bunch of Pre-Built Developer VMs (for Oracle VM VirtualBox): https://www.oracle.com/downloads/developer-vm/community-downloads.html

I use Vagrant a lot and it makes downloading and managing VMs really easy. I've not tried any of the Oracle DB VMs though. Let me know if you want me to try them out.