Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to convert CLOB data into number and compare it to other table.

Status
Not open for further replies.

EM1107

IS-IT--Management
Apr 24, 2002
153
CA
Good day Everyone.
As you probably guess I am not the best in Oracle but I have to perform a task where I require to compare data in one table for the same data in an other table but the data I require to compare is a CLOB in the first table and a number in an other table.

So on my table 1 the CLOB value is actually a number that we can compare to integer in SQL Server and we have a valid number that should match that number in a different table.

I need to find out what value exist in that CLOB table that does not exist in my other table.

To do that I would like to convert the CLOB data to number to match the proper value and than see if the value does exists in the other table.

I tried to run the following script to insert the columns I want to compare into a temporary table that would have the values as NUMBER instead of CLOB data.
CREATE TABLE TEMP_TABLE AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID');

and I get the following error:
Error starting at line : 6 in command -
CREATE TABLE TEMP_REG AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID')
Error report -
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Would anyone have an idea as to how I could do that?
If I could simply convert the value and then compare it as a number that would be better than having to export the data to an other table but it would give me more flexibility once converted.

Thanks in advance and any help would be appreciated.
 
Looks like someone suggested:[tt]
CAST(CAST(clob_field AS VARCHAR2(200)) AS NUMBER(10))[/tt]
or here:[tt]
select to_number(to_char(clob field name here));[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
why not use DBMS_LOB.COMPARE function or the dbms_crypto_toolkit.Hash


Bill
Lead Application Developer
New York State, USA
 
On our installation a simple TO_NUMBER(clob_field) works.
Your results may vary.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top