Select rows with line breaks: Unexpected result

oracleoracle-12c

I'm trying to find rows in a table where there are line breaks.

I found some sample code that lets me do this:

where dump(f1) like '%,10%' and Not (dump(f1) like '%100%')

However, I'm getting an unexpected result.

create table test1 (f1 varchar(25));

insert into test1(f1) values ('Note');
insert into test1(f1) values ('Nota');   --<-- Slight spelling difference

select * from test1 where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

F1                       
-------------------------
Note

Why does the query return a record, if there isn't a carriage return? And why does it only return a single record?

Prediction: I'm having a brain fart and/or I have no idea what I'm doing. It is Monday morning, after all.

Best Answer

DUMP returns a VARCHAR2.

select f1, dump(f1) from test1;

F1            DUMP(F1)
------------- ---------------------------- 
Note          Typ=1 Len=4: 78,111,116,101
Nota          Typ=1 Len=4: 78,111,116,97

2 rows selected.

So searching for '%,10%' will only return 'Note' because it's dump representation contains 101, where as 'Nota' does not.

If you insert those values with a return character/line break, the result would be:

select  f1, dump(f1)
from test1 where 
dump(f1) like '%,10%' 
and  Not (dump(f1) like '%100%');

F1            DUMP(F1)
------------  -----------------------------------------------
Note          Typ=1 Len=4: 78,111,116,101
Note          Typ=1 Len=5: 78,111,116,101,10
Nota          Typ=1 Len=5: 78,111,116,97,10

You get 3 rows returned. The 2 rows ending with ',10' contain the the line breaks.

Related Solutions

Oracle Table Changes – Capturing Table Changes Without Killing the Server

Just from a logical standpoint you're going to have to have 2 copies of the data, or a log of each change (including the deletes/inserts). You can use the tables/triggers like you said, use flashback query (and that will work on 11g by the way, assuming that all the archive logs for the day are still online-- and, in my experience, it's not that expensive of a task depending on the query)

You can also approach it a little differently, although this method is more complex, it might be faster to some extent, especially if there's some limit to which rows are "in scope"-- meaning, that if you can put some kind of code to ignore the vast majority of the rows in the table (like a rule that they cannot update hours that are more than a month old or something similar).

You create a second table that has all the rows that are "in scope" for the update-- it's just a plain old copy of the other table as it sits right now.

When your job runs, it first deletes records from the table that are "out of scope" and then runs a query that gets the edited/deleted rows..

select t1.a as a1, t2.a as a2, t1.b as b1, t2.b as b2 
from t1
left join t2 on t2.id=t1.id
...

Start building your file with this info Be sure to UPDATE the t2 with the new values (and perhaps delete records that were deleted)

Then you run another that gets the inserted rows Add this info to your file as well Likewise, insert those rows into table 2.

In the end, it's a coinflip on whether or not this is faster-- but at least it'll run when you can schedule it-- but the flashback query is basically the same thing and letting oracle handle it.

It's much more simple and perhaps less error prone to just use flashback query in my opinion.

Why is the query returning an unexpected result

My best guess is that what you are actually trying to query is:

SELECT S.sname
FROM Suppliers S
WHERE S.sid IN (SELECT C.sid
FROM Catalog C, Catalog C1
WHERE C1.pid <> C.pid  AND C1.cost = C.cost);

I changed the subselect to return C.sid, and removed the parts table altogether, since in your original body text you stated no conditions on the table.

Is this what you are looking for?

Best Answer

Related Solutions

Oracle Table Changes – Capturing Table Changes Without Killing the Server

Why is the query returning an unexpected result

Related Question