NHibernate calling Oracle stored procedure with schema prefix, how?

Question

I have NHibernate calling stored procedure in Oracle working currently. However, how do I specify a schema prefix in the {call} syntax in the tag?

I tried

<sql-query name="my_sproc">
<return class="my_sproc_class" />
    {call schema2.my_sproc (:p1)}
</sql-query>

But NHibernate run-time came back with 'schema2' not defined while 'schema2' is a definitely defined schema on my Oracle db.

thanks.

Answer 1

Could it be privileges ? The procedure may exist in that schema but you may not have privileges to execute it, or you may have privileges through a role that isn't enabled.

Could be parameters. If the procedure expects two parameters (or is a function) then trying to call it with one can get a "Doesn't exist" error when it really means "There isn't one I can call with just one parameter".

Final option is if you have a package in your schema with the same name as the other schema. Can happen with something generic like 'UTILS'. If you ask Oracle to execute UTILS.PROC and you have a UTILS package, then it will look in the package and throw an error if it doesn't find it, even if there is a UTILS schema with a procedure PROC.

---

Edited to add

In that case, I'm leaning towards a parameters issue The example here seems to use ? as the parameter placeholder.

Answer 2

I could be mistaken here, but I thought that NHibernate requires all UDFs/SPROCS to be prefixed with "dbo"; does {call dbo.schema2.my_sproc (:p1)} work?