Home » SQL & PL/SQL » SQL & PL/SQL » help requred (oracle 10G apex)
help requred [message #362014] Sat, 29 November 2008 22:11 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

I know all the wizards are in holiday mood being a sunday. If i have luck enough and someone throws solution to my below mentioned problem, it will reduce the stress and physical presence on viewing the data for detection.

I have one table which has 50000 rows of 15 columns each and all columns are varchar2. In few columns data contains double quotes, single quote and comma as part of the data. Converting the text data to table i struggled and converted in oracle 10 G. But this table required in Oracle 7.3. I did the exercise of converting the text data to table. enormous errors have occured due to comma delimeter and other few things. I think i can handle all these things. My major problem is few special characters are located in some column like Line feed page break and control L and small rectangle.

In all the columns data, special characters need to be located and simply can be inserted as space. How to locate the special characters. Here i intend to say special characters mean, which are not directly located in physical keyboard.

Any clue will reduce browsing the text data line by line to locate the special characters.

yours
dr.s.raghunathan
Re: help requred [message #362021 is a reply to message #362014] Sun, 30 November 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Almost 300 posts and you don't how to post!
- useful title
- correct version, is this 10g or 7.3?
- test case
- what you already tried
- searching: locating control charaters has been addressed during the last 2 weeks.

In addition, if the purpose is just to convert control character to space, a TRANSLATE expression makes the trick.

Regards
Michel

[Updated on: Sun, 30 November 2008 00:28]

Report message to a moderator

Re: help requred [message #362047 is a reply to message #362021] Sun, 30 November 2008 04:40 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi micheal,





Quote:

Almost 300 posts and you don't how to post!


i do not claim i am an expert/inteligent/wizard and after all a human being and makes a cry while faceing the ache. that's all
Quote:

- useful title


locating control characters is right title still i consider immediate help hence i stated help required.
Quote:

- correct version, is this 10g or 7.3?


even after reading the text follows you require version.
Converting the text data to table i struggled and converted in oracle 10 G. But this table required in Oracle 7.3. i do not have problem of control characters in 10 G and while doing so in 7.3 only i face this problem. Let me know what version you will specify for this type of requirement?

Quote:

- test case


to locate the special characters. Here i intend to say special characters mean, which are not directly located in physical keyboard. i do not know how to make test case for this.

Quote:

what you already tried
- - searching: locating control charaters has been addressed during the last 2 weeks.




this is the only valid / useful suggestion made by you. I should have searched locating special characters in the column. That did not strike my mind. Thanks

Quote:

In addition, if the purpose is just to convert control character to space, a TRANSLATE expression makes the trick.



Michel too has done reading in between lines. I wonder. After locating only i have to use traslate or replace.

Now I have learnt one thing.. No person is allowed to make a emotional cry on feeling the pain especially in orafaq forum.

Thanks a lot Mr.Michel for valuable lesson and to your sarcastic comments.

yours
dr.s.raghunathan





Re: help requred [message #362050 is a reply to message #362047] Sun, 30 November 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't understand the point.

Regards
Michel

[Updated on: Sun, 30 November 2008 06:14]

Report message to a moderator

Re: help requred [message #362068 is a reply to message #362050] Sun, 30 November 2008 09:31 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hello Raghunathan,

As per Mr.Michel; Still i'm not able to understand your question.

I guess; If it's 10g you want to find special character.

Here

Babu
Re: help requred [message #362082 is a reply to message #362014] Sun, 30 November 2008 12:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


This can be done using the TRANSLATE as per Michel's suggestion.

SQL> SELECT TRANSLATE( 'ABCD',CHR(66)||CHR(68),'XY') new_str FROM DUAL;

NEW_
----
AXCY

SQL>


You can refer the ASCII value list for the special characters.

Smile
Rajuvan.
Re: help requred [message #362087 is a reply to message #362014] Sun, 30 November 2008 13:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You can identify the invalid characters by selecting their ascii values based on those that do not match a supplied list of characters, then you can use the results of that with update and translate to replace everything containing those characters with spaces. Please see the demonstration below. This works for currently supported versions. I have no idea whether it will work in outdated versions or why anyone would be using such an outdated version.

-- starting data:
SCOTT@orcl_11g> DESC your_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 YOUR_FIRST_COLUMN                                  VARCHAR2(15)
 YOUR_SECOND_COLUMN                                 VARCHAR2(15)

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

        ID YOUR_FIRST_COLU YOUR_SECOND_COL
---------- --------------- ---------------
         1 test*ing        ANOTHER~ONE
         2 line            and
           feed            again

         3 SOME"THING      else=where


-- identify invalid characters:
SCOTT@orcl_11g> SELECT *
  2  FROM   (SELECT ascii_value, character
  3  	     FROM   (SELECT ROWNUM AS ascii_value,
  4  			    CHR (ROWNUM) AS character
  5  		     FROM   DUAL
  6  		     CONNECT BY LEVEL <= 256)
  7  	     WHERE  INSTR
  8  		      -- list all allowable characters:
  9  		      ('ABCDEFGHIJIKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz.1234567890',
 10  		       character) = 0)
 11  WHERE  EXISTS
 12  	    (SELECT *
 13  	     FROM   your_table
 14  	     WHERE  INSTR (your_first_column, character) > 0
 15  	     OR     INSTR (your_second_column, character) > 0
 16  	     -- and so on like the above for each additional column
 17  	    )
 18  /

ASCII_VALUE C
----------- -
         10
         34 "
         42 *
         61 =
        126 ~


-- replace invalid characters with spaces based on ascii values previously obtained:
SCOTT@orcl_11g> UPDATE your_table
  2  SET    your_first_column =
  3  	      TRANSLATE
  4  		(your_first_column,
  5  		 CHR(10) || CHR(34) || CHR(42) || CHR(61) || CHR(94) || CHR(126),
  6  		 '	'),
  7  	    your_second_column =
  8  	      TRANSLATE
  9  		(your_second_column,
 10  		 CHR(10) || CHR(34) || CHR(42) || CHR(61) || CHR(94) || CHR(126),
 11  		 '	')
 12  	    -- and so on like the above for each additional column
 13  /

3 rows updated.

SCOTT@orcl_11g> -- ending data:
SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

        ID YOUR_FIRST_COLU YOUR_SECOND_COL
---------- --------------- ---------------
         1 test ing        ANOTHER ONE
         2 line feed       and again
         3 SOME THING      else where

SCOTT@orcl_11g>

[Updated on: Sun, 30 November 2008 13:05]

Report message to a moderator

Re: help requred [message #362094 is a reply to message #362087] Sun, 30 November 2008 19:24 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi barbara,

exactly this is the solution i thought and i wrote procedure where i find the column length and through loop routine to find ascii characters. It worked fine for my own test data. whereas when i run the the real data which has been downloaded from csv format to 10 G (no problem of special characters existance) . since i need to upload data to oracle 7.3 database sqlldr need to avoid special characters. There are 50,000 records. I use the procedure exactly what you written to locate the special characters. It goes on displaying the 3000 and some odd number of records. suddenly without error message the procedure gets aborted as if ctrl+c has been pressed. I searched the block where the proximity of the special characters with visual eyes. i do not find any special characters. Can there be any special characters loaded in the data as part of data which causes the aborting the program itself.
yours
dr.s.raghunathan
Re: help requred [message #362096 is a reply to message #362094] Sun, 30 November 2008 20:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
What you are saying is unclear and seemingly contradictory.

What I have provided does not involve either a procedure or a loop, just one query (select statement) to find the characters and one update statement to replace them with spaces. The usage of the where exists clause causes it to stop at the first occurrence. Of course it would be horribly slow if you were to loop through every character of every value of every column, so don't do that.

You had said that you had already loaded the data and were now trying to remove the special characters after the load, but now you mention SQL*Loader. Are you trying to remove the characters while running SQL*Loader or afterwards?

You need to provide a copy and paste of a run of whatever it is that you are running, preferably similar to what I provided, complete with line numbers and any errors received.

If you have something that runs on a small data set, but not on a larger one, then you need to determine whether the problem is the data or the size. To do this, you need to determine where the problem occurs and test your code on just those rows. If you can process those rows alone, then the problem is the size and you need to increase the allocations of your system if possible and/or process in smaller chunks. For example, if you have some sort of unique id or date, you can process a chunk at a time based on those.



Re: help requred [message #362101 is a reply to message #362096] Sun, 30 November 2008 22:35 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi barbara,

what you did on simple select statement i achieved through writing procedure since lack of knowledge.

I received a xls file and i converted using apex utility to convert into a table on oracle 10G. Then the same data required in oracle 7.3 environment. Hence I created dmp of that particular file and send it. On 7.3 environment they have imported the data of the same DMP. They could not achieve it. Hence they required to supply as text file with delimeter , so that they (oracle 7.3) will use sqlldr and write the script to convert the text to to their table. Hence I converted once again the data loaded in Oracle 10G table as text file with delimeter , and text field with embeded '. Now they are unable to load the text data in their table and communicating back that there are some special characters in the data source. Hence I thought of locating all the special character and convert it through space character.

Anyway, it seems some lead of your message creeps in my mind and will try once again at home and will come back still problem persists.

Reply was made from office..


yours
dr.s.raghunathan
Re: help requred [message #362246 is a reply to message #362047] Mon, 01 December 2008 07:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
dr.s.raghunathan wrote on Sun, 30 November 2008 05:40

locating control characters is right title still i consider immediate help hence i stated help required.



So when someone else is stuck with the same problem and does a search to see if the question was already asked, they will not find this one.

On the other hand, when someone searches for "help required," thousands of completely unrelated topics will be returned.

Maybe now you will understand the purpose of a useful title?
Re: help requred [message #362249 is a reply to message #362246] Mon, 01 December 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Maybe now you will understand the purpose of a useful title?

If he didn't understand it after 300 posts, what is the likelyhood he will in 1000 posts?

Regards
Michel
Re: help requred [message #362307 is a reply to message #362249] Mon, 01 December 2008 14:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Chances are that Oracle 7.3 was installed using USASCII7 database characterset (USA ASCII 7 bit). Theoretically that means no extended ascii characters (128-255) would be able to ce stored in the DB. In reality, it would probably store any byte value up to 255, but wouldn't necessarily garantee how character 128-255 would be hanled by function, sqlloader, sqlplus or whatever.

In the US, Excel (on Windows) typically uses Microsoft 1252 codepage (codepage and characterset are the same thing). MS-1252 makes use of the range 128-159 to represent characters like opening and closing single/double quotes, Euro sign etc that don't exist in many other chactersets and Oracle 7.3 almost certainly wouldn't support MS-1252 (I don't think).

On Unix, "tr" is good for stripping out characters you don't want. http://www.softpanorama.org/Tools/tr.shtml

To see non-printing characters, you can use "cat -v input_file.txt" (do a man on it).

od (octal dump) is also useful for seeing bad characters on Unix. An easier way however will be to just use a open your flat file (the one you are feeding to sqlldr) using a regular programming hex editor and.

my_dev>>
#Test file has bad characters that can't be printed
cat bad_char.txt   
aaaabbbb

# show non-printable chars
my_dev>>cat -v bad_char.txt
aaaa^A^Fbbbb

#examine the individual bytes (easier to just use hex editor)
my_dev>>od -x bad_char.txt 
0000000 6161 6161 0106 6262 6262 0a00
0000013

#As per link above, convert non-printing characters to '?'

my_dev>>cat bad_char.txt | tr -c '[:print:][:cntrl:]' '[?*]'
aaaa??bbbb
Re: help requred [message #362334 is a reply to message #362307] Mon, 01 December 2008 21:04 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

the oracle 7.3 was loaded in unix environment whereas i am 10G in PC windows xp environment. I suggested your post to those people of unix to try removing control characters and try load. they did it and successfully removed the control characters and loaded the data in oracle 7.3. previously failure at 3000 th record. But now after removing control characters 98% success rate and 1475 records alone failed on insert. They are at it for finding the problem. I will not be able to bring the source to open forum due to confidentiality they maintain.

Thanks to everyone for providing solutions. Extremely sorry for any outburst made at wrong path.

@Michel

sorry

once again thank you very much all
yours
dr.s.raghunathan
Previous Topic: Spooling Output Issue
Next Topic: Strange requirement!
Goto Forum:
  


Current Time: Tue Dec 06 08:46:46 CST 2016

Total time taken to generate the page: 0.14307 seconds