| NOT LIKE operation on the numbers columns [message #662061] |
Sun, 16 April 2017 21:54  |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
Hi,
I am using materialized view refresh on 12c with OUT_OF_PLACE option set to TRUE and ATOMIC_REFRESH set to FALSE.
and I get the error as follows:
ORA-12008: error in materialized view refresh path
ORA-01756: quoted string not properly terminated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
and I suspect that this is because one of the lines written with the syntax as follows:
select * from tableA where ID_1 NOT LIKE '99999%' and cola = 'J'.
I would like to query the table which is using NOT LIKE operation on the ID_1 (data type = numbers).
I realized that if I remark the line ID NOT LIKE '99999%' and I am able to do the MV refreshed.
I have tried to convert the ID_1 into character, but still I will hit the MV refresh error as above.
Any clue on this?
Thanks.
|
|
|
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662064 is a reply to message #662061] |
Mon, 17 April 2017 00:23  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You say that your predicate is this where ID_1 NOT LIKE '99999%' the ID_1 (data type = numbers) so you are trying to compare a string terminated by a wild card to a number. That is not possible, so Oracle has to convert the number to a string first. Perhaps this implicit conversion is causing the problem. What happens if you create the view correctly, with proper use of TO_CHAR?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662245 is a reply to message #662219] |
Fri, 21 April 2017 06:44 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
ora_newbie111 wrote on Thu, 20 April 2017 20:52
Since the column ID_1 only contain numeric but not character, I had changed the condition to ID_1 < 99999
and it works!
Thanks everyone.
No, it's not a matter of "ID_1 only contain numeric but not character". You said earlier that ID_1 is defined as NUMBER. As such, it does not contain any "characters" at all - of any type, numeric or alphabetic, or special puncutation. None. It is purely a binary representation of of some number, not some numeric character. You still don't have a clear understanding of data types and the difference between a NUMBER and a string of numeric characters.
And given what you said your requirement was, I have my doubts that the query is now returning the correct rows. Rather, it is simply syntactically correct and is able to return something.
[Updated on: Fri, 21 April 2017 06:45] Report message to a moderator |
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662246 is a reply to message #662245] |
Fri, 21 April 2017 07:19 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
type the following command in sqlplus
DESC TABLEA
and paste the result in this issue.
also you said that the flag to not return the values is id_1 starting with 9999. So if the number is
9999123456789 does that mean not return it. Your fix just said return anything less then or equal to 10000
|
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662336 is a reply to message #662246] |
Tue, 25 April 2017 08:54 |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
DDL for tableA:
ID_1 number
I am not able to convert this columns to varchar since there involve a lot of table ammendments.
Therefore I keep it as number.
MV code
CREATE MATERIALIZED VIEW refresh_ID
(ID_1, cola)
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE TBS_1
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
select * from tableA where ID_1 not like '9999%' and cola = 'J';
it show error as follows:
ORA-12008: error in materialized view refresh path
ORA-01756: quoted string not properly terminated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
if I remark NOT like clause, it get refreshed successfully, this is not happen in 11g, only happen in 12c
In the earlier replies, it said that I suppose not to do this comparison since it is illogical to have a numeric columns that using NOT LIKE '9999%'.
if this is not the solution for this, and the less than is not able to be used for since it may not cover those numeric that with more than 5 figures as mention 9999123456789.
I am wondering now both solution is not working either, yes, I want to exclude records that starting from first 4 digit is 9999.
|
|
|
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662340 is a reply to message #662338] |
Tue, 25 April 2017 10:53 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
What is the database version. I made a test table tablea
>desc tablea
Name Null? Type
----------------------------------------- -------- ---------------------
ID_1 NUMBER
COLA VARCHAR2(1)
and populated it with test data and using the following where in my MVIEW
where to_char(ID_1) not like '9999%' and cola = 'J';
[Updated on: Tue, 25 April 2017 10:53] Report message to a moderator |
|
|
|
| Re: NOT LIKE operation on the numbers columns [message #662351 is a reply to message #662340] |
Wed, 26 April 2017 01:09 |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
Database version = 12.1.0.2.0
it created successfully.
But it get failed when I refresh the MV.
ORA-12008: error in materialized view refresh path
ORA-01756: quoted string not properly terminated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
and my MV refreshed script as follows:
BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => 'refresh_ID'
,METHOD => 'C'
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 0
,PARALLELISM => 0
,ATOMIC_REFRESH => FALSE
,OUT_OF_PLACE => TRUE
,NESTED => FALSE);
END;
/
it will get refreshed successfully if I set the OUT_OF_PLACE = FALSE.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
