Tagged: oracle schema package procedure
In a model, I want to run a stored procedure that is in a different schema than the one associated with my database authorization.
I thought that could be done by nesting an Operation in a <<SQLPackage>> Interface, and this one in a <<SQLDatabase>> Interface. However, when I run this it does not prepend the Name of the <<SQLDatabase>> Interface, it just calls PACKAGE.OPERATION, which is of course not found in the schema corresponding to my database user.
Is this an error in the E2E Bridge, and if not, what is the supposed purpose of the <<SQLDatabas>> Interface if not to supply the Schema Name?
And, how can I achieve calling a stored procedure with different Schema Name?
The schema is set on the SQLAlias, so the easiest way to achieve this would be to define a separate SQLAlias pointing to the other schema, and use this Alias in that SQLAdapter call.
If that’s not an option, you could try naming the SQLPackage by prepending the schema name, e.g. ‘OtherSchema.PackageName’. Not sure if it works, and I don’t have an Oracle DB within reach to test it right now. But should be quick to find out.
Does that help?
PS: whether this is a bug or a feature I can’t tell. Someone from the core dev team will have to answer that.
the problem is I need to use several procedures from several Schemas, and I want to avoid having an extra Connection for each one. Not only would this be wasteful and harder to configure and manage, but it also asking for trouble with the Transactions. Consider, doing A, then B, then C, then ROLLBACK on a single connection is different from doing A on connection1, B on connection2, C on connection1 and ROLLBACK on connection1. Moreover, step B on connection2 would not see the same state of the DB (namely, changes made but not yet committed by A would be missing) as B on connection1. So, no, this would result in a big mess with either data races or deadlocks.
The easiest work-around would be to create synonyms in the Schema I use, but, alas, the DB admins tell me this is “verboten” (period!).
Another work-around I’m successful with so far is to “ALTER SESSION SET CURRENT_SCHEMA = …” before every SQL Action.
But thank you for the tip, I’ll check if I can alter the Name and Report it here.
the trick with renaming the package in such a way that it contains the schema name seems to work.
So I’ll use this as a Workaround, many thanks for the idea!
Nevertheless, I’m curious what purpose an interface with the stereotype <<SQLDatabase>> actually has?
Glad to hear, and thanks for letting us know. I guess you will receive an answer to the latter question later this week.
If you want to access stored procedures from more than one schema it is the correct solution to use “schema.package” as name of <<SQLPackage>>.
If you want to access stored procedures from a single schema you can specify the tag *schema* on the <<SQLAlias>>. This is explained in the example sqlStoredProcedures.xml.
Stored procedures in the schema of the user or changed with alter session can be placed in the <<SQLDatabase>> interface.