2

We are using SQLParser to check for syntax errors in Oracle SQL sentences. I saw that the library supports PL/SQL, however, we are having issues parsing the following SQL:

declare
 newSeq number;
begin
  select MAX(id_organization)  + 1 into newSeq from c_organization;
  If newSeq > 0 then
    begin
            execute immediate 'DROP SEQUENCE S_C_ORGANIZATION';
      exception when others then
        null;
    end;
    execute immediate 'CREATE SEQUENCE S_C_ORGANIZATION INCREMENT BY 1 START WITH ' || newSeq ;
  end if;
end

Using their C# library with the following code:

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = sql;
if (parser.checkSyntax() > 0){
    Console.WriteLine(parser.Errormessage);
}

We get:

end of input(10102) near: (0,0) no_root_node(-1000) near: no root node(0,0) end of input(10102) near: (0,0) no_root_node(-1000) near: no root node(0,0)

Any idea if the library supports this kind of PL/SQL statements? (maybe we are doing something wrong)

11
  • 1
    The question is, why do you need to do the checking when the DB does it for you automatically? Commented Jul 30, 2018 at 16:22
  • @Steve we have a development team that creates patches for our databases. When we run those builds into our build server (TeamCity) we wan't to validate syntax without actually running the scripts. Commented Jul 30, 2018 at 16:24
  • 1
    you can simply issue an explain plan to the script and DB will do a compile. If it fails you get an error else you get a query plan which you can just ignore Commented Jul 30, 2018 at 16:29
  • Check this: stackoverflow.com/a/20745645/836215. Implementing full coverage for Oracle's DDL would be a nightmare. Commented Jul 30, 2018 at 16:32
  • 1
    @Steve yeah, thing is if a developer makes a schema change and then another developer does another change that depends on the first change, you need to apply the schema changes before running the second developer changes' or it will fail, even with the correct sql statements. Commented Jul 30, 2018 at 18:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.