Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Billy .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: June 11, 2010 - 7:43 am UTC

Version:

Viewed 1000+ times

You Asked

Can you perform string functions on the datatype LONG?


and Tom said...



In PLSQL yes -- in SQL no.

If the long is 32k or less, you can do some interesting things in PLSQL. Lets say you have a table T with a long column like this:

ops$tkyte@8i> create table t ( x int, y long );
Table created.

ops$tkyte@8i> insert into t values ( 1, 'how now brown cow' );
1 row created.

ops$tkyte@8i> select substr( y, 1, 5 ) from t;
select substr( y, 1, 5 ) from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes


ops$tkyte@8i> create or replace function t_substr
( p_rowid in rowid,
p_from in number,
p_for in number ) return varchar2
2 as
3 l_str long;
4 begin
5 select y into l_str from t where rowid = p_rowid;
6
7 return substr( l_str, p_from, p_for );
8 end;
9 /

Function created.

ops$tkyte@8i> select t_substr( rowid, 1, 5 ) from t;

T_SUBSTR(ROWID,1,5)
------------------------------
how n



So, for longs that are 32k or less, we can write functions that are callable in SQL to allow us to do some operations on these long columns.


Additionally -- we can use dbms_sql to READ longs (but not write) them that are of any size. We would piecewise read them. You cannot call a function that uses dbms_sql from SQL as did above but you can use it in any other procedure/function you write. Here is an example of a piecewise read of a long in plsql:

create or replace procedure showlong( p_query 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.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;
htp.preClose;
raise;
end showlong;
/

set echo on
begin
showlong( 'select text from all_views where rownum = 1' );
end;
/




Rating

  (8 ratings)

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

Comments

tried to implement instr but not successful

Ravi Kumar, March 20, 2002 - 4:30 pm UTC

Hi Tom,

I tried to implement INSTR on long column but not successful. What I am trying to get is, all the trigger_names from user_triggers where trigger_body has some string.

something like this :

select trigger_name
from user_triggers
where long_instr(params_list, 'EMP') = 0;

should give all the trigger with EMP in their body.

But not sure what parameters to send. Expecting a general answer that can be used on any view.

Thanks,
Ravi.

Tom Kyte
March 20, 2002 - 9:37 pm UTC

Expecting alot aren't you....


You could use dynamic sql (in 8i and up) to make a "generic solution" but I'll give one that works for all releases:

create or replace function t_instr
( p_trigname in varchar2,
p_lookfor in varchar2 ) return number
as
l_str long;
begin
select trigger_body into l_str
from user_triggers
where trigger_name = p_trigname;
return instr( l_str, p_lookfor );
end;
/


Now, it looks like you are looking for triggers that are NOT dependent on EMP. A better approach would be to use USER_DEPENDENCIES to find triggers that are not dependent on EMP, that way - triggers that refer to EMP_TABLE, EMP_VIEW, EMPLOYEES, EMP_TAB, etc -- wouldn't return from your query.




Problem with hard coded values not the dependencies

Ravi Kumar, March 21, 2002 - 3:21 pm UTC

Hi Tom,

Thanks for your answer.

But, 'EMP' in that query, I just gave as an example. My actual scenario is, we have lot of triggers and views with some hardcoded values. I would like to get all the trigger_names and views with those hard coded values in their body. From the idea you gave and searching your site, I created this function :

CREATE OR REPLACE function linstr ( p_object in varchar2,
p_lookin in varchar2,
p_column in varchar2,
p_colval in varchar2,
p_string in varchar2)
return number
as
l_long_val varchar2(32760);
begin
execute immediate
'select ' || p_lookin ||
' from ' || p_object ||
' where ' || p_column || ' = :val'
into l_long_val
using p_colval;
return instr(l_long_val, p_string);
end linstr;
/

and I am able to issue queries like

select trigger_name
from user_triggers
where linstr('user_triggers', 'trigger_body', 'trigger_name', trigger_name, '01011900') <> 0;

and I am getting all the triggers. It is working fine with user_views and also regular tables.

Please let me know if there is any better way to check for the hardcoded values.

Thank You again.

Ravi.



Code

Marcio, March 12, 2003 - 1:26 pm UTC

As you coded --

<quote>
You could use dynamic sql (in 8i and up) to make a "generic solution" but I'll
give one that works for all releases:

create or replace function t_instr
( p_trigname in varchar2,
p_lookfor in varchar2 ) return number
as
l_str long;
begin
select trigger_body into l_str
from user_triggers
where trigger_name = p_trigname;
return instr( l_str, p_lookfor );
end;
/
</quote>

Could be missing AUTHID CURRENT_USER in your code?

Thanks,
Marcio

Tom Kyte
March 13, 2003 - 7:21 am UTC

no, especially as I said "in all versions"

could you add it? sure in 8i and up.
is it necessary? maybe, maybe not, depends on HOW you use it or want to use it.



I agree to you but...

Marcio, March 13, 2003 - 11:23 am UTC

Yes, but look that. "As you coded" -- You coded a FUNCTION, What am I doing wrong?

ops$mportes@MRP816> create or replace function f_instr ( p_trigger in varchar2, p_long in varchar2 )
2 return number
3 as
4 l_str long;
5 begin
6 select trigger_body into l_str
7 from all_triggers
8 where trigger_name = p_trigger;
9 return instr( l_str, p_long );
10 exception
11 when no_data_found then
12 return 999999;
13 when others then
14 return 888888;
15 end;
ops$mportes@MRP816> /

Function created.

ops$mportes@MRP816> select f_instr('RELEASE_AU', 'exception') from dual;

F_INSTR('RELEASE_AU','EXCEPTION')
---------------------------------
999999

-- But the trigger exists and there is "exception" word there.

ops$mportes@MRP816> select trigger_body
2 from all_triggers
3 where trigger_name = 'RELEASE_AU'
ops$mportes@MRP816> /

TRIGGER_BODY
--------------------------------------------------------------------------------
begin
if ( not state_pkg.avoid_recursion )
then
begin
state_pkg.avoid_recursion := TRUE;
for i in 1 .. state_pkg.newRows.count loop
update release
set (usuario, terminal, last_update) = ( select osuser, termina
l, sysdate
from minha_sessao )
where rowid = state_pkg.newRows(i);
end loop;
state_pkg.avoid_recursion := FALSE;
exception
when others then
state_pkg.avoid_recursion := FALSE;
RAISE;
end;
end if;
end;

-- So, when I create with AUTHID CURRENT_USER works.

ops$mportes@MRP816> create or replace function f_instr ( p_trigger in varchar2, p_long in varchar2 )
2 return number
3 AUTHID CURRENT_USER
4 as
5 l_str long;
6 begin
7 select trigger_body into l_str
8 from all_triggers
9 where trigger_name = p_trigger;
10 return instr( l_str, p_long );
11 end;
12 /

Function created.

ops$mportes@MRP816> select f_instr('RELEASE_AU', 'exception') from dual;

F_INSTR('RELEASE_AU','EXCEPTION')
---------------------------------
500

ops$mportes@MRP816>

??

Thanks,
Marcio



Tom Kyte
March 14, 2003 - 5:32 pm UTC

use user_triggers.

You have done what I call "moving the goalposts". Yes, if you want to use it for what you are doing -- against ALL_ tables, you would probably want authid current_user since

o a procedure runs without roles
o all_* views look at the roles enabled to see what you can see
o all_* views in DR (definer rights) procedures have different rows visible then when not in DR procedures

reader

A reader, April 08, 2005 - 9:24 am UTC

Do you have any script that you might have
developed in the past, that can count
the # of lines in a long field.

Ex:
# of lines in trigger_body in dba_triggers

Thanks

Tom Kyte
April 08, 2005 - 9:44 am UTC

they have added the trigger body into the *_SOURCE views -- does that do it for you?

Reader

A reader, April 08, 2005 - 10:06 am UTC

Need counts of lines on text (long) from dba_views also

Tom Kyte
April 08, 2005 - 10:11 am UTC

well, take t_substr and have it return

length(l_str) - length(replace(l_str,chr(10),''));

it'll work for upto 32k.

beyond that we would need to use piecewise fetching and dbms_sql.

Update Long data type

Damion Bethune, October 02, 2008 - 5:01 pm UTC

How can I write an update statement to update long data type in table? I do not want to change the type in the table because; it is used by the vendor application
Tom Kyte
October 02, 2008 - 8:36 pm UTC

the most you could ever update using just update will be 32k.

Is that sufficient, or do you need support for longer longs than 32k.

If you say yes, the answer is not going to involve updating the long, we'll need to use the API's in some language to pump that data in.

Sarvindu, June 11, 2010 - 7:18 am UTC

Hi Tom,

I tried the function but it is giving me error message ORA-06502: PL/SQL: numeric or value error

SQL> select VERSION from v$instance;

VERSION
-----------------
10.2.0.4.0


SQL> create or replace function t_instr
  2           ( p_view in varchar2,
  3             p_lookfor in varchar2 ) return number
  4  as
  5     l_str long;
  6  begin
  7     select text into l_str
  8       from sys.user_views where view_name = p_view;
  9     return instr( l_str, p_lookfor );
 10  end;
 11  /

Function created.


SQL> create or replace function t_instr
  2           ( p_view in varchar2,
  3             p_lookfor in varchar2 ) return number
  4  as
  5     l_str long;
  6  begin
  7     select text into l_str
  8       from sys.user_views where view_name = p_view;
  9     return instr( l_str, p_lookfor );
 10  end;
 11  /

Function created.

SQL> select view_name,t_instr(view_name,'803') from user_views order by 2 desc;
select view_name,t_instr(view_name,'803') from user_views  order by 2 desc
                 *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PARA.T_INSTR", line 7


SQL> select max(text_length) from user_views;

MAX(TEXT_LENGTH)
----------------
           44431



Tom Kyte
June 11, 2010 - 7:43 am UTC

If the long is 32k or less, you can do some interesting things in PLSQL.

sounds like the long is longer than 32k then.

You'd have to use the other approach using dbms_sql

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library