question

CharlesB avatar image
CharlesB asked

How to extract CLOG data from oracle database and import that data into a number column in a different table.

Good day.

I have an issue where i require to compare data from a CLOG column where a specific data is stored and compare this with a different table. In order to do that I would like to first export the data I want and save it in a different table as a NUMBER value so that I can compare the data properly.

The issue I have is to insert the data in the new created table.

Here is the code I used to try getting the data into the other table.


1- I created the table I want the data into.

CREATE TABLE EXPORT_TABLE (ID NUMBER (30,0), ENTITYID NUMBER(30,0), DATANUMBER NUMBER(30,0));


Than I created the sql to grab the data from the table including the CLOB column as follow:

INSERT INTO EXPORT_TABLE (ID, ENTITYID, DATANUMBER)
VALUES (SELECT ID, ENTITYID, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DATANUMBER
FROM TABLE
WHERE NAME = 'LINKID');


I receive the following error:

Error starting at line : 7 in command -
INSERT INTO EXPORT_TABLE (SYSTEM_ID, ENTITYID, DOCNUMBER)
VALUES (SELECT system_id, entityid, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM registry
WHERE NAME = 'LINKID')
Error at Command Line : 8 Column : 9
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:

If I run the select statement I do get what I want to import but for some reason it does not get added to my table.

What did I missed here.



data-export
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
anthony.green avatar image
anthony.green answered

Not an oracle person, but typically you wouldn't want the values clause, you would just straight insert select


INSERT INTO EXPORT_TABLE (ID, ENTITYID, DATANUMBER)
SELECT 
ID, ENTITYID, 
CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DATANUMBER
FROM TABLEWHERE NAME = 'LINKID';
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CharlesB avatar image
CharlesB answered
Agree but in my case the CLOB table include many rows for one value so I require to extract that value so it has a proper converted value and compare it to the other table I want to look at.

if it was that simple I would simply do select data from that column where it does not exist in the other table column but I still have to convert it as the data I am looking for in the other table is a NUMBER value. I cannot compare a CLOB data directly to a number value.


1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image anthony.green commented ·

Possibly one to put in a dedicated Oracle forum then as this is primarily for Microsoft SQL Server, may get 1 or 2 people knowing Oracle but possibly few and far between online at present.

Sound like something you want to be doing with substrings and splitting the clob into smaller pieces

0 Likes 0 ·
CharlesB avatar image
CharlesB answered

Does not work I get the error ORA-01722: invalid number

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.