Home » SQL & PL/SQL » SQL & PL/SQL » updating a LONG column (merged) (Oracle 10.2g)
updating a LONG column (merged) [message #427796] Sun, 25 October 2009 11:23 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi All

I am working on Oracle10g DB and stuck on the solution which is nothing but the limitation of Oracle.
Please help me to overcome this condition

Issue I am facing is described below

I have 1 table (say A) having "LONG" data type column and I have to update that column based on some condition but as you all know that to apply functions on "LONG" data type column is not permitted thus I imported that data in other table in "CLOB" column and apply the changes in that table (say B).

Now the table B is having the latest data which needs to updated in the original table (A) but when I try to update original table (A) using "UPDATE" command, I got an Oracle error i.e. PL/SQL: numeric or value error string (which says that the value can't exceeds 4000 chars).

Table Structure:

Tab A:

ID Number
TEXT LONG

Tab B:

ID Number
TEXT CLOB

Please do let me know if anyone has any concern.

Regards
Rajat
Re: Update long column [message #427797 is a reply to message #427796] Sun, 25 October 2009 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop the original table and keep only the latest one and NO MORE use LONG.

It can't be done in PL/SQL as soon as data are biggest than 32K. You have to write an external program.

Regards
Michel
Re: Update long column [message #427813 is a reply to message #427797] Sun, 25 October 2009 23:37 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Michel

Thanks for the quick response.
As you mentioned that it can't be achieved in pl/sql and have to write external program, can you please provide a snippet of the program or some idea relatd to that program which can solve the problem.

Regards
Rajat
Re: Update long column [message #427816 is a reply to message #427813] Mon, 26 October 2009 00:19 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

You can acheive this thru VB. You can find examples how to read and write LOB's in your OracleInstallationfolder\oo4o\VB\SAMPLES\LOB on windows.

Thanks and Regards,

MSMallya
Re: Update long column [message #427817 is a reply to message #427813] Mon, 26 October 2009 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends of the language but the plan is:
1/ read CLOB in a variable
2/ update LONG from this variable.
maybe chunk by chunk.

Regards
Michel
Re: Update long column [message #427833 is a reply to message #427817] Mon, 26 October 2009 02:15 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Michel

I tried to resolve the issue by doing that only but once the data exceeds 32K in the variable only then also the error comes.
Steps I followed:

1. Store the data in a variable in chunks (e.g. 39000 chars)
2. Try to update the LONG column by that variable. An error starts come i.e. PL/SQL: numeric or value error string (which says that the value can't exceeds 4000 chars).

If I try to update the column in chunks data even then also I get an error.
Below is the statement I execute to update the column

UPDATE <TABLE_NAME>
SET COLUMN_NAME = <COLUMN_NAME>||<VARIABLE_NAME>
WHERE ID = VAR_ID;

That operation also through an error i.e.
ORA-00932: inconsistent datatypes: expected NUMBER got LONG.

Note : COLUMN_NAME is a "LONG" datatype column.


Regards
Rajat
Re: Update long column [message #427845 is a reply to message #427833] Mon, 26 October 2009 03:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

PL/SQL: numeric or value error


It can't be done in PL/SQL.

You have to to it in C, VB, Java, .....etc...

Here would be the Java example, for example.

Unable to insert the rows [message #428527 is a reply to message #427796] Wed, 28 October 2009 12:12 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi All

I am facing a problem while inserting the data in a column of data type "LONG" of table A from the column of data type "CLOB" (length 8533 chars) of table B by using the pl/sql block in which I use table type concept to store the data up to 32000 chars and then insert that in the table A.
But only 8000 chars got inserted when I tried to insert the data in chunks.

Note: When I tried to insert the same set of data in a column of data type "LONG" of table C from the LONG data type of table A, it completed successfully by using the same pl/sql block.

Can anyone tell why the problem was coming when trying to insert the data of CLOB in LONG data type column.

Error coming: ORA-06502:PL/SQL: numeric or value error string

Regards
Rajat
Re: Unable to insert the rows [message #428531 is a reply to message #428527] Wed, 28 October 2009 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/427796/136107/#msg_427796
Re: Unable to insert the rows [message #428532 is a reply to message #428527] Wed, 28 October 2009 12:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Did you really think the answer would change in a week? How?

Oracle releasing a patch that added LONG support again after they told people for at least 10 years that LONG support would be dropped?
Re: Unable to insert the rows [message #428540 is a reply to message #428532] Wed, 28 October 2009 13:20 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Thomas

I am sorry but that's not the answer of my question.
This time I am facing some other issue which I discussed in my post. Please reply of the fix of the problem if you do have.

Thanks

Regards
Rajat
Re: Unable to insert the rows [message #428542 is a reply to message #428540] Wed, 28 October 2009 13:27 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. You have written some code. There is a numeric or value error in your code.

Since I can't see your code, I suspect there is a 99% probability it is in regards to a LONG column. That's about all I can guess.

Also what does "Insert data in chunks" mean exactly?
Previous Topic: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared
Next Topic: Max Time for the Max date
Goto Forum:
  


Current Time: Tue Dec 06 12:32:05 CST 2016

Total time taken to generate the page: 0.19009 seconds