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.
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;
/