bildwelt
E2E FORUM
E2E Bridge E2E Commerce

access a stored procedure in a different schema (Oracle)

E2E Forum General Discussion access a stored procedure in a different schema (Oracle)

This topic contains 5 replies, has 3 voices, and was last updated by  Alfred 6 months, 2 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #3094

    Ingo
    Participant

    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?

    • This topic was modified 6 months, 2 weeks ago by  Ingo.
    #3096

    Dirk
    Moderator

    Hi Ingo,

    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?
    Dirk

    PS: whether this is a bug or a feature I can’t tell. Someone from the core dev team will have to answer that.

    #3097

    Ingo
    Participant

    Hi Dirk,

    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.

    #3098

    Ingo
    Participant

    Hi Dirk,

    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?

    #3099

    Dirk
    Moderator

    Hi Ingo,

    Glad to hear, and thanks for letting us know. I guess you will receive an answer to the latter question later this week.

    #3100

    Alfred
    Moderator

    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.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.