如何在表中查找长列的长度 [英] how to find length of a Long Column in a table

问题描述

我在名为Files的表中有一个名为FileSize的LONG列.

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

为了达到这个目的，我做了以下工作:

To achieve the objective, I did the following :

我写了这个PL/SQL脚本来查找大小

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;

但是它抛出一个错误:

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

我正在做以下事情，因为我在下面给出的链接上看到了以下内容: http://www.techonthenet.com/oracle/questions/long_length.php

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

谢谢.

推荐答案

在这种情况下，您不需要立即执行.

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从您的PL/SQL代码中运行一个独立的SQL语句.它无法将任何内容返回给您的代码.您使用的语句不是有效的SQL，因此您得到了ORA-00905.它是有效的PL/SQL代码，因此一旦删除EXECUTE IMMEDIATE，便可以按预期工作.

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.

修改

针对您所关注问题的代码:要在不止一行的情况下执行此操作，可以使用此

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

这篇关于如何在表中查找长列的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！