long||varchar

[Jani]

Select long||varchar from tablename;
when I select the error occurs, how can I select this data ?
Thanx

[jrpm]

What is the error you are receiving?

anyway, try using 'set long 32768' and 'set arraysize 1' in your login.sql file or SQL*PLUS file. Oversized varchars and longs tend to be unretrievable, but if they are small enough, these two could do it.

[amar]

Can you concat a long with a varchar2?

I think you will receive an inconsistent datatype error message from oracle

-amar

[jmodic]

Originally posted by jrpm
What is the error you are receiving?

anyway, try using 'set long 32768' and 'set arraysize 1' in your login.sql file or SQL*PLUS file. Oversized varchars and longs tend to be unretrievable, but if they are small enough, these two could do it.

No, this won't do. There is no way you can perform concatenation or any other operation/function on LONG column in SQL. You'll get ORA-932 "inconsistent datatypes" error as amar has allready pointed out.

But if values stored in LONG column are smaller than 32K then you can do it in PL/SQL, because in PL/SQL VARCHAR2 can be 32K in size and you can read long datatype in VARCHAR2 variable. Once you have your long column datata in a VARCHAR2 variable you can do any operation/transformation on it.

[pipo]

note that you can also use a long variable :


declare
var long;
begin
select long_column
into var
from table
where ...;
dbms_output.put_line(var || ' concatened with text');
end;


[jrpm]

Sorry, misread the original.

For some reason my brain interpreted the pipe as an 'or', not as concatenate. Implication: problem retrieving large fields.

Fifty lashes with a wet noodle.

[jmodic]

Originally posted by pipo
note that you can also use a long variable :


declare
var long;
begin
select long_column
into var
from table
where ...;
dbms_output.put_line(var || ' concatened with text');
end;


Yes, but the question is why would you want to use LONG instead of VARCHAR2 type in PL/SQL. Namely, VARCHAR2 variable can store longer strings than LONG in PL/SQL!!! Actualy, in PL/SQL LONG datatype barely deserves its name - its maximum size is 32760 bytes, while VARCHAR2 has limit of 32767!