dotConnect Oracle and Entity Framework ExecuteStoreCommand

mservidio · Post by **mservidio** » Thu 29 Jul 2010 16:18

Hi All -

When I execute a direct command in entity framework using the ExecuteStoreCommand method, I am getting a "missing expression" exception. It seems the cause of this is that Oracle uses ":" for variables, and it looks as though the provider is actually passing "@" as variable name values to the oracle server. Could someone let me know if there is a way to change the default variable prefix or some other way around this? I definitely want to parameterize the query, so I don't want to hard code any values in the query.

Thanks,
Mark

mservidio · Post by **mservidio** » Thu 29 Jul 2010 22:04

I've figured out my issue. If {0}, {1}, etc... is used as placeholders for the parameterized variables that you pass through in your queries, then it replaces these with @p0, @p1, etc, which for oracle is not correct syntax. Oracle uses ":" to denote parameters. So the simple way around this is to name your variables :p1, :p2, etc.

AndreyR · Post by **AndreyR** » Fri 30 Jul 2010 16:38

You are right, the correct way is to use Oracle-specific placeholders for parameters in ExecuteStoreQuery.

rick.duarte · Post by **rick.duarte** » Tue 01 Feb 2011 15:35

I'm trying to get a nextval from sequence, but I'm getting -1.

Code: Select all

ctx.ExecuteStoreCommand("Select SBLOGS.IDANOTACAOSEQUENCE.NEXTVAL FROM DUAL");
-1

If I run this query on sqlplus I'm getting correct numbers: 21, 22, 23 ...
What could be causing this?
I'm using the latest version 6.10.

Best regards,

Henrique

AndreyR · Post by **AndreyR** » Wed 02 Feb 2011 17:26

Try using PL/SQL Block and Parameter in the following way instead:

Code: Select all

        Devart.Data.Oracle.OracleParameter param = new Devart.Data.Oracle.OracleParameter("p", Devart.Data.Oracle.OracleDbType.Integer);
        param.Direction = System.Data.ParameterDirection.Output;
        var q = context.ExecuteStoreCommand("begin select AUTOINC_SEQ.NEXTVAL into :p from dual; end;", param);