topic Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection in Talend Studio

tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

art400 — Fri, 15 Nov 2024 22:06:03 GMT

Forgive me if there is something obvious that I am not seeing. I created a DB Connection for Oracle, and within it I specified the default Oracle schema. However, when I use SQL builder or data viewer, I get ORA-00942, "table or view does not exist". I can hard-code the schema to the tables and the query works, but it defeats the benefit of storing the default schema in the DB connection profile.

The Oracle DB connection values show in the context variables, with the required schema there. Obviously, I'd like to not have to hard-code my queries. Is there some trick I am missing? Using Talend Studio 8.0.1, Oracle 18.

Thanks,

Art

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

Anonymous — Wed, 15 Feb 2023 09:40:15 GMT

@Not defined Not defined, I'm afraid I don't understand your problem well, can you share a screenshot to explain it?

Regards

Shong

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

art400 — Fri, 17 Feb 2023 15:47:00 GMT

Shong,

I'm providing three screen shots. The first is the component configuration (tDBInput), which shows that I specified PRODLAW as the Oracle schema to use. The second shot shows the SQL Builder panel, where the query works if the two tables are qualified to PRODLAW. The third screen shot shows the ORA-00942 error when I remove the PRODLAW qualifications.

In most other JDBC access scenarios I've used, I've been able to set the default schema, therefore removing the need to hard-code schema names. This means I have one SQL script and I don't have to have two versions for dev and prod.

I am assuming that the reason for the "Oracle schema" entry in the DB set-up is to achieve this, and I see no other way to run an ALTER SESSION SET CURRENT SCHEMA command.

Can you either point out a way to make this happen, or let me know if I am stuck hard-coding in my SQL statements?

Thanks,

Art

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

art400 — Fri, 17 Feb 2023 15:48:13 GMT

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

art400 — Fri, 17 Feb 2023 15:48:44 GMT

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

art400 — Thu, 23 Feb 2023 17:58:02 GMT

Added screen shots and additional info, please review and reply

Re: tDBInput(Oracle) will not recognize Oracle Schema from DB Connection

Anonymous — Fri, 24 Feb 2023 02:34:37 GMT

if you don't specify schema, it will use the default schema of the user. Which version of studio are you using? I can't reproduce the issue on version 8.0.1

BTW, you can use a tOracleRow to run an ALTER SESSION SET CURRENT SCHEMA command. Using tOracleConnection to create a DB connection, use the already existing DB connection on tOracleRow and tOracleInput component.

Regards

Shong