how to find length of a Long Column in a table

Question

I have a LONG column named FileSize in a table called Files.

To achieve the objective, I did the following :

I wrote this PL/SQL script to find the size

declare 
long_var LONG:=0;
begin
dbms_output.put_line(length(long_var));
  execute immediate 'SELECT FILESIZE INTO long_var FROM FILES';
    dbms_output.put_line(length(long_var));
end;

But it throws an error :

ORA-00905: missing keyword
ORA-06512: at line 5

I was doing the following as I saw thw following on the link given below: http://www.techonthenet.com/oracle/questions/long_length.php

Can some one suggest what I am doing wrong as I can not identify the key word I am missing

Thanks.

Answer 1

You don't need EXECUTE IMMEDIATE in this context.

DECLARE 
long_var long:=0;
BEGIN
  DBMS_OUTPUT.PUT_LINE(LENGTH(long_var));
  SELECT filesize INTO long_var FROM files;
  DBMS_OUTPUT.PUT_LINE(LENGTH(long_var));
END;
/

EXECUTE IMMEDIATE runs a stand alone statement of SQL from your PL/SQL code. It can't return anything to your code. The statement you're using isn't valid SQL so you get the ORA-00905. It is valid PL/SQL code and so works as you'd expect once EXECUTE IMMEDIATE is removed.

Edit

Code for your follow on question: To do this with more than one row you can use this

DECLARE 
  CURSOR C1 IS
  SELECT filesize FROM files;
BEGIN
  FOR files IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE(LENGTH(files.filesize));
  END LOOP;
END;
/