Home » SQL & PL/SQL » SQL & PL/SQL » Converting long datatype to its equivalent
Converting long datatype to its equivalent [message #394718] Sun, 29 March 2009 21:24 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Im having some problem inserting a row with long datatype to a table.

But got it working using

copy from REMOTE_USER/REMOTE_USER_PASSWORD@remotedb -
insert testtab using select * from testtab


Since this table will be used in transformation, data might be moved from one table to another, so i thought of converting this datatype to its equivalent (lob or something equivalent)

Im not sure what is the best datatype to replace long - maybe lob/clob. And if theres any limitation doing so.
Re: Converting long datatype to its equivalent [message #394730 is a reply to message #394718] Sun, 29 March 2009 23:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Longs should be converted to clobs. You can use TO_LOB.
Re: Converting long datatype to its equivalent [message #394743 is a reply to message #394730] Mon, 30 March 2009 00:53 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you barbara,

Do you see any limitation working with clob datatypes?

The only once i saw with long datatype is its quite difficults to move data from table to table using normal insert or ctas
Re: Converting long datatype to its equivalent [message #394770 is a reply to message #394743] Mon, 30 March 2009 04:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
LOBS are in all ways superior to LONGS.

There is a package called DBMS_LOB to handle complex LOB manipulation, and in 10g, standart string manipulation functions like LENGTH and SUBSTR are overloaded to handle CLOBS, making their integration into PL/SQL almost seamless:
create table test_163 (col_1 clob);

declare
  v_str   varchar2(32767);
  v_clob  clob;
begin
  for i in 1..3276 loop
    v_str := v_str || '0123456789';
  end loop;
  
  v_clob := to_clob(v_str)||to_clob(v_str);
  insert into test_163 values (v_clob);
end;
/

select length(col_1) from test_163;

select substr(col_1,60000,5) from test_163;
Re: Converting long datatype to its equivalent [message #394795 is a reply to message #394770] Mon, 30 March 2009 06:29 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you for reply.

Just a little confused.

Now lets say i have tableA with one column having long datatype.

So i want to transfer data from tableA to tableB.

tableB should have lob or clob datatype? and i shall use to_lob to convert it?
Re: Converting long datatype to its equivalent [message #394815 is a reply to message #394795] Mon, 30 March 2009 07:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
TableB must have either a LONG OR CLOB data type.
CLOB is the better choice, and is the one Oracle recommend, but it's up to you in the end.

TO_LOB is the function to use to convert LONG columns to CLOBs

Re: Converting long datatype to its equivalent [message #394821 is a reply to message #394815] Mon, 30 March 2009 07:29 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you for the crytal clear note
Re: Converting long datatype to its equivalent [message #394942 is a reply to message #394795] Mon, 30 March 2009 20:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
ajitpal.s wrote on Mon, 30 March 2009 04:29

tableB should have lob or clob datatype?



There is no "lob" datatype. There are "long", "long raw", "clob", and "blob" datatypes and others. "Lob" stands for "large object". "Clob" stands for "character large object". "Blob" stands for "binary large objects". "clob" and "blob" are types of "lob", but there is no "lob" datatype. "Long" datatypes should be converted to "clob" datatypes and "long raw" datatypes should be converted to "blob" datatypes. "To_lob" is the function that is used to do these conversions.
Re: Converting long datatype to its equivalent [message #394958 is a reply to message #394942] Mon, 30 March 2009 21:33 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you again for the clear explanation, got it
Previous Topic: definers rights
Next Topic: Operations within rows returned by query
Goto Forum:
  


Current Time: Mon Dec 05 21:17:40 CST 2016

Total time taken to generate the page: 0.12917 seconds