1

Topic: Error at analysis getStringVal

All greetings. Someone can directs on the correct thought. There is such construction:
[spoiler]

SELECT TRANSLATE (
XMLAGG (XMLELEMENT ("node", d1.name)).getStringVal ();
' <node> / ';
' ')
FROM ICC_RSR.DICT_REGIONS d1
WHERE opv.text_value LIKE ' % ' || TO_CHAR (d1.reg_id) || ' %'

[/spoiler]
In which the line of a type is transferred:

1,2,6

That titles from the region reference manual are tightened correctly, according to classification reg_id
But, if to transfer a line of a type:

24

That working off happens, on 2, 4 and 24 ! Why so happens, can eat a variant as it is possible to organize accurately the correct transmission, in what a hitch?

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2

Re: Error at analysis getStringVal

And a brain to include?

opv.text_value LIKE ' % ' || TO_CHAR (d1.reg_id) || ' %'

c for d1.reg_id = 2 in

' 24 ' LIKE ' %2 %'

And result = TRUE. For d1.reg_id = 4 in

' 24 ' LIKE ' %4 %'

And result = TRUE. For d1.reg_id = 24 in

' 24 ' LIKE ' %24 %'

And result too TRUE. It is necessary to check:

>>> ', ' || <<<opv.text_value>>> || ', ' <<<LIKE ' %>>>, <<<' || TO_CHAR (d1.reg_id) || '>>>, <<<% '>>> and d1.reg_id IS NOT NULL <<<

And it is better:

d1.reg_id in (select regexp_substr (opv.text_value, ' [^,] + ', 1, level) from dual connect by level <= regexp_count (opv.text_value, ', ') + 1)

SY.

3

Re: Error at analysis getStringVal

Did not note version 10.2 so-that replace REGEXP_COUNT on REGEXP_REPLACE + LENGTH. I hope you will think. And if you chase milliseconds replace REGEXP on SUBSTR + INSTR.
SY.

4

Re: Error at analysis getStringVal

SY;
Thanks.
But I did not understand why for

opv.text_value LIKE ' % ' || TO_CHAR (d1.reg_id) || ' %'

When the line 24 arrives, and it is cut on 2, then 4 and 24.
And why it is better to use:

d1.reg_id in (select regexp_substr (opv.text_value, ' [^,] + ', 1, level) from dual connect by level <= regexp_count (opv.text_value, ', ') + 1)

5

Re: Error at analysis getStringVal

Sheriffua;
For those who in t-34, LIKE ' %2 % ' mean checking on twain presence in any position so  will be "yes" for any value opv.text_value in which is present a.
SY.
P.S. Forgot that at you e a fractional part  instead of  as at e. And generally, to avoid NLS problems use o e in opv.text_value, for example, pipe together with:

' | ' || opv.text_value || ' | ' LIKE ' % | ' || TO_CHAR (d1.reg_id) || ' | % ' and d1.reg_id IS NOT NULL

6

Re: Error at analysis getStringVal

Sheriffua wrote:

And why it is better to use:

d1.reg_id Can be indexed.
SY.

7

Re: Error at analysis getStringVal

SY wrote:

Sheriffua;
For those who in t-34, LIKE ' %2 % ' mean checking on twain presence in any position so  will be "yes" for any value opv.text_value in which is present a.
SY.
P.S. Forgot that at you e a fractional part  instead of  as at e. And generally, to avoid NLS problems use o e in opv.text_value, for example, pipe together with:

' | ' || opv.text_value || ' | ' LIKE ' % | ' || TO_CHAR (d1.reg_id) || ' | % ' and d1.reg_id IS NOT NULL

)))
Once again - a line opv.text_value = 24 , but analysis happens as 2 and 4 and 24... This expression:

SELECT TRANSLATE (
XMLAGG (XMLELEMENT ("node", d1.name)).getStringVal ();
' <node> / ';
' ')
FROM ICC_RSR.DICT_REGIONS d1
WHERE opv.text_value LIKE ' % ' || TO_CHAR (d1.reg_id) || ' %'

I.e. at line

WHERE opv.text_value LIKE ' % ' || TO_CHAR (d1.reg_id) || ' %'

Why there is a conversion in 2, then in 4, and then in 24? It getStringVal so transforms or what to look to understand the paternal there is such analysis?

8

Re: Error at analysis getStringVal

Sheriffua wrote:

what to look to understand the paternal there is such analysis?

- act simply in film from a bench hammer, there Solomon  chewed all.

9

Re: Error at analysis getStringVal

Sheriffua wrote:

why there is a conversion in 2, then in 4, and then in 24? It getStringVal so transforms or what to look to understand the paternal there is such analysis?

Yes and here getStringVal??? I seem clearly  that without a border e LIKE ' % ' || TO_CHAR (d1.reg_id) || ' % ' produces "yes" if d1.reg_id is a part of one of numbers in opv.text_value:

with d1 as (
select 2 reg_id from dual union all
select 4 reg_id from dual union all
select 24 reg_id from dual
)
select reg_id
from d1
where ' 24 ' like ' % ' || to_char (d1.reg_id) || ' %'
/
REG_ID
----------
2 - the character the twain is present in line ' 24 ' (we  lines, not numbers) therefore ' 24 ' like ' %2 % ' give "yes"
4 - the character quadruple is present in line ' 24 ' (we  lines, not numbers) therefore ' 24 ' like ' %4 % ' give "yes"
24 - the character the twain for which e  (we  lines, not numbers) is present in line ' 24 ' therefore ' 24 ' like ' %24 % ' give "yes"
SQL>

SY.

10

Re: Error at analysis getStringVal

SY;
Thanks for detailed clarification.

11

Re: Error at analysis getStringVal

Sheriffua wrote:

thanks for detailed clarification.

But I advise:

d1.reg_id in (
select regexp_substr (opv.text_value, ' [^,] + ', 1, level)
from dual
connect by level <= length (
regexp_replace (
opv.text_value || ', ';
' [^,]'
)
)
)

SY.

12

Re: Error at analysis getStringVal

SY wrote:

it is passed...
But I advise:

d1.reg_id in (
select regexp_substr (opv.text_value, ' [^,] + ', 1, level)
from dual
connect by level <= length (
regexp_replace (
opv.text_value || ', ';
' [^,]'
)
)
)

SY.

Thanks for council with application of the regular expressions.
If it is simple, briefly, than it is better LIKE? which you resulted in the first message?

13

Re: Error at analysis getStringVal

Sheriffua wrote:

If it is simple, briefly, than it is better LIKE? which you resulted in the first message?

I already : d1.reg_id can be indexed.

SQL> explain plan for
2 select *
3 from emp
4 where ', ' || ' 7566,7654,7698 ' || ', ' like ' %, ' || empno || ', %'
5 /
Explained.
SQL> select *
2 from table (dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 0:00:01 AM |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0) | 0:00:01 AM |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter (', 7566,7654,7698, ' LIKE ' %, ' || TO_CHAR ("EMPNO") || ', % ')
13 rows selected.
SQL> explain plan for
2 select *
3 from emp
4 where empno in (
5 select regexp_substr (' 7566,7654,7698 ', ' [^,] + ', 1, level)
6 from dual
7 connect by level <= length (regexp_replace (' 7566,7654,7698 ' || ', ', ' [^,] '))
8)
9 /
Explained.
SQL> select *
2 from table (dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 1513497066
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 4 (25) | 0:00:01 AM |
| 1 | NESTED LOOPS | | 1 | 68 | 4 (25) | 0:00:01 AM |
| 2 | NESTED LOOPS | | 1 | 68 | 4 (25) | 0:00:01 AM |
| 3 | VIEW | VW_NSO_1 | 1 | 30 | 3 (34) | 0:00:01 AM |
| 4 | HASH UNIQUE | | 1 | | 3 (34) | 0:00:01 AM |
|* 5 | CONNECT BY WITHOUT FILTERING (UNIQUE) | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0) | 0:00:01 AM |
|* 7 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 0:00:01 AM |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 0:00:01 AM |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter (LEVEL <=3)
7 - access ("EMPNO" =TO_NUMBER ("REGEXP_SUBSTR (' 7566,7654,7698 ', ' [^,] + ', 1, LEVEL)"))
Note
-----
- this is an adaptive plan
25 rows selected.
SQL>

And even if if the index is not present, it can be necessary later when, for example,  the data increases and TABLE ACCESS FULL becomes too slow. In a case with LIKE it is necessary to rewrite the code and with IN all is transparent also it picks up the created index.
SY.

14

Re: Error at analysis getStringVal

SY;
Now all is clear. Once again for detailed , a subject it is possible to close thanks.