Get example data for each column in Database-CodePudding

I'm trying to get EXAMPLE entries for each column in the database The best I can come up with is something like:

SELECT owner || '.' || table_name || '.' || column_name AS ex FROM all_tab_cols;

When I do this, I get the list like I want however I'm having a very difficult time figuring out how to LOOP in SQL.

I think I have narrowed down what I want to do but am stuck figuring out PUT_LINE

BEGIN
  FOR i IN (SELECT owner || '.' || table_name || '.' || column_name AS ex
              FROM all_tab_cols) 
  LOOP
    dbms_output.put_line(select * from i fetch first row only);
  END LOOP;
END;
/

I have tried taking out the second select and just put in 'test' and I get the message "pl/SQL procedure successfully completed" but there is no output.

This is an oracle database and I'm using SQL developer.

CodePudding user response：

The simplest thing that might possibly work would be

declare
  l_sql_stmt varchar2(4000);
  l_value    varchar2(4000);
begin
  for i in (select owner, table_name, column_name
              from all_tab_columns)
  loop
    l_sql_stmt := 'select ' || i.column_name ||
                  '  from ' || i.owner || '.' || i.table_name ||
                  ' fetch first row only';
    execute immediate l_sql_stmt
       into l_value;
    dbms_output.put_line( l_value );
  end loop;
end;

However, this only works if every column in every table you have access to can be implicitly converted to a varchar2(4000). If you have lob columns that exceed the 4000 byte length or you have columns with complex data types, you'll get an error. Since you're doing implicit conversion, you'll get different results for numbers, dates and timestamps depending on your session's NLS settings which might or might not be problematic for you. It would generally be sensible to at least add a predicate to the all_tab_columns query in the cursor to only select those columns whose data types you are prepared to handle.

If you wanted to get more sophisticated, you could use the dbms_sql package to describe the results and fetch the data into a local variable of the appropriate data type. That lets you fix the implicit conversion issue and would let you handle more data types (though, for example, I don't know what you'd want to display for a blob column) but it involves writing quite a bit more code and you'd have to explicitly handle every data type you want to be able to deal with.

Depending on why you're trying to do this, it may make more sense to use the column-level statistics the optimizer has gathered at least for columns that have statistics (but those are probably the columns you'd actually care about having example data for).