OraFAQ Forum: SQL & PL/SQL » updating a LONG column (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » updating a LONG column (merged) (Oracle 10.2g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

updating a LONG column (merged) [message #427796]

Sun, 25 October 2009 11:23

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

Report message to a moderator

Re: Update long column [message #427797 is a reply to message #427796]

Sun, 25 October 2009 11:27

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Update long column [message #427813 is a reply to message #427797]

Sun, 25 October 2009 23:37

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

Report message to a moderator

Re: Update long column [message #427816 is a reply to message #427813]

Mon, 26 October 2009 00:19

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

Report message to a moderator

Re: Update long column [message #427817 is a reply to message #427813]

Mon, 26 October 2009 00:20

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Update long column [message #427833 is a reply to message #427817]

Mon, 26 October 2009 02:15

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

Report message to a moderator

Re: Update long column [message #427845 is a reply to message #427833]

Mon, 26 October 2009 03:14

ThomasG
Messages: 3212
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.

Report message to a moderator

Unable to insert the rows [message #428527 is a reply to message #427796]

Wed, 28 October 2009 12:12

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

Report message to a moderator

Re: Unable to insert the rows [message #428531 is a reply to message #428527]

Wed, 28 October 2009 12:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/m/427796/136107/#msg_427796

Report message to a moderator

Re: Unable to insert the rows [message #428532 is a reply to message #428527]

Wed, 28 October 2009 12:24

ThomasG
Messages: 3212
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?

Report message to a moderator

Re: Unable to insert the rows [message #428540 is a reply to message #428532]

Wed, 28 October 2009 13:20

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

Report message to a moderator

Re: Unable to insert the rows [message #428542 is a reply to message #428540]

Wed, 28 October 2009 13:27

ThomasG
Messages: 3212
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?

Report message to a moderator

Previous Topic:	How to resolve the problem of : identifier 'DBMS_LOCK' must be declared
Next Topic:	Max Time for the Max date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 20:58:04 CDT 2025