Home » SQL & PL/SQL » SQL & PL/SQL » How to select data(first 10 charcters) from a LONG datatype column.? (Oracle 10.2 (Windows client, Solaris Host))
How to select data(first 10 charcters) from a LONG datatype column.? [message #434864] Fri, 11 December 2009 06:00 Go to next message
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 #434866 is a reply to message #434864] Fri, 11 December 2009 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LONG (that is obsolete since the last century) is submitted to many restrictions and one is that it is mandatory the whole data (in SQL and PL/SQL, in external language you can get them by chunks).

Regards
Michel
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434869 is a reply to message #434864] Fri, 11 December 2009 06:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Restrictions on LONG type.

regards,
Delna
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434880 is a reply to message #434864] Fri, 11 December 2009 07:10 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Atleast there must me any way left...
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434882 is a reply to message #434880] Fri, 11 December 2009 07:24 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't know exactly, but TO_LOB may help you.

regards,
Delna
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434883 is a reply to message #434880] Fri, 11 December 2009 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said: no way "in SQL and PL/SQL, in external language you can get them by chunks".
Use external program and language.

Regards
Michel
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434884 is a reply to message #434882] Fri, 11 December 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Fri, 11 December 2009 14:24
Don't know exactly, but TO_LOB may help you.

regards,
Delna

But this require to store the LONG value into a CLOB column either in the same or another table.

Regards
Michel
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434885 is a reply to message #434884] Fri, 11 December 2009 07:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes Michel sir,
And that is why I said,
Quote:
Don't know exactly


regards,
Delna
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 Go to previous messageGo to next message
_jum
Messages: 508
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 #434887 is a reply to message #434886] Fri, 11 December 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only if the LONG is less than 32K.

Regards
Michel
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Fri, 11 December 2009 14:35
Yes 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

Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434890 is a reply to message #434888] Fri, 11 December 2009 07:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
in the select list of a subquery in an INSERT statement


Didn't know that.
Thanks a lot sir...

regards,
Delna
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434897 is a reply to message #434864] Fri, 11 December 2009 10:20 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
As it's production database , I am not allowed to create any object(function) just to run for a adhoc query. is there any other way around.?
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434898 is a reply to message #434897] Fri, 11 December 2009 10:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No. All options that exist to access LONGs have been mentioned in this thread.
Re: How to select data(first 10 charcters) from a LONG datatype column.? [message #434899 is a reply to message #434897] Fri, 11 December 2009 10:30 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 11 December 2009 14:27
As I said: no way "in SQL and PL/SQL, in external language you can get them by chunks".
Use external program and language.

Regards
Michel

[Updated on: Fri, 11 December 2009 10:31]

Report message to a moderator

Previous Topic: i had a doubt
Next Topic: subtract dates
Goto Forum:
  


Current Time: Tue Dec 06 00:22:33 CST 2016

Total time taken to generate the page: 0.15687 seconds