Was wondering why all the code isn't inside the function. Kinda confused me.

Thanks.

if you are calling just a function, is that loop executed ?
if yes how can we trace it ?
what would be the logical flow ?

if we have signigicantly large string varchar2(4000)
and around 2M rows ?
( I have a comment field which has this kind of funny chars
i want to either remove it or convert it into space
the commets varchar2(4000) !!
)

can we do the same thing ?

what about char like '~!@#$%^&*()'

do we have reverse function for ascii() ?
so that I can just specify the rage and sould opereate on the string ?

Thanks,

Thanks tom and your reply is very fast.

trace meance just a debug but I can do it with dbms_output
so sorry for that question.

thanks again
v--

I used part of this code on a form that validates a user's password. It checks to be sure their password does not include any special characters. Excellent.

Hi Tom,
I have a table like below.

desc t1
Name
-------
S

select S, count(s) from t1
group by S ;


S COUNT(*)
- ----------
A 17
B 78
C 84
F 23
G 30
I 30
O 40
P 24
R 60
S 36
T 108
U 24
W 18

In the above list some characters are missing, since they are not there in table t1 itself.(say letters like D,M,N,O,X,Y,Z). these characters are not at all there in table.
But my requirement is missing chars in the list of A,B,C, D, E...XYZ should display with the count zero.

like
D 0
M 0
N 0
O 0
X 0
Y 0
Z 0

Will it be possible through the SQL query.

Have a great time.

in that "utils" package of yours? :-)

that you've probably got dozens of such 'demos' that would make a great utility package.

Great answer.

We have a number(12,2) columns for *amounts* all over the database. Somehow bad data is stored in these columns and core-dumps or throws null pointer error to java programs.
Eg:

select number_col, dump(number_col, 10)
from table_xxx
where number_col < 1 and number_col > 0.9999999999
/

0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1

select dump(0, 10) from dual
/
Typ=2 Len=1: 128
select dump(0.0) from dual;
Typ=2 Len=1: 128


I have already read how Oracle stores numbers in metalink.
Our developer also has did quite some research from the java side and tried to insert such a value, but failed.
{ zero (0.0) is inserted with bad data, the dumps above show the difference }

Tom,
Could you please clarify how this would happen ? We have been using 9.0.1 and currently running 9.2.0.4 and we have
always kept our jdbc versions upto date.

Here is an excerpt of what the java developer had reported:
===========================================================
These are the details.
A zero is stored in Oracle as -128
A one is stored in Oracle as -63 2 (2 bytes)
This particular digit (in which the null pointer is thrown) is stored as -63 1

Now, Oracle translates dobles through a particularly goofy logic.
This is inside classes12.jar oracle.sql.LnxLibThin class

public byte[] lnxren(double d) throws SQLException {
byte[] is = new byte[20];
int i = 0;
boolean bool = !(d < 0.0);
d = Math.abs(d);
if (d < 1.0) {
for (int i_475_ = 0; i_475_ < 8; i_475_++) {
if (powerTable[i_475_][2] >= d) {
i -= (int) powerTable[i_475_][0];
d *= powerTable[i_475_][1];
}
}
if (d < 1.0) {
i--;
d *= 100.0;
}
} else {
for (int i_476_ = 0; i_476_ < 8; i_476_++) {
if (powerTable[i_476_][1] <= d) {
i += (int) powerTable[i_476_][0];
d *= powerTable[i_476_][2];
}
}
}
if (i > 62)
throw new SQLException(CoreException.getMessage((byte) 3));
if (i < -65)
throw new SQLException(CoreException.getMessage((byte) 2));
boolean bool_477_ = !(d >= 10.0);
byte i_478_ = 8;
int i_479_ = 0;
byte i_480_ = (byte) (int) d;
for (/**/; i_479_ < i_478_; i_479_++) {
is[i_479_] = i_480_;
d = (d - (double) i_480_) * 100.0;
i_480_ = (byte) (int) d;
}
i_479_ = 7;
if (bool_477_) {
if (i_480_ >= 50)
is[i_479_]++;
} else
is[i_479_] = (byte) ((is[i_479_] + 5) / 10 * 10);
for (/**/; is[i_479_] == 100; is[i_479_]++) {
if (i_479_ == 0) {
i++;
is[i_479_] = (byte) 1;
break;
}
is[i_479_] = (byte) 0;
i_479_--;
}
for (i_479_ = 7; i_479_ != 0 && is[i_479_] == 0; i_479_--)
i_478_--;
byte[] is_481_ = new byte[i_478_ + 1];
is_481_[0] = (byte) i;
System.arraycopy(is, 0, is_481_, 1, i_478_);
return NUMBER._toLnxFmt(is_481_, bool);
}

If,
if (i > 62)
throw new SQLException(CoreException.getMessage((byte) 3));
if (i < -65)
throw new SQLException(CoreException.getMessage((byte) 2));

is commented out then this will give -63 1 if a zero goes into this method as an argument. However,
such a thing won't happen because one those code is not commented out and two there is error check before that in classes12.jar oracle.sql.NUMBER
which is the caller to this method:

public static byte[] toBytes(double d) throws SQLException {
if (d == 0.0 || d == -0.0)
return _makeZero();
if (d == Double.POSITIVE_INFINITY)
return _makePosInf();
if (d == Double.NEGATIVE_INFINITY)
return _makeNegInf();
return _getLnxLib().lnxren(d);
}

The makezero returns -128. And hence -63 1 should not go in, and I have not been able to get it in either,
and I did try a lot of permutations.
Now, we can again have a fail safe mechansim to check whether it is -63, 1 and treat it as zero. But
again it does not address why it happened.
==========================================================

Sorry if the information is overwhelming.

We are currently updating these *bad* values to zero on tables that have millions of rows as a work-around.

I have seen this type of data break ASP pages in my previous project.

Can you please provide some insight on how this could
occur and any other solution than updating ?

Tom,
Is there a way to find all special characters that may exist in a clob column. Users have entered special characters into the clob columns by doing a copy/past from MSWord.
Ex. go into MSWord symbol (or character map) and copy/past the fraction 1/3.

Thank you

I want to get this result. Is it possible?

STRING

"No pre-sales question should be answered via Technical Support, all pre-sales suggestions should be considered invalid. Technical Support is not authorized to make product suggestions. "

RESULT

"No pre-sales question should be answered chr(10)
via Technical Support, all pre-sales chr(10)
suggestions should be considered invalid.chr(10)
Technical Support is not authorised to chr(10)
make product suggestions."

I want to loop through a String and replace a space with chr(10) at a specific length of a string.

Thanks TOM

Tom,

Could you simplify the strip_bad function by only using the TRANSLATE function like this?


create or replace package utils2 as
function strip_bad( p_string in varchar2 )
return varchar2;
end;
/

create or replace package body utils2 as

g_bad_chars varchar2(256);

function strip_bad( p_string in varchar2 ) return varchar2 is
begin
return translate( p_string, g_bad_chars, 'a');
end;



begin
g_bad_chars := 'a';
for i in 0..255 loop
if ( i not between ascii('a') and ascii('z') AND
i not between ascii('A') and ascii('Z') AND
i not between ascii('0') and ascii('9') )
then
g_bad_chars := g_bad_chars || chr(i);
end if;
end loop;
end;
/

It seems to return the same results.

Tom-- I think strip_bad could be further simplified.

GANOTEDP\dganote> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE FUNCTION strip_bad (p_string IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 good_string VARCHAR2(62)
5 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
6 'abcdefghijklmnopqrstuvwxyz' ||
7 '1234567890';
8 BEGIN
9 RETURN
10 TRANSLATE (p_string, 'T' ||
11 TRANSLATE ( p_string, '.' || good_string
12 , '.'
13 ) -- find the bad characters
14 , 'T'); -- remove the bad characters
15* END;
16 /

Function created.

GANOTEDP\dganote> select strip_bad('0110-02-000') from dual;

STRIP_BAD('0110-02-000')
--------------------------
011002000

GANOTEDP\dganote> select strip_bad('0110.-()*"'||chr(10)||'~!@#$%^&*()') from dual;

STRIP_BAD('0110.-()*"'||CHR(10)||'~!@#$%^&*()')
--------------------------------------------------
0110

Tom-- Now I see you proposed a similar nested TRANSLATE function at:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:671777767982, <code>

I used the code midified as below:
create or replace package utils
as
function strip_bad( p_string in varchar2 )
return varchar2;
end;
/

create or replace package body utils as

g_bad_chars varchar2(256);
g_a_bad_char varchar2(256);

function strip_bad( p_string in varchar2 ) return varchar2
is
begin
return replace(
translate( p_string,
g_bad_chars,
g_a_bad_char),
chr(127),'');
end;



begin

for i in 0..255 loop
if i not between ascii(' ') and ascii('~')
then
g_bad_chars := g_bad_chars || chr(i);
g_a_bad_char:= g_a_bad_char||chr(127);
end if;
end loop;
end;
/

Exactly this is what i need...Excellent...thanks

Please note that the strip_bad function is ASCII-oriented, as this example shows (the ö character is removed):

SQL> select phrs_txt, strip_bad(phrs_txt) from glsry where phrs_no = 200 and iso_639_3_cd = 'ger';

PHRS_TXT               
-------------------
STRIP_BAD(PHRS_TXT)
-------------------
Strömungsgeschwindigkeit
Strmungsgeschwindigkeit
 

Data in a particular column has a special character equivalent to keyboard press of ALT+0227 (ã).

On the db on windows, the column gets the character required. But on AIX the character gets inserted as a (?) instead of ã.

Is this something to do with how the db is created? If so, please advise how to go about changing the parameters?

Changed the NLS_CHARACTERSET to WE8ISO8859P1 and it worked.
So this should have been set at the time of db creation, right?

We can easily eliminate just the ASCII unprintable control characters, while leaving Unicode and other special characters:

select TRANSLATE ('÷'
, 'T' || CHR(1)||CHR(2)||CHR(3)||CHR(4)||CHR(5)
|| CHR(6)||CHR(7)||CHR(8)||CHR(9)||CHR(10)
|| CHR(11)||CHR(12)||CHR(13)||CHR(14)||CHR(15)
|| CHR(16)||CHR(17)||CHR(18)||CHR(19)||CHR(20)
|| CHR(21)||CHR(22)||CHR(23)||CHR(24)||CHR(25)
|| CHR(26)||CHR(27)||CHR(28)||CHR(29)||CHR(30)
|| CHR(31)||CHR(127) -- all ASCII control characters
, 'T') clean_desc
from dual
/

C
-
÷

Very Good. Thanks

Tom, I was testing your UTILS Package [from top] and just took bad characters part of it ..but when i try to display what's been gathered as bad characters, nothing is coming. it has a blank string..where am i doing wrong :-( ?

declare
g_bad_chars varchar2(256);
g_a_bad_char varchar2(256);

begin
for i in 0..255 loop
if ( i not between ascii('a') and ascii('z') AND
i not between ascii('A') and ascii('Z') AND
i not between ascii('0') and ascii('9') )
then
g_bad_chars := g_bad_chars || chr(i);
end if;
end loop;
/* g_a_bad_char := rpad(
substr(g_bad_chars,1,1),
length(g_bad_chars),
substr(g_bad_chars,1,1));
*/
dbms_output.put_line(g_bad_chars);
End;

I do not see anything in output [and yes, set serveroutput is on :-)]

Thank you..

I could see the output on SQLplus [I was trying it in TOAD and could not see the output].
But this opened up another can of worms for me:-)

When i run this query, I am getting output but for bad characters, it's showing me a list of all those sepcial characters and also,does a repeat of all the characters. Here is an example of what i am talking about [btw, i am using Oracle 9i]


SQL> declare
2 g_bad_chars varchar2(256):='';
3 begin
4 for i in 0..255 loop
5 if ( i Not between ascii('a') and ascii('z') AND
6 i Not between ascii('A') and ascii('Z')
7 )
8 then
9 g_bad_chars := g_bad_chars || chr(i);
10 end if;
11 end loop;
12 dbms_output.put_line(g_bad_chars);
13 End;
14 /

!"#$%&'()*+,-./0123456789:;<=>?@[\]^_`{|}~
!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnop
qrstuvwxyz{|}~

PL/SQL procedure successfully completed.

Also, Why are you using 0..255 [256 ASCII Character check, when there are only 127 {I think :-)}]

Thanks for any suggestions you have on this.

We have a requirement to accept text in multiple languges.

For example, a material may have a name in Thai, Japanese, Simplified Chinese, Traditional Chinese, Korean, and German, and English.

We need to prevent people from accidentally using inappropriate characters in any of these fields. For example, you should not be able to put a Simplified Chinese character in the Traditional Chinese field. This is complicated, of course, by the fact that some characters are used by both Simplified Chinese and Traditional Chinese. Japanese and Korean also both use some Chinese characters. And, of course, all of them use the Western 0-9.

Are there any linguistic functions in Oracle that will let us validate the content of each field?

The most linguistically aware string functions in Oracle seem to be the REGEXP functions, but they don't seem to have the ability to handle this.

Any other options?

From SQL Reference:
ASCII returns the decimal representation in the database character set of the first character of char.
CHR returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set.

What character set are you using?

Regards
Michel

Michel, thanks for responding ..I am using "WE8MSWIN1252" character set. [I have no clue what that is and how it effects my pl/sql code given above but..]

Thanks,

This answer your last question. You use 0 to 255 as this is the range of code points defined in WE8... (actually in WE8SWIN1252 not all these values are used, some are undefined).
ASCII function was extended to support more than ASCII code points. It should be renamed as something like CODE_POINT or CDPT to follow CHR function name.

There is no problem with your PL/SQL procedure. The problem comes from TOAD that badly handles the string you put in dbms_output buffer.

If you execute it in SQL*Plus, you get (with WE8MSWIN1252 and in 10.2 but maybe other and/or incorrect result in other version):

SQL> declare
2 g_bad_chars varchar2(256):='';
3 begin
4 for i in 0..255 loop
5 if ( i Not between ascii('a') and ascii('z') AND
6 i Not between ascii('A') and ascii('Z')
7 )
8 then
9 g_bad_chars := g_bad_chars || chr(i);
10 end if;
11 end loop;
12 dbms_output.put_line(g_bad_chars);
13 End;
14 /



 !"#$%&'()*+,-./0123456789:;<=>?@[\]^_`{|}~¿¿¿¿¿¿¿¿¿¿¿¿¿`¿¿¿¿¿¿¿¿¿¿¿¿Y ¡¢£¤¥¦§¨©ª«
¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ

PL/SQL procedure successfully completed.

Regards
Michel

The output I was getting was from sqlplus and used this in both versions of oracle 9i and 10g [release 2].

I do not see the output you shown above. I am not sure why they are different?

if you have any more information, i'm interested..
Thanks

What is your Oracle db version? What is your db server OS and version?
Same questions for the client.
If you use Windows, do you use DOS or Windows SQL*Plus?
What is your NLS_LANG setting?
What is your client character set (at OS level)?

Spool the result in a file and then dump it to check what you get.

Regards
Michel

Maverick,

Suggest you do some reasonable debugging.

For example, add code to your DBMS_OUTPUT to print ASCII('a'), ASCII('z'), ASCII('A'), ASCII('Z').

Then change your code so that you concate '| ' || i || ':' || chr(i).

Show us that.

Hi Tom,

I am not sure if I am right exactly, but I made a change in the package which you wrote -

changed
from
"g_a_bad_char := rpad(
substr(g_bad_chars,1,1),
length(g_bad_chars),
substr(g_bad_chars,1,1));"
to
"g_a_bad_char := substr(g_bad_chars,1,1);"

and rest of the package stays same.

It gives me the same result.

Although it gives me the same result, I am expecting there could be some reason you have used this above.

Could you please explain me this?

This might make a base for a more generalized function.


create or replace function strip_bad(ar_str varchar2,
ar_good varchar2 default '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
ar_placeholder varchar2 default chr(1))
return varchar2
as
v_result varchar2(4000);
v_char varchar2(1);
v_len number;
begin
v_result := ar_str;
v_len := nvl(length(v_result), 0);
for i in 1..v_len loop
v_char := substr(v_result, i, 1);
if v_char <> ar_placeholder and instr(ar_good, v_char) = 0 then
v_result := replace(v_result, v_char, ar_placeholder);
end if;
end loop;
v_result := replace(v_result, ar_placeholder);
return v_result;
end;

Hi Tom,

Please show us how to eliminate all the other unnecessary characters from the strings using the REGEXP_REPLACE function (available in 10g) except the one listed below.

"Regular_Text should contain only following characters a-z,A-Z,0-9,@,&,$,%,SingleQuote and Underscore."

If possible please do help us to explain how that works.

Regards

Hitesh,

You can use negate [^a-z,_,A-Z,0-9,@,&,$,%,'']
any character that are not allowed will become null.

Frank

SQL> COLUMN str_old FORMAT A30
SQL> COLUMN st_new FORMAT A30

SQL> SELECT str str_old,
2 REGEXP_REPLACE( str, '[^a-z,_,A-Z,0-9,@,&,$,%,'']' , '') AS st_new
3 FROM ( SELECT 'az*^!~:()+=<>@$%_AZ097''' AS str FROM dual);

STR_OLD ST_NEW
------------------------------ ------------------------------
az*^!~:()+=<>@$%_AZ097' az@$%_AZ097'

Thanks Zuran for the answer.

It's been more than 2 weeks since his last post... anyone here any news?

I hope you are just chilling off some where on a vacation, and all is well with you and family.

It is something like this

Select translate(:My_Text, ':'||translate(:My_Text, ':ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', ':'), ':') from dual

Your strip_bad function is great. Is there anyway to use it against all fields in a query?
i.e.
select a,b,c,d,.... from TEST; --The query could have over 100 fields.

Where any of the fields could have invisable chars without doing:
select util.strip_bad(a), util.strip_bad(b).... from TEST;

Something along the lines of:

select util.strip_bad(*) from (select select a,b,c,d,.... from TEST);

In 10g, use the POSIX "character class":
http://www.psoug.org/reference/regexp.html

for example, this lists all 127 ASCII characters and replaces all "unprintable" characters with an underscore:

select TO_CHAR(level,'999')||' '||chr(level) ||' -> '||
REGEXP_REPLACE(
chr(level)
, '[^[:print:]]'
, '_') from dual
connect by level <= 127
/

Please give me some solution

my valid range is 32 - 126 i'm doing a basic ascii for loop compartion for 2Million files selecting the data from table and character by character it's taking almost 1/2 hr

i want to optimize this one!

and also is't safe to do validation for unprintable chars on DB layer ?

@Raj-- "Validation"? That's vague: what do you want to happen when you find something outside the acceptable ASCII range of 32-126?

Thank you for very much for assisting,

Well here is my clear explanation ===>

I'm doing an logic of plsql to check unprintable character for a column say "DATA" on a TABLE by using a bulk collect cursor.
up on any unprintale char if it founds, then it will stop the execution and come out with status=true boolean value.

below is the procedure which I'm calling on every record column data value to validate for unprintable chars, but I want to optimized the below code(it taking app. 15mins for 1 million records to execute the plsql code)

We have a table of 200 million records with once of it's single data column size of 1000 to 4000 characters app. <<worst case>>

Please give me a solution for this.

PROCEDURE unprintablechars(
      p_content IN   VARCHAR2(4000),
      p_status OUT  BOOLEAN)
  IS
      -- variable declaration
      v_str_length   SIMPLE_INTEGER:=0 ;
  BEGIN
       -- Set the default value to false;
       p_status:=FALSE;  
       IF (p_content IS NOT NULL)
       THEN
              -- Get the length of the colContent String
              v_str_length:=LENGTH(p_content);
              -- Check for unprintable characters
           LOOP
       -- If ASCII range out of 32 to 126, then set flag := true
              IF(ASCII(SUBSTR(p_content,v_str_length,1)) < 32
                 OR ASCII(SUBSTR(p_content,v_str_length,1)) > 126)
                      AND ((ASCII(SUBSTR(p_content,v_str_length,1)) <> 13))
                      --excluding carriage return
            THEN
                 p_status:=TRUE;
                 EXIT;
             END IF;
               -- Decrement the strLength to get the substring character of the colContents
               v_str_length:=v_str_length-1;
            -- Exit the loop when the strlength reaches zero
            EXIT WHEN v_str_length=0;
            -- End the loop logic
       END LOOP;
    END IF;
END unprintablechars;

Thank you once again to TOM @ ORACLE :)

ops$tkyte%ORA11GR2> create or replace view goodchars
  2  as
  3  select ' '||max(replace(sys_connect_by_path( ch, ' ' ),' ', '' ) ) chars
  4    from (
  5  select chr(case when level = 95 then 13 else level+32 end) ch, rownum r
  6    from dual
  7  connect by level <= 126-32+1
  8         )
  9    start with r = 1
 10  connect by prior r+1 = r
 11  /

View created.

Elapsed: 00:00:00.02
ops$tkyte%ORA11GR2> select * from goodchars;

CHARS
-------------------------------------------------------------------------------
 !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmn
opqrstuvwxyz{|}~


Elapsed: 00:00:00.01

<b>that was a bit of technique (also known as trickery) to generate your string of acceptable characters...</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t;

Table dropped.

Elapsed: 00:00:00.03
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t ( x varchar2(20) );

Table created.

Elapsed: 00:00:00.01
ops$tkyte%ORA11GR2> insert into t values ( 'hello world' );

1 row created.

Elapsed: 00:00:00.02
ops$tkyte%ORA11GR2> insert into t values ( 'hello ' || chr(127) || ' world' );

1 row created.

Elapsed: 00:00:00.00

<b>one good string, one bad string...</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select x, nvl2( replace( translate( x, (select chars from goodchars), 'x' ), 'x', '' ), 'Bad', 'Fine' )
  2    from t;

X                    NVL2
-------------------- ----
hello world          Fine
hello  world        Bad

Elapsed: 00:00:00.01

<b>and we can see that... Here is how it works.

We take your string X, first argument to translate

We get the string of good characters and that is the second argument.

The last argument is just 'x'.  This will have the effect of turning ' ' into 'x', and then every other character in the good characters string into nothing (they disappear).

Then we replace 'x' with nothing - the x's disappear.

Then, using nvl2, we just see if we are null or not.  If null - good, if not something was leftover and it must be bad</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select count( case when flag = 'Fine' then 1 end ) good_cnt,
  2         count( case when flag = 'Bad' then 1 end ) bad_cnt
  3    from ( select object_name, nvl2( replace( translate( object_name, (select chars from goodchars), 'x' ), 'x', '' ), 'Bad', 'Fine' ) flag
  4             from big_table.big_table )
  5  /

  GOOD_CNT    BAD_CNT
---------- ----------
   1000000          0

Elapsed: 00:00:02.22

<b>that be a 1,000,000 row table - on my laptop.</b>


ops$tkyte%ORA11GR2> update big_table.big_table
  2  set object_name = substr( object_name, 1, 29 ) || chr(128)
  3  where rownum <= 100;

100 rows updated.

Elapsed: 00:00:00.00
ops$tkyte%ORA11GR2> select count( case when flag = 'Fine' then 1 end ) good_cnt,
  2         count( case when flag = 'Bad' then 1 end ) bad_cnt
  3    from ( select object_name, nvl2( replace( translate( object_name, (select chars from goodchars), 'x' ), 'x', '' ), 'Bad', 'Fine' ) flag
  4             from big_table.big_table )
  5  /

  GOOD_CNT    BAD_CNT
---------- ----------
    999900        100

Elapsed: 00:00:02.04

Thanks a lot..!

It optimized upto some extent but i fill bit more time it's taking for 09 mins for 2.4 Million files on data of each row cosist of a minimum 100 characters to 4000 app.
--> tested using my laptop, SQL Tools

One more concern is't possible to to stop counting the bad character if any one bad character found it should stop counting or logic so that it may save some time..

Here i'm trying to validate the given data and has to responsed weather it's having proper data then it will allowed to do some other operation on it otherwise it should stop it basing on a boolean flag in a procedure!.

executed result:

PL/SQL block, executed in 09:25.693 (565.693 sec.)
Total no. of records : 2458815
Unprintable chars present in the file
Total execution time 09:26.005 (566.005 sec.)

Thanks a lot for giving me the good solution! AWESOME!

select * from (
select object_name,
    nvl2( replace( translate( object_name,
              (select chars from goodchars), 'x' ), 'x', '' ), 'Bad', 'Fine' ) flag
      from big_table.big_table
) where flag = 'Bad' and ROWNUM=1; 

By the by Can you clarify me one doubt,

We are actually storing the entire file content into db rows, will it be faster way can we handle the validation check or else using java on files is faster to handle the validation check ?

Thanks. My special character problem solved