Home » SQL & PL/SQL » SQL & PL/SQL » Long data type in oracle (Oracle 11)
Long data type in oracle [message #658154] Tue, 06 December 2016 00:27 Go to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Is there any Oracle function which returns varchar2 for the given Long data type value

Thanks
SaiPradyumn
Re: Long data type in oracle [message #658158 is a reply to message #658154] Tue, 06 December 2016 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No.

Re: Long data type in oracle [message #658169 is a reply to message #658158] Tue, 06 December 2016 02:00 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Mike ,
We need to implement the logic by using pl/sql code ?
Re: Long data type in oracle [message #658170 is a reply to message #658169] Tue, 06 December 2016 02:33 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ideally you need to stop using long datatype completely and use clob instead. Long has lots of restrictions that make it difficult to work with.
Re: Long data type in oracle [message #658171 is a reply to message #658169] Tue, 06 December 2016 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
saipradyumn wrote on Tue, 06 December 2016 09:00
Thanks Mike ,
We need to implement the logic by using pl/sql code ?
Yes and no.
Yes if you work only in SQL and PL/SQL with a standard SQL tool like SQL*Plus.
No if you work with your own application you develop.

But as cookiemonter said: you should not work with LONG columns which are obsolete since version 8.0 in 1997.

Re: Long data type in oracle [message #658176 is a reply to message #658171] Tue, 06 December 2016 04:26 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Mike ,

The data dictionary which is provided by oracle is having the long as data type for one of the column.

Ex : DATA_DEFAULT of DBA_TAB_COLUMNS

My requirement is to get the default values of all tables and need to do some validations based on the values.
There i am unable to apply any substr,instr,to char functions on that column.
Re: Long data type in oracle [message #658179 is a reply to message #658176] Tue, 06 December 2016 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle does what it wants in its dictionary, this does mean you can do the same thing.
In addition, Oracle does not access the data using SQL but C programming language.

Quote:
My requirement is to get the default values of all tables and need to do some validations based on the values.
You still did not tell us what is your client tool which is, as I told you, the main point to know how to do it.
If your need is DBA job of validation then use a Perl script for instance.

Re: Long data type in oracle [message #658180 is a reply to message #658179] Tue, 06 December 2016 05:00 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Mike ,

We are using SQL Developer tool to connect to the database.
Re: Long data type in oracle [message #658181 is a reply to message #658180] Tue, 06 December 2016 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, you then have to write a PL/SQL procedure; I bet you'll find many juts typing "oracle long to varchar2" in Google.

Re: Long data type in oracle [message #658185 is a reply to message #658181] Tue, 06 December 2016 08:20 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Mike ,

Thanks for your support. I had implemented one function which returns the varchar for the given long value.

But still getting some problem while using that function in sql query :

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
    P_LONG LONG)
  RETURN VARCHAR2
IS
 -- l_long LONG := P_LONG;
  l_vc2 VARCHAR2(32767);
BEGIN
  
--  l_vc2 :=   CASE
--      WHEN l_long IS NULL
--      THEN 'NO_VALUE'
--      ELSE SUBSTR(l_long,1,32767)
--    END;
IF  P_LONG  IS NULL THEN  
l_vc2 := 'NO_VALUE';
ELSE  
l_vc2  :=   SUBSTR(P_LONG,1,32767);
END  IF;  
  RETURN l_vc2;
END;

then i am using that function in the following query :

 
  SELECT LONG_TO_CHAR(TEXT)   CH  FROM user_views where rownum = 1;

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 10 Column: 21
Re: Long data type in oracle [message #658186 is a reply to message #658185] Tue, 06 December 2016 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need to select the long into a long variable then call the function with the variable.
Re: Long data type in oracle [message #658187 is a reply to message #658185] Tue, 06 December 2016 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Tue, 06 December 2016 06:20
Hi Mike ,

Thanks for your support. I had implemented one function which returns the varchar for the given long value.

But still getting some problem while using that function in sql query :

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
    P_LONG LONG)
  RETURN VARCHAR2
IS
 -- l_long LONG := P_LONG;
  l_vc2 VARCHAR2(32767);
BEGIN
  
--  l_vc2 :=   CASE
--      WHEN l_long IS NULL
--      THEN 'NO_VALUE'
--      ELSE SUBSTR(l_long,1,32767)
--    END;
IF  P_LONG  IS NULL THEN  
l_vc2 := 'NO_VALUE';
ELSE  
l_vc2  :=   SUBSTR(P_LONG,1,32767);
END  IF;  
  RETURN l_vc2;
END;

then i am using that function in the following query :

 
  SELECT LONG_TO_CHAR(TEXT)   CH  FROM user_views where rownum = 1;

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 10 Column: 21
You are not allowed to use SUBSTR(LONG_TEXT)!
>There i am unable to apply any substr,instr,to char functions on that column.
Re: Long data type in oracle [message #658188 is a reply to message #658187] Tue, 06 December 2016 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yup, should have actually read the function.
@saipradyumn - you should have followed Michels advice and done a google search, the first several hits contain complete working examples.
Re: Long data type in oracle [message #658190 is a reply to message #658187] Tue, 06 December 2016 08:51 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi BlackSwan ,

if it is pl/sql block we are able to use the substr function on long data type.


declare
  l_long long;
  l_vc2 varchar2(32767);
begin
  SELECT text into l_long FROM user_views where rownum = 1;  
  --
  l_vc2 := substr(l_long, 1, 32767);
  --
  dbms_output.put_line(l_vc2);
end;

Above block successfully returning query for one of the view .
But when integrated the same logic with in the function and try to access the function in sql query its not working.

Re: Long data type in oracle [message #658191 is a reply to message #658190] Tue, 06 December 2016 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PL/SQL long datatype is NOT a real LONG, it is a synonym (actually subtype) for VARCHAR2(32760).

Re: Long data type in oracle [message #658204 is a reply to message #658191] Tue, 06 December 2016 22:47 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Mike ,

So Finally we can not perform any sub string functions on LONG data type by using simple QUERY.
Need to go for PL/SQL Only.
Re: Long data type in oracle [message #658206 is a reply to message #658204] Wed, 07 December 2016 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... or use another language.
If you post in details your actual issue maybe we can better help.

Re: Long data type in oracle [message #658224 is a reply to message #658206] Wed, 07 December 2016 06:13 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Mike ,

We are expecting the certain default values of the tables should be same for some columns .
My actual requirement is to find out those tables where we have differences in default values of some columns .
Re: Long data type in oracle [message #658226 is a reply to message #658224] Wed, 07 December 2016 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which shell or programming language do you know?

Re: Long data type in oracle [message #658326 is a reply to message #658226] Fri, 09 December 2016 09:48 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi mkie ,

I know only pl/sql .
Re: Long data type in oracle [message #658327 is a reply to message #658326] Fri, 09 December 2016 09:51 Go to previous message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+long+to+char
Previous Topic: create trigger error in SQL
Next Topic: Package Specification varaible
Goto Forum:
  


Current Time: Sun Feb 25 16:04:47 CST 2018

Total time taken to generate the page: 0.02462 seconds