Home » SQL & PL/SQL » SQL & PL/SQL » NOT LIKE operation on the numbers columns (Oracle 12c)
NOT LIKE operation on the numbers columns [message #662061] Sun, 16 April 2017 21:54 Go to next message
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 #662062 is a reply to message #662061] Sun, 16 April 2017 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 25352
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

How can we reproduce what you report?

We can't say what you did wrong since you decided to not show us exactly what you did do.
Re: NOT LIKE operation on the numbers columns [message #662064 is a reply to message #662061] Mon, 17 April 2017 00:23 Go to previous messageGo to next message
John Watson
Messages: 6896
Registered: January 2010
Location: Global Village
Senior Member
You say that your predicate is this
 where ID_1 NOT LIKE '99999%' 
and also Quote:
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 #662065 is a reply to message #662064] Mon, 17 April 2017 01:41 Go to previous messageGo to next message
ora_newbie111
Messages: 11
Registered: January 2009
Junior Member
I have tried to use the to_char function but same error still hit.

select * from tableA where to_char(ID_1) NOT LIKE '99999%' and cola = 'J'.

Re: NOT LIKE operation on the numbers columns [message #662066 is a reply to message #662065] Mon, 17 April 2017 02:01 Go to previous messageGo to next message
John Watson
Messages: 6896
Registered: January 2010
Location: Global Village
Senior Member
How about showing what you are actually doing?
Re: NOT LIKE operation on the numbers columns [message #662069 is a reply to message #662065] Mon, 17 April 2017 06:44 Go to previous messageGo to next message
EdStevens
Messages: 751
Registered: September 2013
Senior Member
ora_newbie111 wrote on Mon, 17 April 2017 01:41
I have tried to use the to_char function but same error still hit.

select * from tableA where to_char(ID_1) NOT LIKE '99999%' and cola = 'J'.

It is still completely illogical to try to do a 'like' comparison on a column that is a number, even with proper use of to_char. Comparisons to numbers should be 'less than' or 'greater than' or 'equal to'.

What are you really trying to accomplish, and why do you think a 'like' comparison on a number is appropriate?
Re: NOT LIKE operation on the numbers columns [message #662076 is a reply to message #662069] Mon, 17 April 2017 09:19 Go to previous messageGo to next message
ora_newbie111
Messages: 11
Registered: January 2009
Junior Member
What I am trying to do is I would like to exclude the data being selected with ID_1 which is starting with first 4 character = 9999. 
Re: NOT LIKE operation on the numbers columns [message #662080 is a reply to message #662076] Mon, 17 April 2017 12:31 Go to previous messageGo to next message
joy_division
Messages: 4727
Registered: February 2005
Location: East Coast USA
Senior Member
Is ID_1 always the same length?

If yes, then it would be rather easy to use an equality operator.
If no, then it sounds like Id's starting with 9999 define a special type of ID and that VARCHAR2 would have been a better choice for that column.

[Updated on: Mon, 17 April 2017 12:33]

Report message to a moderator

Re: NOT LIKE operation on the numbers columns [message #662087 is a reply to message #662061] Mon, 17 April 2017 15:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8708
Registered: November 2002
Location: California, USA
Senior Member
The error stack indicates that the problem is a missing quotation mark:

ORA-01756: quoted string not properly terminated

Instead of typing a piece of what you think you ran, you need to post a copy and paste of an actual run of creation of the materialized view and other associated things, such as materialized view logs and whatever method you are using to refresh it or trigger a refresh, complete with the accompanying error message. It sounds like you are missing a quotation mark on the line that you have commented out. It may be that you have some character that looks similar to a quote that is not actually a quote or some such thing.

Re: NOT LIKE operation on the numbers columns [message #662115 is a reply to message #662087] Tue, 18 April 2017 06:19 Go to previous messageGo to next message
Bill B
Messages: 1583
Registered: December 2004
Senior Member
paste in the MVIEW create script and the output of a DESC on TABLEA. There is no way we can help you with the information you have given.
Re: NOT LIKE operation on the numbers columns [message #662118 is a reply to message #662076] Tue, 18 April 2017 06:40 Go to previous messageGo to next message
EdStevens
Messages: 751
Registered: September 2013
Senior Member
ora_newbie111 wrote on Mon, 17 April 2017 09:19
What I am trying to do is I would like to exclude the data being selected with ID_1 which is starting with first 4 character = 9999. 
So ID_1, even though declared as a NUMBER isn't really a number but simply a code that, by application standard, only uses the numeric characters. Correct?
Re: NOT LIKE operation on the numbers columns [message #662219 is a reply to message #662118] Thu, 20 April 2017 20:52 Go to previous messageGo to next message
ora_newbie111
Messages: 11
Registered: January 2009
Junior Member
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.
Re: NOT LIKE operation on the numbers columns [message #662238 is a reply to message #662219] Fri, 21 April 2017 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 12659
Registered: September 2008
Location: Rainy Manchester
Senior Member
Code tags are for code, not sentences.
Re: NOT LIKE operation on the numbers columns [message #662245 is a reply to message #662219] Fri, 21 April 2017 06:44 Go to previous messageGo to next message
EdStevens
Messages: 751
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 Go to previous messageGo to next message
Bill B
Messages: 1583
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 Go to previous messageGo to next message
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 #662338 is a reply to message #662336] Tue, 25 April 2017 09:45 Go to previous messageGo to next message
cookiemonster
Messages: 12659
Registered: September 2008
Location: Rainy Manchester
Senior Member
See if explicitly to_charing id_1 before you do the like on it helps.
Re: NOT LIKE operation on the numbers columns [message #662340 is a reply to message #662338] Tue, 25 April 2017 10:53 Go to previous messageGo to next message
Bill B
Messages: 1583
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';
compiled and worked correctly. I am running 12.1.0.2.0

[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 Go to previous messageGo to next message
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.
Re: NOT LIKE operation on the numbers columns [message #662352 is a reply to message #662351] Wed, 26 April 2017 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64729
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why didn't you post the CREATE TABLE statement so we will be able to test your EXACT same case?

Re: NOT LIKE operation on the numbers columns [message #662362 is a reply to message #662352] Wed, 26 April 2017 10:47 Go to previous message
Bill B
Messages: 1583
Registered: December 2004
Senior Member
Using OUT_OF_PLACE=TRUE is a new feature in oracle 12c and it might have a bug. Set OUT_OF_PLACE=FALSE and your MVIEW will be fine.
Previous Topic: Issue with DATE
Next Topic: COnditional change Serial
Goto Forum:
  


Current Time: Thu Apr 27 14:25:52 CDT 2017

Total time taken to generate the page: 0.21809 seconds