#15844: Parameters are not been identified inside single quotes for OCI connections

alonsomendez reported 2021-03-26T19:56:46Z · last modified 2021-10-20T15:48:15Z

Parameters are not been identified inside single quotes for OCI connections

Dev	shubhamy101
QA

Priority	Major
Complexity	Unknown

Component	Q Analyzer - Parameterized Scripts
Version	22.1

An user is reporting that when using parametrized scripts, the parameter variable is not been recognized when it is inside single quotes when connecting to an Oracle OCI connection like in the following example:

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('\&1','MMDDYYYY')

When running this example, the Query Analyzer window keep spinning and the Parameters window never pops up. When the parameter is not inside quotes, the script runs properly.

Idera issue #271

9 attachments

Not Able to Reproduce Issue

2021-09-06T08:21:29Z

185 KB
OCI9.png

2021-09-13T22:13:12Z

145 KB
Backslash.png

2021-09-15T16:42:42Z

45 KB
Parameterized Script Quoted String Backslash Enabled is checked 1.png

2021-09-22T15:17:46Z

813 KB
Oracle Options.png

2021-09-22T15:17:46Z

525 KB
Table3 Offset.png

2021-09-22T15:18:19Z

460 KB
Parameterized Script Quoted String Backslash Enabled is checked 4.png

2021-09-22T15:18:19Z

496 KB
Oracle Options.png

2021-09-22T15:19:30Z

451 KB
Parameterized Script Quoted String Backslash Enabled is not checked 3.png

2021-09-22T15:19:30Z

862 KB

All Comments (17) Change History

tomconrad 2021-08-10T16:00:49Z

To see how parameterized scripting works, look at ADS documentation here...

shubhamy101 2021-09-06T08:24:58Z

Hi Tom,

I was not able to reproduce the issue mentioned (The Parameter window is popping up for every case I tried).

I am also attaching document with snapshots for your reference.

Thanks

abdullah 2021-09-15T16:27:00Z · Edited by

tomconrad

If I look into the Idera support issue #271, this is the original text which did not have \ escape

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('&1','MMDDYYYY')

This is the original issue reported:

An user is reporting that when using parametrized scripts, the parameter variable is not been recognized when it is inside single quotes like in the following example.

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('&1','MMDDYYYY')

I tested this on my system and was able to reproduce the issue. The query runs but it never asks for the parameters to be inputted. This does not happen if the parameter is not in single quotes like in the following example:

SELECT * FROM "asif"."table3"

WHERE TEST > &1

Furthermore, if you add a \ slash before the &, to make the parameter character "escape" the single quote, the query run successfully. Like this:

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('\&1','MMDDYYYY')

This solution does not work for the customer because they would have to modify all the scripts they get from Oracle Developer tools. This is the query they are running that is giving them troubles:

INSERT INTO TBL_LISP_DATA_STG

SELECT /*+ parallel (hist,16)*/ *

FROM FACETS_INTF.TBL_LISP_DATA_STG hist

WHERE (hist.contract_year = &5

OR hist.contract_year = &4)

AND hist.lis_end_dt > TO_DATE('&1','MMDDYYYY')

AND hist.contract_number in (SELECT DISTINCT PAGR.PAGR_SIC CONTRACT

FROM FACETS.CMC_GRGR_GROUP GRGR

INNER JOIN FACETS.CMC_PAGR_PARENT_GR PAGR

ON PAGR.PAGR_CK = GRGR.PAGR_CK

WHERE pagr.pagr_sic IN (SELECT DISTINCT cvst.cust_cd_value

FROM facets_intf.cvst_cust_cd_trans cvst

WHERE cvst.cust_entity = 'LICS'

AND cvst.cust_cd_type = 'CNTR'));

Parameters &4 and &5 are asked for and replaced as expected for the execution. But parameter &1 is not.

Attached is the SQL Log from my test. In there you can see that the parameter is not been set when running the query. I did this on an Oracle 12c on version 21 connection and the customer did it on an Oracle 12c OCI connection on version 20.0

If I look into the Idera support issue #271, this is the original text which did not have \ escape

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('&1','MMDDYYYY')

This is the original issue reported:

An user is reporting that when using parametrized scripts, the parameter variable is not been recognized when it is inside single quotes like in the following example.

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('&1','MMDDYYYY')

SELECT * FROM "asif"."table3"

WHERE TEST > &1

Furthermore, if you add a \ slash before the &, to make the parameter character "escape" the single quote, the query run successfully. Like this:

SELECT * FROM "asif"."table3"

WHERE TEST > TO_DATE('\&1','MMDDYYYY')

INSERT INTO TBL_LISP_DATA_STG

SELECT /*+ parallel (hist,16)*/ *

FROM FACETS_INTF.TBL_LISP_DATA_STG hist

WHERE (hist.contract_year = &5

OR hist.contract_year = &4)

AND hist.lis_end_dt > TO_DATE('&1','MMDDYYYY')

AND hist.contract_number in (SELECT DISTINCT PAGR.PAGR_SIC CONTRACT

FROM FACETS.CMC_GRGR_GROUP GRGR

INNER JOIN FACETS.CMC_PAGR_PARENT_GR PAGR

ON PAGR.PAGR_CK = GRGR.PAGR_CK

WHERE pagr.pagr_sic IN (SELECT DISTINCT cvst.cust_cd_value

FROM facets_intf.cvst_cust_cd_trans cvst

WHERE cvst.cust_entity = 'LICS'

AND cvst.cust_cd_type = 'CNTR'));

Parameters &4 and &5 are asked for and replaced as expected for the execution. But parameter &1 is not.

tomconrad 2021-09-07T14:56:32Z · (edited)

As I recall, this works fine using thin client but did not work using an OCI connection when using the \ escape.

shubhamy101 2021-09-13T08:29:03Z · (edited)

Fixed in SVN Revision: 58959

Parameters are being recognized inside single quotes for OCI connections

tomconrad 2021-09-13T22:16:21Z · (edited)

Hi Shubham,

Please demonstrate your unit tests here with screenshots. It doesn't seem to be working for me. See here. The right QA window is thin client. The left QA window is OCI. The OCI window never prompted on the first statement. The second statement had a prompt.

Thanks,

Tom

Hi QA Team,

Please test this with Oracle 19 as well. Please also regression test the Oracle thin client connections to make sure that not was changed.

Thanks,

Tom

shubhamy101 2021-09-14T07:07:35Z

Hi Tom,

We don't have Oracle OCI v19 Connection file.

Can you provide us that.

Thanks,

Shubham

tomconrad 2021-09-14T17:55:16Z

Hi Shubham,

I added it to the gdrive.

Thanks,
Tom

tomconrad 2021-09-15T16:50:53Z

Hi Shubham,

We should add a checkbox option to File->Options->Query Analyzer->Oracle 9i/10g/11g/12c/18c/19c where the title is Parameterized Script - Quoted String Backslash Enabled and the description is Default for the use of a backslash with a quoted string. Checked or Enabled requires that a backslash be used when parameterizing a quoted string like SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = ’\&name’. Unchecked or disabled does not require a backslash in a quoted string like SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = ’&name’

Let me know if you have questions.

Thanks,

Tom

tomconrad 2021-09-15T21:36:07Z

Hi Nadine,

We should document this new function. Once it is complete, we can talk about it.

Thanks,

Tom

shubhamy101 2021-09-17T09:35:53Z

Fixed in SVN Revision: 58966

Backslash Selection Checkbox added for Oracle OCI Connection

tomconrad 2021-09-17T20:21:18Z · (edited)

Hi Shubham,

I did a quick test and looks like it is working correctly... Thank you.

Hi QA Team,

Please test this new feature with Oracle OCI and thin client. In addition, please regression test a couple of databases like SQL Server, MySQL and PostgreSQL to make sure that their parameterized script behavior has not changed. Try with the option enabled and disabled. This option should only affect Oracle.

Thanks,

Tom

shubham.tripathi 2021-09-21T06:07:54Z

Hi Tom/Asif,

Please provide oracle 19 OCI setup steps for ubuntu platform.

Thankyou,

Shubham Tripathi

tomconrad 2021-09-21T14:36:10Z

Hi Shubham,

Let's just test on Mac and Windows and not on Ubuntu.

Thanks,

Tom

rajkiran.reddy 2021-09-22T15:12:47Z

QA verified:

Parameters are now been identified inside single quotes for Oracle OCI connections is working as expected with the latest build.

Build tested: ADS v 22.1.0-dev-20

Platform tested : Windows-10
Please find the attached screenshots.

Test rail link
For windows : https://idera.testrail.net/index.php?/runs/view/4618&group_by=cases:section_id&group_order=asc&group_id=32866

shubham.tripathi 2021-09-24T10:07:24Z

QA verified:

Parameters are now been identified inside single quotes for Oracle OCI connections is working as expected with the latest build.

Build tested: ADS v 22.1.0-dev-20

Platform tested : Windows-10

Test rail link
For Mac: ADS 22.1 Test Suite (MAC) - TestRail

Thankyou,

Shubham Tripathi

shubham.tripathi 2021-10-20T15:48:15Z

QE Verified:

Parameters are not been identified inside single quotes for OCI connections

ticket and its working as expected

Build tested: \\172.20.32.12\Divisions\Divisions\Aquafold\datastudio\v22.1.0-rc-1

Platform tested : Mac OS,Windows-10, Ubuntu

TestRail Link :-

Mac:ADS 22.1 Test Suite - MAC - TestRail

Windows-10: ADS 22.1 Test Suite - Windows 10 - TestRail

Ubuntu: ADS 22.1 Test Suite - Linux - TestRail

Thanks

Search Tips

Aqua Data Studio / nhilam

Parameters are not been identified inside single quotes for OCI connections

9 attachments

Issue #15844

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Parameters are not been identified inside single quotes for OCI connections

9 attachments

Issue #15844

Completion