Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sudha.

Asked: August 31, 2000 - 8:21 am UTC

Answered by: Tom Kyte - Last updated: May 22, 2013 - 8:57 pm UTC

Category: Database - Version: 7.3.4

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Thanks for the ref_cursor, worked perfectly. I have a nother question for you -- How do I get part of dat from a long datatype field ? I know I can't use substr or instr. Can this be done ? By any method !

Thanks in advance
Sudha

and we said...


In PLSQL we can use dbms_sql to do this, for example - the following will print out an entire long regardless of its length:

create or replace procedure showlong( p_query in varchar2,
p_name in varchar2,
p_value in varchar2 )
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.bind_variable( l_cursor, p_name, p_value );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen,
l_curpos , l_long_val,
l_long_len );

l_curpos := l_curpos + l_long_len;
dbms_output.put_line( l_long_val );
exit when l_long_len = 0;
end loop;
end if;
dbms_output.put_line( '====================' );
dbms_output.put_line( 'Long was ' || l_curpos ||
' bytes in length' );
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end showlong;
/

set echo on
begin
showlong( 'select text from all_views where view_name = :x', ':x', 'ALL_VIEWS' );
end;
/
set echo off

So, you can use dbms_sql.column_value_long to get any piece of a long in PLSQL. This cannot be called from SQL.


If the LONG is always 32k or less, another option is:


create or replace procedure longsubstr( p_rowid in rowid,
p_from in number,
p_for in number )
return varchar2
as
l_tmp long;
begin
select LONG_COLUMN into l_tmp from T where rowid = p_rowid;

return substr( l_tmp , p_from, p_for );
end;
/


and then you can:

select a, b, longsubstr(rowid,35,200) from T
/





and you rated our response

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Long datatype fields

April 03, 2002 - 11:52 am UTC

Reviewer: David from GB

Tom, how can I insert into a table the values returned by l_curpos for each row. When I include an insert INTO table statement it only does it once.

I would like to pass as the query 'select text from all_tables' but then insert the LONG VALUE SIZE into a separate table.

Regards

Tom Kyte

Followup  

April 03, 2002 - 4:06 pm UTC

does not compute -- I don't know what you are asking to do. l_curpos is just a number, an offset.

If you want to insert the "long value size" - it sounds like you want a function that returns the size in bytes of a given long. turn this into a function that given a query that identifies a row -- returns the long size in bytes. In 8i -- if you make that an autonomous transaction, you can call it from sql in an insert statement.

longsubstr() should be a function, not a procedure

August 18, 2004 - 9:35 am UTC

Reviewer: Sudhakar Atmakuru from Toronto, Canada

Hi tom,

I see a typo in your response. The example you gave for longsubstr() should define a function but not a procedure, since a procedure can not return a value. Dont you think so?

Thanks,
Sudhakar.

Tom Kyte

Followup  

August 18, 2004 - 9:52 am UTC

yes

Dealing with long datatype fields

August 18, 2004 - 5:15 pm UTC

Reviewer: Bopanna Kallichanda from Colorado

I created this function

create or replace function long_substr
( p_table_owner in varchar2,
p_table_name in varchar2,
p_partition_name in varchar2)
return varchar2
as
l_tmp long;
begin
select high_value
into l_tmp
from all_tab_partitions
where table_owner = p_table_owner
and table_name = p_table_name
and partition_name = p_partition_name ;

return l_tmp;
end;

and then I ran the following query
select table_owner, table_name, partition_name, HIGH_VALUE, long_substr2(table_owner, table_name, partition_name) HIGH_VALUE2
from all_tab_partitions
where table_name = 'ORDER_ITEM_FACT'

KBDSS ORDER_ITEM_FACT OIF_FY1999M10 2451511 <null>
KBDSS ORDER_ITEM_FACT OIF_FY1999M11 2451546 <null>
KBDSS ORDER_ITEM_FACT OIF_FY1999M12 2451574 <null>

Function returns null value. Any ideas on why it is null?

Thanks


Tom Kyte

Followup  

August 18, 2004 - 11:40 pm UTC

you named it long_substr but are calling long_substr2?

when I changed both to be long_substr, i get:

ops$tkyte@ORA9IR2> select user, table_name, partition_name, HIGH_VALUE,
  2  long_substr2(user, table_name, partition_name) HIGH_VALUE2
  3      from user_tab_partitions
  4      where table_name = 'T'
  5  /
 
USER                           TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME
------------------------------
HIGH_VALUE
-------------------------------------------------------------------------------
HIGH_VALUE2
-------------------------------------------------------------------------------
OPS$TKYTE                      T
JUNK
MAXVALUE
MAXVALUE
 
OPS$TKYTE                      T
PART1
TO_DATE(' 2003-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
TO_DATE(' 2003-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
 
OPS$TKYTE                      T
PART2
TO_DATE(' 2003-03-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
TO_DATE(' 2003-03-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
 

Dealing with long datatype fields

August 19, 2004 - 12:48 pm UTC

Reviewer: Bopanna Kallichanda from Colorado

The problem seems to be table and the function need to be owned by the same user. When I created the function as the user who owns the table, it works fine. That is what I wanted. Thank you.


Tom Kyte

Followup  

August 19, 2004 - 5:27 pm UTC

or use dba_tab_partitions...

Dealing with long datatype fields

August 19, 2004 - 7:43 pm UTC

Reviewer: Bopanna Kallichanda from Colorado

dba_tab_partitions worked. Thank you once again.

Need to accomplish the same thing in a Before Insert Trigger.

September 02, 2004 - 5:29 pm UTC

Reviewer: Ben Sauer from Provo, UT USA

Tom,

I used this to create a function that takes a field and populates another field with the top line of the first field. Worked great for all my historical data. Problem is that I now need to have the database do the same thing for new records on a Before Insert Trigger. My function calls dbms_sql.column_value_long(l_cursor, 1, 40, 0 , val_out, len_ref); which produces a this command must follow a fetch. (The previous line.) Any ideas on modifying this approach or an alternate approach for it? Thanks,

Tom Kyte

Followup  

September 02, 2004 - 9:45 pm UTC

need to accomplish "what" exactly?



Long Values cont...

September 07, 2004 - 2:42 pm UTC

Reviewer: Ben L Sauer from Provo, UT USA

Sorry, should have been more clear. I need to get part of the data from a long datatype field. I know I can't use substr or instr to do that, but what can I use. The approach above won't work because I need to do it before it's written to the database.

Tom Kyte

Followup  

September 07, 2004 - 2:51 pm UTC

nothing much is going to work in the trigger as longs are not accessible to the row triggers.

why do you need it "before" (since longs in general are not necessarily all there during the insert -- they normally do piecewise inserts after the row has been inserted already for big longs)

what is the goal -- what is the end result needed? (i hope you don't have data encoded in the long, that would just be "wrong")

That's what I was afraid of...

September 07, 2004 - 4:03 pm UTC

Reviewer: Ben Sauer from Provo, UT USA

I'm trying to get around a poorly designed front end by clipping out the first line of the long and putting it into a title field and having it go in at the same time as the insert so the front end will update the main screen with it. I realize it's bad practice, but given the circumstances about optimal recovery from bad code; at least if it were possible.

Tom Kyte

Followup  

September 07, 2004 - 4:21 pm UTC

You might be able to get away with this, if the long is piecewise inserted, all bets are off.  if long exceeds 32k, you have to use dbms_sql to piecewise fetch the first bit:




ops$tkyte@ORA9IR2> create table t ( pk int primary key, l long, x varchar2(20) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package state_pkg
  2  as
  3          type array is table of number index by binary_integer;
  4
  5          g_data array;
  6  end;
  7  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_bi
  2  before insert on t
  3  begin
  4          state_pkg.g_data.delete;
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> create or replace trigger t_aifer
  2  after insert on t for each row
  3  begin
  4          state_pkg.g_data(state_pkg.g_data.count+1) := :new.pk;
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> create or replace trigger t_ai
  2  after insert on t
  3  declare
  4          l_rec t%rowtype;
  5  begin
  6          for i in 1 .. state_pkg.g_data.count
  7          loop
  8                  select * into l_rec from t where pk = state_pkg.g_data(i);
  9                  update t set x = substr( l_rec.l, 1, 20 );
 10          end loop;
 11  end;
 12  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 'hello world blah blah blah blah blah blah blah blah blah', null );
 
1 row created.
 
ops$tkyte@ORA9IR2> select pk, x from t;
 
        PK X
---------- --------------------
         1 hello world blah bla
 
 

serach long datatype

September 21, 2004 - 6:32 am UTC

Reviewer: Charanjiv from india

Can i do something like ..
select view_name from dba_views
where TEXT like 'MY STRING'



Tom Kyte

Followup  

September 21, 2004 - 7:52 am UTC

</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX <code>

index it, create index t_idx on t(longcolumns) indextype is ctxsys.context;

and search it

where contains( longcolumns, '{my string}' ) > 0;

Dealing with long datatype fields

December 06, 2004 - 12:55 am UTC

Reviewer: sohwe from Malaysia


RE: search long datatype in xxx_VIEWS (see September 21, 2004)

September 19, 2007 - 5:49 pm UTC

Reviewer: A reader

Please clarify. Can/should we create a ctxsys index on XXX_VIEWS (as you seemed to imply above)?

Elsewhere

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

you wrote:

"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)

..... "

But if they're more than 32K, we need to follow your September 7, 2004 follow-up.
Tom Kyte

Followup  

September 24, 2007 - 7:00 am UTC

where did I suggest above that you would create an index on the data dictionary?

if in fact that is what you mean by xxx_views

TEXT from DBA_VIEWS

November 05, 2007 - 3:11 pm UTC

Reviewer: Duke Ganote from Amelia, OH USA

I think they were referencing this:

Q: "Can i do something like ..
select view_name from dba_views where TEXT like 'MY STRING'"

A: "index it, create index t_idx on t(longcolumns) indextype is ctxsys.context;
and search it where contains( longcolumns, '{my string}' ) > 0;"

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

But you're really suggesting something like this example, where I'm trying to find views that embed a NO_MERGE hint (version 10gR2):

BEGIN
FOR rec in
( SELECT view_Name, text, text_length
FROM USER_VIEWS
WHERE text_length < 32000 ) LOOP
IF UPPER(rec.text) LIKE '%NO_MERGE%' THEN
DBMS_OUTPUT.PUT_LINE(rec.view_Name);
END IF;
END LOOP;
END;
/

November 13, 2012 - 10:38 am UTC

Reviewer: Oscar Rivera from NH


Thanks and Function to get LONG to CLOB

May 22, 2013 - 7:51 pm UTC

Reviewer: kpanchan from Atlanta,GA

Tom

I enhanced your SHOWLONG procedure as Function to return CLOB. This helped me to overcome copying data from legacy database with LONG datatype to CLOB dataype in Oracle 11g. Your solution rock across the dblink.

Code:
create or replace FUNCTION F_long_to_clob( p_query in varchar2,
p_name in varchar2,
p_value in varchar2 )
RETURN CLOB
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
v_big_str VARCHAR2(32767):='';
vTempClob CLOB;
vClob CLOB;

begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.bind_variable( l_cursor, p_name, p_value );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen,
l_curpos , l_long_val,
l_long_len );
l_curpos := l_curpos + l_long_len;
--dbms_output.put_line( l_long_val );
vTempClob:= l_long_val;
vClob:= vClob || vTempClob;

exit when l_long_len = 0;
end loop;
end if;
--dbms_output.put_line( '====================' );
--dbms_output.put_line( 'Long was ' || l_curpos ||
-- ' bytes in length' );
dbms_sql.close_cursor(l_cursor);

return (vClob);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end f_long_to_clob;
/

How to execute this code:

select
F_LONG_TO_CLOB('SELECT LONG_COL FROM schemaname.table_name@dblink WHERE 1 = :x ',':x',1)
from dual;

I want to share because your solution works across dblink.

Thanks once again. Your website oracle knowledgebase !!
Tom Kyte

Followup  

May 22, 2013 - 8:57 pm UTC

thanks!

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here