EXECUTE IMMEDIATE with c# String via OracleParameter, quote scaping

Question

I'm currently working on an assignment. I need to be able to execute a SQL statement via an stored procedure in Oracle.

I can do it within SQLDeveloper, using something like:

declare
n varchar2(100);
begin
   n := 'insert into REGION(ID_REGION, NOMBRE_REGION) values(10,''DESAMPA'')';
   EXECUTE IMMEDIATE n;
end;

The region table is pretty simple, an id and a varchar. That block works in SQL Developer. However, I need to call it inside this stored procedure:

Procedure Hacer_Query (P_Query in Varchar2)
AS
EXECUTE IMMEDIATE P_Query;
END;

This procedure also works. However, I'm having trouble calling this procedure from C#. For some reason, I'm getting ORA-00900 invalid SQL errors. I believe it may have something to do with the scaping of the single quotes, since

SELECT nombre_region FROM REGION:

Works from C# when I store it in a cursor. I'm using OracleParameter to send this to the procedure:

"insert into REGION(ID_REGION, NOMBRE_REGION) values(11,'DESAMPA')"

Is there some special way to do this? Any special syntax for the quotes?

Thanks for the help!

EDIT:

EXECUTE IMMEDIATE  'insert into REGION(ID_REGION, NOMBRE_REGION) values(10,''DESAMPA'')';

does not work. It seems it only works from within a block. I feel like I'm missing something here...

Nevermind that last part, I am now calling it from a block. Still doesn't work from C# however.

Answer 1

Try to make a function that calls it, and use this function in your Stored Procedure.

Function sounds like this:

CREATE OR REPLACE FUNCTION SISADMIN.EXECSQL(SQL1 VARCHAR2) RETURN VARCHAR2 IS
  FUNCTIONRESULT VARCHAR2(1000);
BEGIN
  EXECUTE IMMEDIATE SQL1;
  RETURN(FUNCTIONRESULT);
END EXECSQL;

DECLARE 
  TESTE VARCHAR2(100);
BEGIN 
  TESTE :=  SISADMIN.EXECSQL('insert into REGION(ID_REGION, NOMBRE_REGION) values(10,''DESAMPA'')');
END;

The function is not perfect, but you can adjust it.. well, it works!