2

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Egalitarian
  • 2,168
  • 7
  • 24
  • 33

1 Answers1

6

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;
/
  • Now I get this error : ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 – Egalitarian Mar 25 '11 at 10:54
  • Your SELECT expects only one row but you have many, so you need to filter it with a WHERE clause to get only one. If you want to get the length of many rows you will need to use a cursor instead. Alternatively you can make this into a function and then use that in a normal select statement. –  Mar 25 '11 at 12:27
  • It had been a while since I used DBMS_OUTPUT package.. remember to do "set serveroutput on" - also: unfortunately this method fails when LONG column data exceeds 32k (at least for my 32-bit SQL*Plus client). To detect length for larger LONG values I had to use this solution: http://stackoverflow.com/questions/5497238/get-the-length-of-a-long-raw – nothingisnecessary Jul 30 '13 at 21:57