Using LIKE on Long Data Types

-- Carlos Magno

create or replace function getlong_const( p_owner in varchar2,
                                          p_table_name in varchar2,
                                          p_cons_name in varchar2) return varchar2
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
dbms_sql.parse( l_cursor,
'select search_condition from dba_constraints where owner = :x and table_name=:y and constraint_name=:z ',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_owner );
dbms_sql.bind_variable( l_cursor, ':y', p_table_name );
dbms_sql.bind_variable( l_cursor, ':z', p_cons_name );

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

if (dbms_sql.fetch_rows(l_cursor)>0)
   dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,l_long_val, l_long_len );
end if;
return l_long_val;
end getlong_const;

select * 
from dba_constraints 
where getlong_const(owner, table_name, constraint_name) like '%NOT NULL%';

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s