Anyone know how to specify the schema for target-oracle? I am using tap-spreadsheets-anywhere as the extractor and target-oracle as the loader. I have set the load_schema in meltano.yml to be the schema I want the data to go into, but it still just loads into the schema for the user that I am logging into the db as.

Hi Christian! I think

load_schema

is unfortunately a noop for target-oracle. Even if it was supported, upon reading the docs, it seems like such operations would require too broad privileges, i.e.

CREATE ANY TABLE

. If you're still interested in it, I suggest opening an issue in target's issue tracker: https://github.com/radbrt/target-oracle/issues

thank you

Hi, I’m maintaining that tap, and it seems the target schema was simply left out of the documentation 😅 . The setting is simply called

schema

. You can try adding it as-is, but I’m not entirely certain it will work. I’m updating the docs, will let you know when it is done. After having looked for more than 10 seconds, it seems my undocumented schema setting is a dud, so it might take a little longer to fix.

@Henning Holgersen Thank you for looking into this, I appreciate it!

No problem. I’m a little busy the next two weeks so I’m afraid I can’t make any promises about when I will have anything, but now that the issue has marinated a little I do recall one issue you might know something about: From what I can remember from my time actually working with Oracle, schemas were tightly tied to users. I seem to remember a colleague of mine even giving up on a proposed design because she wasn’t able to grant User A the ability to create tables in the schema of User B. Do you know anything about that? Is this possible with normal grants? Is there perhaps some special grant type?

I am not sure the specific grants, but yeah, the user that accessing the database needs to have permissions to create tables, which can be granted for the user's own schema (same name as the username) or any other schema. It is also possible that a user could load into a pre-existing table with just insert, update, delete grants. I was considering a solution where the staging tables get created in the schema accessing the database but the final data get's loaded into the target schema. But I am not sure how difficult that would be in the plugin, I was going to poke around myself with it sometime this week, but in the meantime decided to move on to the transform step and start learning that (my organization is just starting out using meltano, so we have a lot of investigation to do)