How to select data(first 10 charcters) from a LONG datatype column.? [message #434864] |
Fri, 11 December 2009 06:00  |
DBA_SangramKeshari
Messages: 44 Registered: October 2009 Location: Mumbai
|
Member |
|
|
Hi,
I am getting below error wile i am trying to select forst 10 charcter from a log datatype column. May be I am using it in a wrong way using SUBSTR function.
I dont know whether SUBSTR works for LONG columns or not as it's char function.
I am not able to find any inbuild conversion function which will work like TO_CHAR.Please revert.
My steps:-
=================================================
SQL> create table long_varchartest(
2 nirav long, sangram varchar2(30));
Table created.
SQL> insert into long_varchartest values('ns89027','sd22712');
1 row created.
SQL> insert into long_varchartest values('Nirav Shah', 'Sangram Dash');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from long_varchartest;
NIRAV SANGRAM
--------------------------------------------------------------------------------
------------------------------
ns89027 sd22712
Nirav Shah Sangram Dash
SQL> insert into long_varchartest(sangram)
2 select nirav from long_varchartest;
select nirav from long_varchartest
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
|
|
|
|
|
|
|
|
|
|
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434886 is a reply to message #434884] |
Fri, 11 December 2009 07:37   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
In all_views there is a column of the type LONG, You can read it out (copy it in the VARCHAR2 vtext) with the code. May be this could help you.
SET SERVEROUTPUT ON SIZE 100000;
DECLARE
vnr NUMBER;
tname VARCHAR2(4000);
vtext VARCHAR2(4000);
vlong LONG;
BEGIN
SELECT ROWNUM view_name, text, text INTO tname, vlong, vtext FROM all_views WHERE ROWNUM = 1;
DBMS_OUTPUT.put_line(' VIEW_NAME=' || tname||' lenght='||length(vlong)||' cont='||substr(vtext,1,60));
END;
|
|
|
|
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434888 is a reply to message #434885] |
Fri, 11 December 2009 07:42   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
delna.sexy wrote on Fri, 11 December 2009 14:35Yes Michel sir,
And that is why I said,
Quote:Don't know exactly
regards,
Delna
In this case check the doc. First lines of TO_LOB doc:
[quote title=Quote:]TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement./quote]
Regards
Michel
[Updated on: Fri, 11 December 2009 08:24] Report message to a moderator
|
|
|
|
|
|
|