ORA-00900: invalid SQL statement error?What's wrong with my sql?

Question

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;

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:

It looks fine. There must be something wrong in *how* you're running the script. What tool are you using and how are you applying the script exactly? — sstan, Jun 07 '16 at 15:58
@sstan,I upload the screenshot to the internet,please wait a minute,it is really wrong. — flower, Jun 07 '16 at 16:02
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. — flower, Jun 07 '16 at 16:07
Don't use comments to add information - instead, edit the question and add it there. I've done it for you this time - next time, please remember. Thanks. — Bob Jarvis - Слава Україні, Jun 07 '16 at 16:11
Have you installed procedural option? check this http://www.techonthenet.com/oracle/errors/ora00900.php. Or this https://community.oracle.com/thread/469571?start=0&tstart=0 — Aris2World, Jun 07 '16 at 16:13
@Sarly I also found this http://stackoverflow.com/questions/14868229/how-to-install-procedural-option-in-oracle-11gr2. Maybe as someone else already suggested could be a problem of the client you're using. — Aris2World, Jun 07 '16 at 16:20

Alex Poole · Accepted Answer · 2016-06-07T16:36:33.673

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.

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.

According to the documentation:

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.

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: invalid SQL statement error?What's wrong with my sql?

1 Answers1

Linked