Procedure to calculate column sizes after data load

From: Ric Sullivan <Ric_Sullivan_at_Harvard.edu>
Date: Sun, 29 Nov 1998 15:17:27 -0600
Message-ID: <3661B9E7.FD71E4CF_at_Harvard.edu>

Hi,

After loading data into tables, I'd like to write a procedure to calculate the appropriate column sizes based upon the MAX(LENGTH(column_name)) for each column in each table in a schema. I'd like to do it with a procedure rather than generated SQL.

Here's what I did:

CREATE OR REPLACE PROCEDURE get_max_col_sizes AS  c1 IS SELECT

           a.table_name
          ,a.column_name
          FROM
           user_tab_columns a
          WHERE
                   owner = 'ME';

col_lngth NUMBER := 0;
BEGIN
  FOR utc_rec IN c1 LOOP
   SELECT
    MAX(LENGTH(utc_rec.column_name)
   INTO lngth
   FROM
    utc_rec.table_name;
 INSERT INTO
  max_col_sizes VALUES(utc_rec.table_name, utcrec.column_name,lngth);   END LOOP;
END; The error I get is that the table specified in the FROM clause of the procedure body must reference a table to which the user has access. It appears that Oracle is interpreting
 FROM
  utc_rec.table_name

as a literal and not as a name of a table, whose columns I'd like to access to find the maximum length of each column's data. How can a have the utcrec.table_name and utcrec.column_name be interpreted as table and column names rather than string literals or is there some other problem?

Any ideas?

--
Ric Sullivan
Harvard University

Ric_Sullivan_at_Harvard.edu

Received on Sun Nov 29 1998 - 22:17:27 CET