lmcarreiro lmcarreiro - 4 months ago 65
SQL Question

Execute oracle DDL script with functions from c# code

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;

Answer

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?.)