Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    30

    Unanswered: Extract View SQL, line-by-line, from ALL_VIEWS

    Hi,

    Anyone know how to extract the TEXT column from the ALL_VIEWS table, on a line-by-line basis, into another table, effectively storing each line of the view text to a row ?

    So that if I have a view called 'my_view' :

    select *
    from products
    where id = 300

    it extracts into a table, with columns :

    VIEW_NAME, LINE_NO, SQL
    my_view,1,select *
    my_view,2,from products
    my_view,3,where id = 300

    ???

    I want to do this for all my views, so they all end up in the one table, line-by-line, referenced by view-name and line-no...

    Thanks,
    Adam

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    this will help with a bit of modification!

    ok, the problem is the source isn't stored in a varchar2 field si I have a function to extract the view source
    -----------------------------------------------------------
    CREATE OR REPLACE FUNCTION view_extract (pViewName IN varchar2)
    return varchar2
    is
    data_chunk varchar2(254);
    chunk_size number := 254;
    chunk_size_returned number;
    vPath varchar2(100) := 'd:\webdb\NewUpdate\views\';
    mycursor number;
    stmt varchar2(1024) := 'select text from user_views where view_name = '''||pViewName||'''';
    cur_pos number := 0;
    rows number;
    dummy number;
    vFile utl_file.file_type;
    status varchar2(50);
    begin
    vFile := utl_file.fopen(vPath, lower(pViewName)||'.sql', 'w');
    mycursor := dbms_sql.open_cursor;
    dbms_sql.parse(mycursor, stmt, dbms_sql.v7);
    dbms_sql.define_column_long(mycursor,1);
    dummy := dbms_sql.execute(mycursor);
    rows := dbms_sql.fetch_rows(mycursor);

    utl_file.put_line(vFile, 'CREATE OR REPLACE FORCE VIEW '||pViewName||' AS ');
    loop
    dbms_sql.column_value_long(mycursor, 1, chunk_size, cur_pos, data_chunk, chunk_size_returned);
    utl_file.put(vFile, data_chunk);
    cur_pos := cur_pos + chunk_size;
    exit when chunk_size_returned = 0;
    end loop;
    dbms_sql.close_cursor(mycursor);
    utl_file.put_line(vFile, '');
    utl_file.put_line(vFile, '/');
    utl_file.put_line(vFile, '');
    utl_file.fclose(vFile);
    return('sucess - '||pViewName);
    exception
    when others then
    utl_file.fclose(vFile);
    return('failure - '||sqlerrm);
    end;

    -------------------------------------------------------------------

    Then I loop through all the views :

    -------------------------------------------------------------------

    declare
    vViewFile utl_file.file_type;
    vPath varchar2(100) := 'd:\webdb\NewUpdate\';
    vVar varchar2(100);
    cursor c1 is
    select view_name from user_views;
    begin
    vViewFile := utl_file.fopen(vPath,'views.sql','w',32000);
    utl_file.put_line(vViewFile,'spool c:\06_views.log');
    utl_file.put_line(vViewFile,'set define off');
    for r in c1 loop
    utl_file.put_line(vViewFile,'PROMPT '||lower(r.view_name)||'');
    utl_file.put_line(vViewFile,'@views\'||lower(r.vie w_name)||'.sql;');
    vVar := view_extract(r.view_name);
    -- dbms_output.put_line(vVar);
    end loop;
    utl_file.put_line(vViewFile,'spool off');
    utl_file.put_line(vViewFile,'');
    utl_file.put_line(vViewFile,'quit');
    utl_file.put_line(vViewFile,'');
    utl_file.fclose_all;
    end;

    ---------------------------------------------------------------------

    This creates me a script to recreate each view and another script that calls all the other scripts! You can modify it to put the source into a table but you wouldn't be able to create a view to see the source on the fly. The only downside is if the view is larger than 32767 bytes it doesn't extract all the source

    hope that helps
    Robert

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a script I use. It will create a spool file of the DDL for the view ...
    It will ask you for the owner and view_name. Go to the bottom of this script and change the directory structure to match your environment ...

    HTH
    Gregg
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •