Problem with Long column [message #434607] |
Wed, 09 December 2009 22:57  |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Hi,
I need to query dba_mviews. If the defuiition of the query is more than 4000 characters it has to throw an exception.
But the column query in DBA_MVIEWS is long. So we cannot find the length of the long column.
Below is the function writtn by me to handle the above exception.
request to let me know if i am wrong and missing out anythg.
function f_return_query(p_owner in varchar2, p_name in varchar2)
return varchar2 as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4001;
l_curpos number := 0;
begin
dbms_sql.parse(l_cursor,
'select query ' || ' from dba_mviews ' ||
' where upper(mview_name) = upper(:x) and upper(owner) = upper(:y)',
dbms_sql.native);
dbms_sql.bind_variable(l_cursor, ':x', p_name);
dbms_sql.bind_variable(l_cursor, ':y', p_owner);
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor) > 0) then
dbms_sql.column_value_long(l_cursor,
1,
l_buflen,
l_curpos,
l_long_val,
l_long_len);
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
EXCEPTION
When value_error then
p_err.write('Base view is having more than 4000 characters!!');
p_err.write_ora_error;
raise;
end f_return_query;
Thanks in advance
|
|
|
|