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.
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
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
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
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
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
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
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