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)

Fede E.
  • 2,118
  • 4
  • 23
  • 39
  • 1
    The question is, why do you need to do the checking when the DB does it for you automatically? – Steve Jul 30 '18 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. – Fede E. Jul 30 '18 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 – Steve Jul 30 '18 at 16:29
  • Check this: https://stackoverflow.com/a/20745645/836215. Implementing full coverage for Oracle's DDL would be a nightmare. – ibre5041 Jul 30 '18 at 16:32
  • What is the purpose of this code? – Wernfried Domscheit Jul 30 '18 at 17:23
  • @Steve I understand, but these are basically 1 or 2 line blocks that devs do to databases, which are versioned along with the commit. The idea is to validate the database changes without needing to build the entire database and then run the change the dev just made. With a huge team, it get's hard to recreate the DBs for each commit. – Fede E. Jul 30 '18 at 17:50
  • @FedeE. you only need the schema not the data itself. without the database your query validation might miss things like typo names and duplicate names. you can just store a copy of the db some where on the build server and do the explain command. It won't modify the DB at all so its 100% safe – Steve Jul 30 '18 at 18:39
  • 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. – Fede E. Jul 30 '18 at 18:41
  • @FedeE. which is exactly what you want to do IMO. you want to validate the sql script actually work on the given schema instead of just the grammar is correct – Steve Jul 30 '18 at 19:20
  • @WernfriedDomscheit: The purpose of the code is obviously to fix a sequence value. We have a similar statement that we use for certain backups. It's a design flaw of Oracle that the sequence value can only be changed with a DDL command and is captured by the backup as part of the DDL and not in a state consistent with the table data. – Codo Jul 30 '18 at 19:41
  • @code if that code is supposed to fix sequence value, then the better approach is to alter the seqences's step. Then call seq.nextal and then alter the step to previous value. DROP&CREATE might loose privs, invalidate PL/SQL packages, ... – ibre5041 Jul 30 '18 at 19:45

0 Answers0