Execute oracle DDL script with functions from c# code

Question

I've some integration tests written in C# code using a Oracle Database. The test project has a CreateDatabase.sql that contains the DDL to create the entire database in each test execution.

When I had only sequences and tables, I was splitting the content of this file in ";" char and executing each create statement separately, but now I've some functions and their statements contains some ; chars in it, so I can't use this approach anymore.

I've checked on .NET / Oracle: How to execute a script with DDL statements programmatically question, but it did not help.

1) If I try to execute the entire file content in a single OracleCommand, I get an error ORA-00911: invalid character because of the ; chars

2) If I try to wrap the file content in a "begin {0} end;" I get an error PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ...

3) I could try to parse the SQL file and put each statement inside a EXECUTE IMMEDIATE, but it will be harder...

Is there another option?

I'm using the Oracle.DataAccess version 4.112.3.0 to execute the commands.

EDIT @kevinsky ask for a script, here it is a simplified example... the entire script create hundreds of objects...

CREATE SEQUENCE SQ_ARAN_SQ_ARQUIVO_ANEXO;

CREATE OR REPLACE FUNCTION UFC_SPSW_DISP_COMPOSICAO(p_id_composicao IN NUMBER) RETURN NUMBER
IS
   retorno NUMBER:= 0;
   numeroItens NUMBER;
   temDefinicao BOOLEAN := false;

   CURSOR item_cur is
    select idc.itdc_sq_item_definicao_composi, idc.insu_sq_insumo, idc.comp_sq_composicao, idc.itdc_nr_coeficiente, idc.COMP_DS_COMPOSICAO, idc.comp_sq_composicao_pai
    from item_definicao_composicao idc;
  BEGIN  
    FOR item_rec IN item_cur LOOP
      temDefinicao := true;
      IF (item_rec.itdc_nr_coeficiente is null) THEN
        RETURN null;
      ELSE
        IF (item_rec.insu_sq_insumo is null) THEN
          numeroItens := UFC_SPSW_DISP_COMPOSICAO(nvl(item_rec.comp_sq_composicao_pai, item_rec.comp_sq_composicao));
        else
          retorno := retorno + 1;
        END IF;
      END IF;
    END LOOP;
    IF (temDefinicao = false) THEN
       RETURN 0;
    END IF;
    RETURN retorno;
  END;

CREATE SEQUENCE SQ_CALC_SQ_CALCULO;

If SQL*Plus is an option you can just invoke it using C# to execute the whole script. There also are few parsers available that are more or less usable for parsing the script into elemental commands. — Husqvik, Aug 10 '16 at 18:14
Please show us the script. If you have a Create file this is Data Definition Language and has to be wrapped in EXECUTE IMMEDIATE — kevinskio, Aug 10 '16 at 18:14
I've never used SQL*Plus, I'll search about it... I'm considering to split only when the `;` is followed by a token that is the begining of a DDL statement, like CREATE | ALTER | DROP | ... instead of using a rich parser that knows the syntax of each SQL Statement — lmcarreiro, Aug 10 '16 at 19:37

score 1 · Answer 1 · edited May 23 '17 at 11:51

Here is an idea. Make the front slash / (alone on a separate line) your new standard way of terminating every statement in your script instead of relying on the semi colon. For instance, your sample script could become:

CREATE SEQUENCE SQ_ARAN_SQ_ARQUIVO_ANEXO
/

CREATE OR REPLACE FUNCTION UFC_SPSW_DISP_COMPOSICAO(p_id_composicao IN NUMBER) RETURN NUMBER
IS
   retorno NUMBER:= 0;
   numeroItens NUMBER;
   temDefinicao BOOLEAN := false;

   CURSOR item_cur is
    select idc.itdc_sq_item_definicao_composi, idc.insu_sq_insumo, idc.comp_sq_composicao, idc.itdc_nr_coeficiente, idc.COMP_DS_COMPOSICAO, idc.comp_sq_composicao_pai
    from item_definicao_composicao idc;
  BEGIN  
    FOR item_rec IN item_cur LOOP
      temDefinicao := true;
      IF (item_rec.itdc_nr_coeficiente is null) THEN
        RETURN null;
      ELSE
        IF (item_rec.insu_sq_insumo is null) THEN
          numeroItens := UFC_SPSW_DISP_COMPOSICAO(nvl(item_rec.comp_sq_composicao_pai, item_rec.comp_sq_composicao));
        else
          retorno := retorno + 1;
        END IF;
      END IF;
    END LOOP;
    IF (temDefinicao = false) THEN
       RETURN 0;
    END IF;
    RETURN retorno;
  END;
/

CREATE SEQUENCE SQ_CALC_SQ_CALCULO
/

By using the /, your script remains perfectly valid if you wish to run it using SQL*Plus. But it now has the advantage that it becomes trivial to parse by statement in C# so that you can execute each statement separately without the semi colon problems.

I've used this technique in the past and it has worked well.

(Relevant reading in case you are not familiar with the use of the slash in Oracle SQL scripts: When do I need to use a semicolon vs a slash in Oracle SQL?.)

Thanks, it would resolve my problem... but I don't want to change my script, I will use my uglier solution that I talked about in the question comment... I'll post here soon — lmcarreiro, Aug 10 '16 at 22:24

score 1 · Accepted Answer · answered Oct 13 '17 at 11:56

I had the same prolem and solved. Use both 'BEGIN END' and 'EXECUTE IMMEDIATE'.

This is my test (success case)

begin
    EXECUTE IMMEDIATE 'create or replace procedure SP_JHKIM2 IS
        begin
            dbms_output.put_line(''ABC'');
        end;';
end;

score 0 · Answer 3 · answered Aug 10 '16 at 22:34

I split only on ; that was followed by some reserved words (or in the end of the file) using regex lookahead assertion.

Ex:

var statements = Regex.Split(
    fileContent,
    @"\s*;\s*(?=(?:CREATE|ALTER|DROP|RENAME|TRUNCATE)\s|\s*$)",
    RegexOptions.IgnoreCase);

Execute oracle DDL script with functions from c# code

3 Answers3