Destination Oracle: Allow to connect using service name and SID

[walkabout21]

Tell us about the problem you're trying to solve

What are you trying to do, and why is it hard? A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]
My oracle DB uses service instead of SID so Oracle connector doesn't work

Describe the solution you’d like

A clear and concise description of what you want to see happen, or the change you would like to see
connector includes options for Service name but GUI does not. Add service to GUI

Describe the alternative you’ve considered or used

A clear and concise description of any alternative solutions or features you've considered or are using today.
The are no alternative options

Additional context

Add any other context or screenshots about the feature request here.

existing code in connector

      connectionString = switch (connectionType) {
        case SERVICE_NAME -> buildConnectionString(config, protocol.toString(), SERVICE_NAME.toUpperCase(),
            config.get(CONNECTION_DATA).get(SERVICE_NAME).asText());
        case SID -> buildConnectionString(config, protocol.toString(), SID.toUpperCase(), config.get(CONNECTION_DATA).get(SID).asText());
        default -> throw new IllegalArgumentException("Unrecognized connection type: " + connectionType);
      };
    } else {
      // To keep backward compatibility with existing connectors which doesn't have connection_data
      // and use only sid.
      connectionString = buildConnectionString(config, protocol.toString(), SID.toUpperCase(), config.get(SID).asText());
    }

Are you willing to submit a PR?

I am not a front end developer

We accept contributions!
Don't feel pressured, but if you want to contribute we can help you by giving some tips,
highlighting the necessary code change or explaining any relevant point your feature will impact.
You can also send questions on #dev Slack channel.

We understand if you can't submit a PR and we're tremendously grateful
that you've already contributed by suggesting a new feature.
-->
Remove this with your answer :-)

[walkabout21]

Oracle source gui includes option to pick service vs sid but it is not included for oracle destination

[walkabout21]

I believe this is actually a bug, and not a new feature request after reviewing the source functionality and the connector code

[marcosmarxm]

This is a feature request, source and destination code are different. I added the request to connector team backlog.

[walkabout21]

Looks like there have been two requests for this feature now. Any movement on it? We can't implement without it and I assume most Oracle targets beyond 12c will have the same requirement.

[marcosmarxm]

Today Oracle isn't in Airbyte roadmap for Q1 2023 @walkabout21 so this won't be prioritized for now. @grishick maybe you have a different info, let us now!

[kolfild26]

Hi, I faced with the same issue, i.e not able to use Service name for an Oracle destination connector.
So, could anyone know if there is any updates of this?
Or any temporary workarounds (?) also would be very glad for sharing them.

[johnspackman]

Looks like there have been two requests for this feature now. Any movement on it? We can't implement without it and I assume most Oracle targets beyond 12c will have the same requirement.

This is true - oracle pluggable databases are a requirement now and that means that Oracle must connect with service name and not SID.

Service names have been preferred since something like Oracle 8! (Current version of Oracle is 21.)

AIUI, without this feature, Oracle cannot be used as a destination for Airbyte (though I'd be very pleased to be proven wrong)

[kolfild26]

@johnspackman
It turned out we could switch to Service Name from SID in sources and it works out.
Such workaround is acceptable imho.

[progammer3000]

Would you please kindly help me out in figuring how i can change it, as i tried to just change the "SID" to "SERVICE_NAME" in that java file but i was not successful.

[kolfild26]

Did you restart Airbyte after? Should be working then as it builds from sources while starting.

final String connectionString = String.format(
        //"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=%s)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SID=%s)))",
	"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=%s)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SERVICE_NAME=%s)))",
        protocol,
        config.get(JdbcUtils.HOST_KEY).asText(),
        config.get(JdbcUtils.PORT_KEY).asText(),
        config.get("sid").asText());

p.s. Keep in mind that this workaround doesn't affect the user interface. SID (not the SERVICE NAME) will remain on the settings page.
p.p.s. You also should not change this:

L95 config.get("sid").asText());

Just one line above.
Hope that helps.

[progammer3000]

Thank you for your reply @kolfild26 , I truly appreciate it. I have indeed changed it in accordance to your reply above, however i suspect it may be due ot the container itself? What i mean is, i changed that file saved it, stopped the container and started it again yet i still faced the same issue (and yes i idd not changed this part here (L95 config.get("sid").asText());). Thanks again

[kolfild26]

however i suspect it may be due ot the container itself?

Where do you change the source file? It's supposed you change it in your local airbyte repository which you have cloned.
I'm installing on a local machine. So as the docs says I run .sh script to start all the components. It runs multiple containers, not just one. But I don't even directly touch any of them. I change the sources in repo and restart the bash script.

[stevenkitter]

can you guys build a image to public？

[pvdkooi]

if possible you can set a paramter in the listener config :USE_SID_AS_SERVICE_LISTENER=on
Service is now exposed as a SID also

[tolkunb]

This is the easiest workaround. If you're running Oracle on docker you can attach to your container and update listener.ora easily.
Most probably under /opt/oracle/product/{ORACLE_VERSION}/dbhome_1/network/admin

[itgkarl]

Is there any news on this topic?

We would love to try Airbyte.
Unfortunately we are blocked as we are only allowed to use "service" for connections to our Oracle databases.

Is there any chance that this feature will be included in the roadmap for 2025?