Skip to Main Content
  • Questions
  • Is it possible to create a private user under a DB schema?


Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: April 30, 2024 - 5:12 am UTC

Last updated: May 16, 2024 - 6:39 am UTC

Version: 18C

Viewed 1000+ times

You Asked

We are writing to discuss an operational challenge that we are currently facing with the integration of Oracle Integration Cloud (OIC) and Oracle Autonomous Database within our organization. Our setup utilizes OIC as the primary integration layer, in conjunction with the Autonomous Database for staging data, performing validations, and executing other data derivation tasks.

Our infrastructure includes multiple OIC environments, each configured to connect to the same Autonomous Database but utilizing distinct database schemas. These schemas primarily contain custom tables and packages essential for enforcing our business rules. During the integration process, particularly when invoking subroutines, it is necessary to specify the database name along with the package or procedure name, as detailed in the Oracle documentation ( ).

We encounter significant challenges when migrating integrations between different OIC environments due to the requirement of manually updating the schema name in each database activity to match the target environment's schema. This process is not only time-consuming but also prone to errors, impacting our efficiency and operational continuity.

In previous discussions with the Oracle Support team, the suggestion was made to utilize separate databases with identical schema names to circumvent this issue. However, due to resource constraints, expanding beyond our current setup of one database for production and another for non-production environments is not feasible.

Given these circumstances, we are reaching out to inquire if there might be an alternative solution or workaround that could facilitate a more streamlined migration process between OIC environments without the need for manual updates. Any suggestions or guidance you could provide would be greatly appreciated

and Connor said...

Is it conceivable to have a wrapper schema to look after this ?



OIC environment 1 calls SCHEMA1.MY_PROC
OIC environment 2 calls SCHEMA2.MY_PROC

We create a new schema OIC_ROOT with execution privs on all relevant schemas where we would then have

procedure OIC_ROOT.MY_PROC is
  if "environment" = OIC1 then
  elsif "environment" = OIC2 then

Ideally the determination of environment could be done without external parameters needed to be passed, eg, examining sys_context

In that way, whilst you have an extra layer of code in the database to look after, you never need to touch the OIC config because it always calls OIC_ROOT in all cases.

Others welcome to add their thoughts/ideas.


  (3 ratings)


Pradeep, May 15, 2024 - 1:53 am UTC

I dont think it is a feasible option.

When we create a connection in OIC, we have to specify a DB Username. So, the connectivity happens with same user. So, there is no way we can differentiate the users when connecting from different applications without an external parameter.

Connor McDonald
May 16, 2024 - 6:39 am UTC

But if you are, as you said, "migrating between environments" then do things like the hostname etc give you any info? Take a look at the docs for SYS_CONTEXT - there's a lot you can glean about the environment

Maybe some CI/CD?

Ramon Caballero, May 15, 2024 - 7:44 pm UTC

I know you are trying to solve it from the database perspective, and I tried too, maybe with default schema, maybe with private synonyms or on logon triggers but I think we are solving for the symptom and not the cause.
If we focus on the cause, you say when you make changes or migrate between environments (which is the same environment) you sometimes forget changes needed.
Why not automate deployment by grabbing your code, with the REST APIs of OIC, automate the change (text replacement per environment) and redeploy with, again, REST APIs?
I am not a developer but I think this is doable.

Connection is not a problem

Pradeep, May 16, 2024 - 5:26 am UTC

Changing the connection doesnt make any difference because connection is no only the place where we mention the schema name.

Schema is mentioned in each and every ATP DB activity within the OIC integration.

Till now we used to change the schema names directly in the IAR files (OIC Integration Archives)

But, in the recent upgrades Oracle has introduced a checksum validations on the IAR files. So, any IAR file updated from the backend will be corrupted.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library