Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying that table or view does not exist. the details are :

ORA-06550: line 8, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

The Script is :

a number := 0;
x number := 0;
i number := 0;
tablename varchar2(100):= 'FILES';
columnname varchar2(100):= 'FILESIZE';
for  j in (select columnname from tablename) loop
  a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(j.columnname));
    i := i+1;
  x:= x + a;
end loop;

The table name is FILES. And the column name is FILESIZES.

Can you suggest what I am doing wrong. And what can I do to find the size of long Column??


share|improve this question
up vote 6 down vote accepted

The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this. Next to that, if possible, forget about LONG and implement lobs instead.

For docu see

sample code slightly modified to fit your needs:

  v_emp_cursor    EmpCurTyp;
  v_stmt_str      VARCHAR2(200);
  z               long;
  x               number := 0;
  a               number;
  tablename       varchar2(100):= 'dba_views';
  columnname       varchar2(100):= 'text';
  -- Dynamic SQL statement
  v_stmt_str := 'SELECT '||columnname||' from '||tablename;
  -- Open cursor
  OPEN v_emp_cursor FOR v_stmt_str;
  -- Fetch rows from result set one at a time:
      FETCH v_emp_cursor INTO z;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      x:= x+a;
  -- Close cursor:
  CLOSE v_emp_cursor;
  dbms_output.put_line (x);
share|improve this answer
Ok, can you tell me which all places to put execute immediate ? So that the script runs properly – Egalitarian Mar 25 '11 at 11:41
sure, updated answer – ik_zelf Mar 25 '11 at 13:15
Thanks a Xillion – Egalitarian Mar 26 '11 at 20:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.