Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Long column
Problem with Long column [message #434607] Wed, 09 December 2009 22:57 Go to next message
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
Re: Problem with Long column [message #434635 is a reply to message #434607] Thu, 10 December 2009 01:03 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A PL/SQL VARCHAR2 can be 32K long.
To go further you have to create your own external program.

Regards
Michel
Previous Topic: Materialized View with for update
Next Topic: Locks on Row and Table in Oracle
Goto Forum:
  


Current Time: Tue Feb 11 03:04:18 CST 2025