The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, Joshua .

Asked: March 28, 2001 - 8:49 pm UTC

Last updated: June 09, 2008 - 12:01 pm UTC

Version: Oracle 8.1.7

Viewed 10K+ times! This question is

You Asked

I have a long datatype that contains only Varchar2 characters. I need to extract only sections of the long datatype rather than retrieving the whole attribute. THe lentghs and content i am trying to extract are variable.

Is there a way of doing this short of writing the long to file and then reloading the file using a 3gl ? ( There is a string pattern that can be used to identify the start and end of the content required )

Thanks in advance
Josh

and Tom said...

Well, in 8.0 and up you should not use LONGS and LONG RAWS, the data should be converted to CLOBS and BLOBS. Then you would have dbms_lob.instr, dbms_lob.substr and so on to do this easily.

You can conver longs to clobs in 8i using the built in TO_LOB() sql function. You would use this to permanently convert the longs to clobs.

If you must keep it as a long for whatever reason, the following might be of some help. With LONGS (but not LONG RAWS) we can get piecewise READ ONLY access to the long via dbms_sql. Here is an exmaple that will get the substr of any long from any table. What I am expecting here is a query that takes on bind variable to identify the row. In a normal table -- ROWID would work best. For this, I'm using a view so I have to use the "primary key" of the view instead:

sys@TKYTE816> create or replace function longsubstr( p_query in varchar2,
2 p_bind in varchar2,
3 p_from_byte in number,
4 p_for_bytes in number )
5 return varchar2
6 as
7 l_cursor integer default dbms_sql.open_cursor;
8 l_long_val long;
9 l_buflen integer;
10 l_ignore number;
11 begin
12 dbms_sql.parse( l_cursor,
13 p_query,
14 dbms_sql.native );
15
16 dbms_sql.bind_variable( l_cursor, ':bv', p_bind );
17
18 dbms_sql.define_column_long(l_cursor, 1);
19 l_ignore := dbms_sql.execute(l_cursor);
20
21 if (dbms_sql.fetch_rows(l_cursor)>0)
22 then
23 dbms_sql.column_value_long
24 ( c => l_cursor,
25 position => 1,
26 length => p_for_bytes,
27 offset => p_from_byte,
28 value => l_long_val,
29 value_length=> l_buflen );
30 end if;
31
32 return l_long_val;
33 exception
34 when others then
35 if dbms_sql.is_open(l_cursor) then
36 dbms_sql.close_cursor(l_cursor);
37 end if;
38 raise;
39 end longsubstr;
40 /

Function created.

sys@TKYTE816>
sys@TKYTE816>
sys@TKYTE816> begin
2 for x in ( select view_name, text_length
3 from user_views
4 where text_length = ( select max(text_length)
5 from all_views )
6 )
7 loop
8 dbms_output.put_line( 'Last 100 bytes of view ' || x.view_name );
9 dbms_output.put_line
10 ( longsubstr
11 ( 'select text from user_views where view_name = :bv',
12 x.view_name,
13 greatest( x.text_length-100, 0 ),
14 100 )
15 );
16 end loop;
17 end;
18 /
Last 100 bytes of view STRADDLING_TS_OBJECTS
and bitand(t.property,512)!=0
and t.bobj# = i.bo#
and ip.bo#= i.obj#
and ip.ts# != tp.ts#

PL/SQL procedure successfully completed.

That can return any 32k substr of the long. Search this site for SHOW_LONG for another example using this functionality.

Rating

(18 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Works -- but why?

Vladimir Andreev, May 07, 2002 - 8:44 am UTC

This is definitely a way around the LONG columns. But that does NOT alter the fact that Oracle keeps LONG columns in the dictionary even while strongly encouraging developers to migrate to LOBs.
I could understand keeping the datatype unchanged for the old columns (like USER_VIEWS.Text), but why defining NEW dictionary columns (like USER_IND_EXPRESSIONS.COLUMN_EXPRESSION) of the LONG datatype beats me. That converts otherwise trivial tasks into nightmare -- try to get the equivalent of user_ind_columns with real column names when some of them are descending, for example. Or try to automate range partition maintenance operations based on the high values and not names, and you'll se what I mean.

USER_VIEWS column TEXT is of datatype LONG --> VARCHAR2

Alex Hendriks, November 14, 2002 - 8:23 am UTC

Thanks! This really helped me a lot. I implemmented it in a meta-table system we use here.

I wonder if it is also possible to use these longs to varchar2 conversion with execute_emmediate instead of dbms_sql?

November 14, 2002 - 7:22 pm UTC

nope

A reader, November 15, 2002 - 9:37 am UTC

Hi Tom,

 Since LONG data type is depricated in oracle why oracle has long datatype is user_views  views(In oracle 9.2)???

SQL> desc user_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG <<------
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)

November 15, 2002 - 7:23 pm UTC

backwards compatibility for now.

A reader, November 15, 2002 - 9:42 am UTC

In oracle 9i R2

SQL> select count(*) from  all_tab_columns where DATA_TYPE      = 'LONG';

  COUNT(*)
----------
        99

Still oracle is using LONG. Strange !!!!!

Substr on Long?

Maverick, May 17, 2005 - 12:48 pm UTC

Tom, I was trying to extract values from user_tab_partitions in Oracle version 9.2.0.1 .
I need something like this
select table_name,partition_name,substr(high_value,11,19) date from user_tab_partitions
where table_name ='MY_TABLE';

but since substr cannot be used on long datatypes, how should i get this info. We are putting date values for this partitions in this column

eg: high value has a value
TO_DATE(' 2005-04-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

Any help is appreciated.
Thanks,

May 17, 2005 - 2:17 pm UTC

see orginal answer "longsubstr" above.......

Extracting LONGS from DBLINK to new DB

Russell, August 30, 2005 - 4:18 am UTC

Tom,

There is an Oracle DB that we need to extract data out of. For some reason, one of the fields in the remote data is a LONG datatype. Ultimately, I only need about 7 characters from the LONG, and from what I can see, the field has less than 32K characters.

I created a table on my database (with a Long , varchar2 and a CLOB field to try and store the data in different attempts).

I have tried Insert Into (into the LONG), and the error ORA-00997: Illegal use of LONG datatype) is returned

I created the longsubstr function listed on this page, and when I tried to insert that data ( or select it even)

select MSGKEY, DESTINATION, DATECREATED, MSGTYPE, MSGTEXT, longsubstr(M.rowid,1,100)
FROM W6ADMIN.W6MESSAGES@CNRP M
WHERE M.MSGTYPE = 'No Work'
AND m.DATECREATED BETWEEN TRUNC(SYSDATE) -1 AND TRUNC(SYSDATE);

I get the following:
ORA-02069 global_names parameter must be set to TRUE for this operation

On setting global_names to true, I get
ORA-02070 database CNRP does not support ROWIDs in this context.

I do not have access to change or modify anything on the remote end. I have to import this database into mine every day. Do you have any ideas, tips or suggestions to help me out?

thanks

Russell

August 30, 2005 - 7:09 am UTC

That remote site is going to have to help you. if they won't, you'll have to copy the entire long over (using something like sqlplus copy command).

IF THEY have the longsubstr and create a view on their site:

select MSGKEY, DESTINATION, DATECREATED, MSGTYPE, MSGTEXT,
longsubstr(M.rowid,1,100) text
FROM W6ADMIN.W6MESSAGES;

You can just select from that.

Conversion/Extraction of Longs

Sucheta Babar, December 23, 2005 - 2:19 am UTC

Actually, this answer helped me few years back. Recently some body asked me same question and I came back to this site. asktom.oracle.com is always guieded me in my career.
Sorry I have not responded earlier questions even though I have used it many times.

Thanks TOM

Select LONG from REMOTE view

Mark Bowl, April 14, 2006 - 9:41 am UTC

I have the following problem: I can't select a LONG column from remote database, when there are Join operation in the query. Example:
SELECT long_column FROM remote_table@DB
-> that's OK, but:
SELECT long_column FROM remote_table@DB INNER JOIN remote_table2@DB ON ...
-> returns: "ORA-00997: Illegal use of LONG datatype"
I cannot even perform (because there a LONG column in the joined table):
SELECT nonlong_column FROM remote_table@DB INNER JOIN remote_table2@DB ON ...
That's very strange to me because:
SELECT long_column FROM local_table INNER JOIN local_table2 ON ...
-> is OK!
Can you help me to SELECT a long column from remote DB in query with JOIN clause (the LONG column is only in the SELECT list and not in the join clause)!
So how can I get the value of this column?
Actually I'm trying to retrieve data from Oracle's views so the advice to change the LONG columns into LOBs is not useful here. Any help? Thanks in advance.
p.s. Here is the exact query that I'm trying to execute (to extract only the partitions by DATE range):

SELECT apc.NAME||'.'||apc.COLUMN_NAME cname, atp.PARTITION_NAME, atp.HIGH_VALUE
FROM all_tab_partitions atp INNER JOIN
all_part_key_columns apc ON (atp.TABLE_OWNER=apc.OWNER AND atp.TABLE_NAME=apc.NAME
AND apc.OBJECT_TYPE='TABLE' AND apc.COLUMN_POSITION=1) INNER JOIN
all_tab_cols atc ON (apc.OWNER=atc.OWNER AND apc.NAME=atc.TABLE_NAME
AND apc.COLUMN_NAME=atc.COLUMN_NAME AND atc.DATA_TYPE='DATE')
WHERE atp.TABLE_OWNER='MDR'

How to get the length of a LONG over HS?

Mathew Butler, June 14, 2006 - 8:33 am UTC

Oracle 9.2.0.5

I am attempting to process some data stored in MS access which is being retrieved into Oracle using a Heterogeneous Service (HS). The remote data is being interpretted as a LONG.

I want to check the length of the data in the remote Access DB - but over the HS connection ( ie: fro mteh Oracle side) - I can then compare the source and target data lengths for validation.

Is there a simple way to do this?

I thought that SHOW_LONG might help - I searched for SHOW_LONG on this site but got no hits.

Can you please publish the code for SHOW_LONG?

June 14, 2006 - 12:28 pm UTC

search for showlong

ORA-01010 invalid OCI operation for showlong, dblink and HS

Mathew Butler, June 15, 2006 - 5:16 am UTC

* showlong ran successfully with a local query
* showlong ran successfully with a dblink ( a link back to the same DB ) query
* Ran showlong over dblink to remote acccess database over HS and got ORA-01010
* ORA-01010 received when executing "dbms_sql.column_value_long" - see below:

<b>- Is this expected behaviour?</b>
<b>- Can you think on any (Oracle based) workaround?

I'm pulling data into Oracle from access and carrying out some validation on the data retrieved ( checking the length of remote columns that are translated as longs ) </b>

Here is an example of the failure:

SQL> create or replace procedure showlong( p_query in varchar2 )
  2  as
  3      l_cursor    integer default dbms_sql.open_cursor;
  4      l_n         number;
  5      l_long_val  varchar2(250);
  6      l_long_len  number;
  7      l_buflen    number := 250;
  8      l_curpos    number := 0;
  9  begin
 10      dbms_output.put_line( 'Parsing...' );
 11      dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
 12
 13      dbms_output.put_line( 'Define...' );
 14      dbms_sql.define_column_long(l_cursor, 1);
 15      dbms_output.put_line( 'Execute...' );
 16      l_n := dbms_sql.execute(l_cursor);
 17
 18      dbms_output.put_line( 'Fetch...' );
 19      if (dbms_sql.fetch_rows(l_cursor)>0)
 20      then
 21          loop
 22              dbms_output.put_line( 'Process fetch...' );
 23              dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
 24                                          l_long_val, l_long_len );
 25              l_curpos := l_curpos + l_long_len;
 26              dbms_output.put_line( l_long_val );
 27              exit when l_long_len = 0;
 28        end loop;
 29     end if;
 30     dbms_output.put_line( '====================' );
 31     dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );
 32     dbms_sql.close_cursor(l_cursor);
 33  exception
 34     when others then
 35        if dbms_sql.is_open(l_cursor) then
 36           dbms_sql.close_cursor(l_cursor);
 37        end if;
 38        raise;
 39  end showlong;
 40  /

Procedure created.

SQL>
SQL> begin
  2    showlong( 'select "Description" from part1@segordb where "ReferenceSeq" = 254' );
  3  end;
  4  /
Parsing...
Define...
Execute...
Fetch...
Process fetch...
begin
*
ERROR at line 1:
ORA-01010: invalid OCI operation
ORA-02063: preceding line from SEGORDB
ORA-06512: at "NPADMIN.SHOWLONG", line 38
ORA-06512: at line 2




ORA-01010 invalid OCI operation

Cause: One of the following:

* You attempted an invalid OCI operation. 
* You are using an Oracle client application linked with release 7.1 (or higher) libraries, the environment variable ORA_ENCRYPT_LOGIN is set to TRUE, and you attempted to connect to a release 7.0 (or lower) Oracle database server. 
* You are connected to a release 7.1 (or higher) Oracle database server, the DBLINK_ENCRYPT_LOGIN initialization parameter is set to TRUE, and you attempted to use a database link pointing to a release 7.0 (or lower) Oracle database server. 
* You are connected to a release 9.0.2(or higher) Oracle database server and you attempted to use a database link pointing to a release 9.0.1 (or lower) Oracle database server for distributed autonomous transactions. 

Action: For the above causes:

* Do not use the invalid OCI operation. 
* If you do not wish to use encrypted connect passwords in your distributed database, then set ORA_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted connect passwords, then you must upgrade all Oracle database servers to release 7.1 (or higher). 
* If you do not wish to use encrypted database links in your distributed database, then set DBLINK_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted database links, then you must upgrade all Oracle database servers to release 7.1 (or higher). 
* Do not attempt distributed autonomous transactions on release 9.0.1 (or lower) Oracle database servers. 


As always, many thanks in advance.

Best Regards.

June 15, 2006 - 8:44 am UTC

seems like it could be expected (I never tried it), to confirm, you would need to utilize support.

Thanks - already have an SR open for a related LONG issue.

Mathew Butler, June 16, 2006 - 4:52 am UTC

Best Regards,

Appearing some junk characters

ravs, July 06, 2006 - 6:18 am UTC

Hi Tom,
I am using your function given above to read data from long over a dblink and trying to insert it into a clob.
some junk charcters are appearing.

select longsubstr1('select case_history from table_case@mylnk1 where objid=:bv',268719164,0,4000) as desc from dual; 
desc
--------------------------------------------------------
Tony Holder welcomed everyone to the combined CAMSS and SPACE User Group called <B>¿The National Repair User Group¿</B>. Introductions were made, and the minutes of the previous meeting were agreed.

The original string

select case_history from table_case@mylnk1 where objid=268719164 ;
case_history
----------------------------------------------------------
Tony Holder welcomed everyone to the combined CAMSS and SPACE User Group called <B>The National Repair User Group</B>. Introductions were made, and the minutes of the previous meeting were agreed.

¿ appearing in place of 

I debugged your function and could not find why this character change?

my source db version is : 8.1.7.2
my target version is : 9.2.0.6

Source character set : WE8ISO8859P1
Target Charcterset : WE8ISO8859P1
source
SQL> select parameter,value from v$nls_parameters@mylnk1 where parameter='NLS_CHARACTERSET' ;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -------------------------------
NLS_CHARACTERSET                                                 WE8ISO8859P1

target
SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET' ;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------
NLS_CHARACTERSET                                                 WE8ISO8859P1

i don't have any clue why this is happening?
can you please help on this Tom?
- Thanks in advance
ravi

July 08, 2006 - 9:46 am UTC

stupid windows "smart quotes"

You have characters in there that are not valid in your character set - they are "windows only" characters.

When we pull the long and convert to a clob, character set conversion is kicking in and they are getting replaced with their "equivalent" which is basically "not there".

The client that inserted this said "I'm using the we8iso8859p1 characterset", but it lied to us. It was using the windows WE8MSWIN1252 (probably) one...

those smart quotes - not part of we8iso8859p1.

Characters missing

ravs, July 10, 2006 - 1:57 am UTC

Hi Tom,
Thanks alot for your response.
I tried by running the above test on unix box also.
But it is also giving the same problem. My oracle is on sun solaris 5.8. I think somewhetere in the function it is not able to understand those characters? Is there any way that i can keep them intact?

- Thanks in advance,
ravi

July 10, 2006 - 7:45 am UTC

characters are not missing, your charactersets are NOT windows charactersets, hence when character set translation takes place (convert LONG into LOB), the invalid characters are mapped - it is all about these "windows smart quotes" which exist in the MSWIN character sets, but not in the standard characterset you are using.

character conversion

ravs, July 11, 2006 - 5:38 pm UTC

Hi Tom,
I checked my code again. The smart quotes are getting converted to some junk character at the following piece of code itself
if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long
( c => l_cursor,
position => 1,
length => p_for_bytes,
offset => p_from_byte,
value => o_output, -- varchar2 datatype
value_length=> l_buflen );
end if;
dbms_output.put_line(substr(o_output,1,250)) ;
when tried to print them, here itself its showing some junk characters.

so i think "dbms_sql.column_value_long" is removing those smart quotes.
Is there any way to include windows smart quotes also
- Thanks in advance
ravi

July 12, 2006 - 3:27 pm UTC

THEY ARE NOT VALID CHARACTERS IN YOUR CHARACTER SET.

I don't know what else to say, any time character set conversion kicks in - bamm, garbage in, garbage OUT. The data is bad to begin with.

How to go about text compare

Kshitij Peter, July 24, 2006 - 12:05 pm UTC

I need to compare the text in the views (as pointed out in one of the post on this page).
I need to check whether a particular string (say xyz) exists in the TEXT column in DBA/USER _VIEWS.

Is there a simpler way to just compare the text in it.

What I am trying to do is:

select * from DBA_VIEWS
where TEXT like '%xyz%';
--But here the TEXT being long can't be compared directly. Tried to use to_char and to_lob, but it gives a 00932-inconsistent data type error.

Please help.
Thanks in advance

July 24, 2006 - 2:47 pm UTC

if your views are 32k and less - you can use plsql

begin
for x in ( select * from dba_views )
loop
if x.view_text like whatever
then
dbms_output.put_line(something)

.....

Use INSTR function with long datatype

Sara, September 18, 2006 - 1:18 pm UTC

I have the same problem with INSTR function.
Can you help me?

Thanks for all

Sara

September 18, 2006 - 1:45 pm UTC

well, just use the same technique, you have to piecewise fetch it and do the instr against the bits of the string as you fetch them.

how to use forms with hetrogeneous dblink

esraa, May 29, 2008 - 6:04 am UTC

I am using Oracle 10G , Access Db 2007, Windows XP

I finished hetrogeneous connectivity from Oracle 10G  to  Access Db 2007.
It is running well on the Oracle database .
I could display, update data on Access  form my SQL>....
You can see the following for view creation  and description:
connect sys/sys as sysdba
SQL> select view_name ,owner from all_views where owner = 'EMPLOYMENT'
2 /

VIEW_NAME OWNER
------------------------------ ------------------------------
ORA_ATTENDANCE EMPLOYMENT
VACANCY EMPLOYMENT
INDEX_BY_TYPES EMPLOYMENT

SQL> select text from all_views where view_name = 'ORA_ATTENDANCE';

TEXT
--------------------------------------------------------------------------------
select ID,USERID,CHECKTIME from my_attendance@access_db

-----------------------------------------------------------------
After connect to oracle schema 'employment':
SQL> desc ora_attendance;
Name 
---------------------------------------
ID 
USERID 
CHECKTIME 

---------------------------------------------------------
My probelm now is how to create Oracle Form data block on this view .
When I create the form aand run it :
no data display on query and error  FRM-40505 hapens.
What is wrong???
Thanks for your help in addvance.

May 29, 2008 - 8:14 am UTC

why do people do this all of the time. post the same exact text here, there and everywhere.

How to find the the length of data of long data type

vivek, June 09, 2008 - 4:48 am UTC

How to find the the length of data of long data type

June 09, 2008 - 12:01 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:510622111991#29261383968124

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Works -- but why?

USER_VIEWS column TEXT is of datatype LONG --> VARCHAR2

Substr on Long?

Extracting LONGS from DBLINK to new DB

Conversion/Extraction of Longs

Select LONG from REMOTE view

How to get the length of a LONG over HS?

ORA-01010 invalid OCI operation for showlong, dblink and HS

Thanks - already have an SR open for a related LONG issue.

Appearing some junk characters

Characters missing

character conversion

How to go about text compare

Use INSTR function with long datatype

how to use forms with hetrogeneous dblink

How to find the the length of data of long data type

More to Explore

CLOB/BLOB/etc