OraFAQ Forum: SQL & PL/SQL » Retrieve specified characters from LONG datatype

Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve specified characters from LONG datatype (Oracle 11.2.0.4, Windows x64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Retrieve specified characters from LONG datatype [message #648096]

Tue, 16 February 2016 08:25

MurraySobol
Messages: 5
Registered: February 2016
Location: Kitchener ON

Junior Member

Hi:
I have a requirement to retrieve the first 250 characters from a LONG datatype; the destination column datatype is defined as varchar2(250).
When I try this SQL:
CAST(ol.freight_shipping_instructions AS varchar2(250))
AS freight_shipping_instructions,
I get this error:
FW_ORDER_TO_DELIVER_DETAIL PROCEDURE 1 328 16 PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got LONG ERROR 0
FW_ORDER_TO_DELIVER_DETAIL PROCEDURE 2 123 4 PL/SQL: SQL Statement ignored ERROR 0
The destination column:freight_shipping_instructions is defined as varchar2(250); I can not change that definition.
The source column:ol.freight_shipping_instructions (ol is an alias to the table name) is defined as LONG; I also can not change that definition.
Any ideas?
Thanks
Murray Sobol

Report message to a moderator

Re: Retrieve specified characters from LONG datatype [message #648098 is a reply to message #648096]

Tue, 16 February 2016 08:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://www.google.com/webhp?hl=en&tab=ww&gws_rd=ssl#hl=en&q=oracle+convert+long+to+char

Report message to a moderator

Re: Retrieve specified characters from LONG datatype [message #648102 is a reply to message #648096]

Tue, 16 February 2016 10:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why people are still using the obsolete LONG datatype?
They always end with questions like to get a sub-string of a LONG or how to use a LONG in a WHERE clause and so on.

The answer is: you can't in SQL.
You have to use a programming language.

As you want the first 250 characters you can use T. Kyte's PL/SQL function describes there.

And in the future use CLOB and now convert all your current LONG columns to CLOB.

Report message to a moderator

Re: Retrieve specified characters from LONG datatype [message #648200 is a reply to message #648102]

Thu, 18 February 2016 08:07

MurraySobol
Messages: 5
Registered: February 2016
Location: Kitchener ON

Junior Member

We were able to resolve this issue using PL/SQL in a Procedure.
The first step was to implement Tom Kyte's GetLong function.
I had already created a Global Temporary table to contain the results and then used an "INSERT INTO global temporary table" "select values (using getlong function)".
I was able to fine-tune the Function to retrieve whatever number of characters I needed.
So, the comment to convert to "CLOB", although interesting, was not needed in this situation.
In the future I plan to upgrade my servers to Oracle 12c and also convert to a Unicode database; I will then be able to use extended varchar columns (up to 32K).
My concerns with converting LONGs to CLOB revolve around using "secure files" as implemented in Oracle 11g; I understand that Oracle 12c has radically changed / improved this process.

Thanks to all for comments and insights.
Murray

Report message to a moderator

Re: Retrieve specified characters from LONG datatype [message #648213 is a reply to message #648200]

Thu, 18 February 2016 09:52

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback.

Quote:

My concerns with converting LONGs to CLOB revolve around using "secure files" as implemented in Oracle 11g;

Can you extend on this.
I use LOB since they have been introduced in version 8 and never used secure files (which did not exist).
What's the problem?

Report message to a moderator

Previous Topic:	update using with statement
Next Topic:	Sql Query between String

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:02:20 CDT 2024