Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 :

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

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??

Thanks.

share|improve this question
add comment

1 Answer

up vote 5 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 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

sample code slightly modified to fit your needs:

decLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  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';
BEGIN
  -- 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:
  LOOP
      FETCH v_emp_cursor INTO z;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(z));
      x:= x+a;
  END LOOP;
  -- Close cursor:
  CLOSE v_emp_cursor;
  dbms_output.put_line (x);
END;
/
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
add comment

Your Answer

 
discard

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.