Another bug for the collection

fidelfs · Sep 19, 2016

I have copied and pasted a variable containing a dynamic select statement.
i.e.:
l_main_query_part1 VARCHAR2(32000):= 'SELECT
a.operation
,a.cost_center....
....
...

In a random way some constants were changed from capital to lower case.

FROM
-----
AND glb2.period_name = gp.period_name
and gp.period_set_name = ''DR Standard Mo''
AND glb2.ledger_id =

_ledger_id

TO
---
AND glb2.period_name = gp.period_name
and gp.period_set_name = ''Dr Standard Mo''
AND glb2.ledger_id =

_ledger_id

or

FROM
----
AND glb2.actual_flag = ''A''

TO
----
AND glb2.actual_flag = ''a''

In a randow way some were changed when others did not.

Marco Kalter · Sep 20, 2016

I have no immediate explanation for this. Is this issue reproducible? If so, can you let me know the exact steps?

fidelfs · Sep 20, 2016

It only happens when you are copy and paste within Pl/SQL developer.
Copying from a program type (procedure, package, etc) into a sql window.

It happens with constants in a dynamic variable. I think is because the double quote used in the dynamic variable to represent a single quote. It is not for all the quote values, but it only happen for those quote vales.

It also adds an space after the first double "single" quote and before the final double "single" quote.

i.e. This is the original value ''THIS IS AN EXAMPLE''
and it is copied into '' THIS iS aN EXAMPLE '' Note the extra space.

fidelfs · Sep 20, 2016

Making a correction. It is not the copy and paste.
It is the beautifier that changes to capital to lower case and adds spaces.

This is the code before beautifier:

SELECT
a.operation
,a.cost_center
,a.account
,a.counter_party
,a.component_stream
,a.account_analysis
,a.account_basis
,a.product_code
,a.future_seg
,a.code_combination_id
,SUM(a.start_period_balance) start_period_balance
,SUM(a.current_period_balance) current_period_balance
,SUM(a.current_period_balance) - SUM(a.start_period_balance) movment
,a.period_name
,

_ledger_id ledger_id,

_currency_code currency_code,

_startperiod start_period,

_currentperiod current_period
FROM
(SELECT MAX (glb.period_name) period_name,
glcc.segment1 operation,
glcc.segment2 cost_center, glcc.segment3 ACCOUNT
,
glcc.segment4 counter_party,
glcc.segment5 component_stream,
glcc.segment8 account_analysis,
glcc.segment9 account_basis,
glcc.segment7 product_code,
glcc.segment10 future_seg,
glcc.code_combination_id,
SUM(GLB.PERIOD_NET_DR - GLB.PERIOD_NET_CR)
start_period_balance,
0 current_period_balance
FROM apps.gl_balances GLB,
apps.gl_code_combinations glcc,
apps.gl_periods gp
WHERE GLB.actual_flag = ''A''
AND gp.start_date BETWEEN TO_DATE(''01/01/1950'',''MM/DD/YYYY'') AND
(SELECT start_date FROM gl_periods WHERE
period_num||''-''||period_year =

_startperiod
AND period_set_name = ''DR Standard Mo'')
AND GLB.code_combination_id = glcc.code_combination_id
AND glb.period_name = gp.period_name
and gp.period_set_name = ''DR Standard Mo''
AND glcc.summary_flag = ''N''
AND GLB.template_id IS NULL
AND GLB.ledger_id =

_ledger_id
AND glcc.chart_of_accounts_id =

_coa_id
AND GLB.currency_code =

_currency_code
GROUP BY segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment8,
segment9,
segment7,
segment10,
glcc.code_combination_id
UNION ALL
SELECT MAX (glb2.period_name) period_name,
glcc.segment1 operation,
glcc.segment2 cost_center, glcc.segment3 ACCOUNT
,
glcc.segment4 counter_party,
glcc.segment5 component_stream,
glcc.segment8 account_analysis,
glcc.segment9 account_basis,
glcc.segment7 product_code,
glcc.segment10 future_seg,
glcc.code_combination_id,
0 start_period_balance,
SUM(glb2.PERIOD_NET_DR - glb2.PERIOD_NET_CR)
current_period_balance
FROM apps.gl_balances glb2,
apps.gl_code_combinations glcc,
apps.gl_periods gp
WHERE 1=1
AND glcc.summary_flag = ''N''
AND glb2.template_id IS NULL --Added on 15-JAN-2016
AND glcc.chart_of_accounts_id =

_coa_id
AND glb2.actual_flag = ''A''
AND glb2.code_combination_id = glcc.code_combination_id
AND glb2.period_name = gp.period_name
and gp.period_set_name = ''DR Standard Mo''
AND glb2.ledger_id =

_ledger_id
AND glb2.currency_code =

_currency_code
AND gp.start_date BETWEEN TO_DATE(''01/01/1950'',''MM/DD/YYYY'') AND
(SELECT start_date FROM gl_periods WHERE
period_num||''-''||period_year =

_currentperiod
AND period_set_name = ''DR Standard Mo'')GROUP BY segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment8,
segment9,
segment7,
segment10,
glcc.code_combination_id)a
GROUP BY
a.operation
,a.cost_center
,a.ACCOUNT
,a.counter_party
,a.component_stream
,a.account_analysis
,a.account_basis
,a.product_code
,a.future_seg
,a.period_name
,a.code_combination_id';

fidelfs · Sep 20, 2016

This is the code after the beuatifier:

SELECT a.Operation
,a.Cost_Center
,a.Account
,a.Counter_Party
,a.Component_Stream
,a.Account_Analysis
,a.Account_Basis
,a.Product_Code
,a.Future_Seg
,a.Code_Combination_Id
,SUM(a.Start_Period_Balance) Start_Period_Balance
,SUM(a.Current_Period_Balance) Current_Period_Balance
,SUM(a.Current_Period_Balance) - SUM(a.Start_Period_Balance) Movment
,a.Period_Name
,

_Ledger_Id Ledger_Id
,

_Currency_Code Currency_Code
,

_Startperiod Start_Period
,

_Currentperiod Current_Period
FROM (SELECT MAX(Glb.Period_Name) Period_Name
,Glcc.Segment1 Operation
,Glcc.Segment2 Cost_Center
,Glcc.Segment3 Account
,Glcc.Segment4 Counter_Party
,Glcc.Segment5 Component_Stream
,Glcc.Segment8 Account_Analysis
,Glcc.Segment9 Account_Basis
,Glcc.Segment7 Product_Code
,Glcc.Segment10 Future_Seg
,Glcc.Code_Combination_Id
,SUM(Glb.Period_Net_Dr - Glb.Period_Net_Cr) Start_Period_Balance
,0 Current_Period_Balance
FROM Apps.Gl_Balances Glb
,Apps.Gl_Code_Combinations Glcc
,Apps.Gl_Periods Gp
WHERE Glb.Actual_Flag = '' a
''
AND Gp.Start_Date BETWEEN
To_Date('' 01 / 01 / 1950 '', '' Mm / Dd / Yyyy '') AND
(SELECT Start_Date
FROM Gl_Periods
WHERE Period_Num || '' - '' || Period_Year =

_Startperiod
AND Period_Set_Name = '' Dr STANDARD Mo '')
AND Glb.Code_Combination_Id = Glcc.Code_Combination_Id
AND Glb.Period_Name = Gp.Period_Name
AND Gp.Period_Set_Name = '' Dr STANDARD Mo ''
AND Glcc.Summary_Flag = ''
N''
AND Glb.Template_Id IS NULL
AND Glb.Ledger_Id =

_Ledger_Id
AND Glcc.Chart_Of_Accounts_Id =

_Coa_Id
AND Glb.Currency_Code =

_Currency_Code
GROUP BY Segment1
,Segment2
,Segment3
,Segment4
,Segment5
,Segment6
,Segment8
,Segment9
,Segment7
,Segment10
,Glcc.Code_Combination_Id
UNION ALL
SELECT MAX(Glb2.Period_Name) Period_Name
,Glcc.Segment1 Operation
,Glcc.Segment2 Cost_Center
,Glcc.Segment3 Account
,Glcc.Segment4 Counter_Party
,Glcc.Segment5 Component_Stream
,Glcc.Segment8 Account_Analysis
,Glcc.Segment9 Account_Basis
,Glcc.Segment7 Product_Code
,Glcc.Segment10 Future_Seg
,Glcc.Code_Combination_Id
,0 Start_Period_Balance
,SUM(Glb2.Period_Net_Dr - Glb2.Period_Net_Cr) Current_Period_Balance
FROM Apps.Gl_Balances Glb2
,Apps.Gl_Code_Combinations Glcc
,Apps.Gl_Periods Gp
WHERE 1 = 1
AND Glcc.Summary_Flag = ''
N''
AND Glb2.Template_Id IS NULL --Added on 15-JAN-2016
AND Glcc.Chart_Of_Accounts_Id =

_Coa_Id
AND Glb2.Actual_Flag = '' a
''
AND Glb2.Code_Combination_Id = Glcc.Code_Combination_Id
AND Glb2.Period_Name = Gp.Period_Name
AND Gp.Period_Set_Name = '' Dr STANDARD Mo
''
AND Glb2.Ledger_Id =

_Ledger_Id
AND Glb2.Currency_Code =

_Currency_Code
AND Gp.Start_Date BETWEEN
To_Date('' 01 / 01 / 1950 '', '' Mm / Dd / Yyyy '') AND
(SELECT Start_Date
FROM Gl_Periods
WHERE Period_Num || '' - '' || Period_Year =

_Currentperiod
AND Period_Set_Name = '' Dr STANDARD Mo '')
GROUP BY Segment1
,Segment2
,Segment3
,Segment4
,Segment5
,Segment6
,Segment8
,Segment9
,Segment7
,Segment10
,Glcc.Code_Combination_Id) a
GROUP BY a.Operation
,a.Cost_Center
,a.Account
,a.Counter_Party
,a.Component_Stream
,a.Account_Analysis
,a.Account_Basis
,a.Product_Code
,a.Future_Seg
,a.Period_Name
, a.Code_Combination_Id ';

fidelfs · Sep 20, 2016

If I remove the double "single" quotes before beautifier then there isn't a problem.
The double quotes fools beautifier.

Claus Pedersen · Sep 21, 2016

The select in the example is not valid SQL. How should the beautifier know how to handle code that is not valid?

Double quotes equals an empty string in Oracle SQL. How should the beautifier handle code like ''A'', i.e. A?
The beautifier must then treat the A like an attribute, and everything goes haywire

fidelfs · Sep 21, 2016

It is a valid statement.

test this:

SELECT 'This string has a single quote ('')' my_string
FROM dual

That is how you handle escape quotes. and oracle compiler agrees with the statement.

Marco please take a look.

fidelfs · Sep 22, 2016

Bump! I think this is too critical to let sleep.

I might be 100% wrong, but I need to be sure that it is not me.

William Robertson · Sep 22, 2016

True, but in your 'Before beautifier' post the first quote character appears in the line:

Code:

WHERE GLB.actual_flag = ''A''

fidelfs · Sep 23, 2016

Yes, that is the before beautifier.

The second post after the "Beautifier", it created the second line with the lower case "a"

Claus Pedersen · Sep 25, 2016

Please confirm that the select

SQL:

SELECT *
FROM apps.gl_balances
WHERE actual_flag = ''A''

gives an error when executed (because of the double quotes),
whereas

SQL:

SELECT *
FROM apps.gl_balances
WHERE actual_flag = 'A'

is valid sql?

In the first example, the beatifier will try to interpret A as a column name (and not a string literal) and will therefore put in in lower case, a.

fidelfs · Sep 26, 2016

Claus,

My bug is for a dynamic select statement.
i.e. var1 varchar2(1000) := 'Select * from apps.gl_balances where actual_flag = ''A''';

It is in my first post. Double "single" quote is standard way to escape the single quote.
It is not so a select like you wrote.

Another bug for the collection

fidelfs

Member³

Marco Kalter

Administrator

fidelfs

Member³

fidelfs

Member³

fidelfs

Member³

fidelfs

Member³

Claus Pedersen

Member³

fidelfs

Member³

fidelfs

Member³

William Robertson

Member³

fidelfs

Member³

Claus Pedersen

Member³

fidelfs

Member³

Similar threads