ORA-00900:无效的SQL语句错误?我的SQL怎么了? [英] ORA-00900: invalid SQL statement error?What's wrong with my sql?

问题描述

我尝试编写一个拆分字符串的函数，但显示为:ORA-00900: invalid SQL statement error.怎么了?我认为v_str varchar2(500);或v_strs_last varchar2(4000) := p_value;可能是错误的.

I try to write a function that split the string,but it shows:ORA-00900: invalid SQL statement error.What's wrong?I think that v_str varchar2(500); or v_strs_last varchar2(4000) := p_value; may be wrong.

CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (4000);

create or replace function strsplit(p_value varchar2,
                                p_split varchar2 := ',') 
return strsplit_type
pipelined is
v_idx    integer;
v_str  varchar2(500);
v_strs_last varchar2(4000) := p_value; 
begin
  loop
    v_idx := instr(v_strs_last, p_split);
    exit when v_idx = 0;
    v_str       := substr(v_strs_last, 1, v_idx - 1);
    v_strs_last := substr(v_strs_last, v_idx + 1);
    pipe row(v_str);
   end loop;
pipe row(v_strs_last);
return; 
end strsplit;

我的oracle版本是Oracle Database 11g企业版11.2.0.3.0版-64位生产版本.我在DB SOLO 5中运行该脚本. 错误图片是:

My oracle version is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.And I run the script in DB SOLO 5. The error picture is:

推荐答案

您的DB Solo客户端似乎正在解释该语句末尾的第一个分号，这对于纯SQL(DML或DDL)而言是合理的，但不适用用于PL/SQL.

Your DB Solo client seems to be interpreting the first semicolon it sees as the end of the statement, which is reasonable for plain SQL (DML or DDL) but not for PL/SQL.

您可以从发布的日志图像中看到这一点；它将create function ... v_ids integer部分视为一个语句，因为它以第一个分号结尾-编译但出现错误.然后它将下一个数据块(直到下一个分号)作为一个单独的语句-v_str varchar2(5000)-并得到ORA-00900，因为它不是有效的SQL.

You can see that from the log image you posted; it treats the create function ... v_ids integer part as one statement because that ends with the first semicolon - it compiles but with an error. Then it takes the next chunk, up to the next semicolon, as a separate statement - v_str varchar2(5000) - and it's that which gets the ORA-00900, since it is not valid SQL.

根据文档:

语句必须用分号或'GO'关键字分隔.您可以将设置更改为仅使用分号或'GO'作为语句分隔符，而不使用默认设置(接受其中的任一个).当您在查询编辑器中有多个语句时，DB Solo会将它们一次发送到您的服务器.

The statements must be separated by either semicolon or the 'GO' keyword. You can change the settings to only use a semicolon or 'GO' as statement separator instead of the default setting which accepts either one. When you have more than one statement in the Query Editor, DB Solo will send them to your server one at a time.

因此，基于此似乎似乎不了解如何以不同的方式对待PL/SQL.但是您可以更改设置以不将分号当作语句分隔符-全面说明，这意味着您需要在create type和create function语句以及您进行的任何其他查询或调用之后添加GO.这类似于在SQL * Plus或SQL Developer中到处使用/.

So based on that it doesn't seem to understand how to treat PL/SQL differently; but you can change your settings to not treat the semicolons as statement separators - across the board, which means you'd need to add GO after both the create type and create function statements, and any other queries or calls you make. This would be similar to using / everywhere in SQL*Plus or SQL Developer.

使用过程编辑器可能会更容易.大概是在对象浏览器中创建类型和函数之后.

It may be easier to use the procedure editor. Presumably after you've created the type and function from the object browser.

或者，当然，请使用其他客户端...

Or, of course, use a different client...

这篇关于ORA-00900:无效的SQL语句错误?我的SQL怎么了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！