topic Re: tDBRow query in a Oracle DB in Talend Studio

tDBRow query in a Oracle DB

RVeitch_84 — Fri, 15 Nov 2024 21:52:34 GMT

Looking for some help trying to run the following query to apply grants to two different tables.

GRANT INDEX, SELECT, READ ON EVO.RAW_TRANSACTIONS TO OAX$OAC , OAX_USER ;

GRANT INDEX, SELECT, READ ON EVO.UCR_TRANS_EVO TO OAX$OAC , OAX_USER ;

When I try to run the above in tDBRow I get the following error

tDBRow_1 - ORA-00933: SQL command not properly ended.

Thanks againg for any help.

Re: tDBRow query in a Oracle DB

Anonymous — Sun, 23 Apr 2023 02:19:15 GMT

Hello @Robert Veitch ,

tDBRow only support to execute one query statement.

For your case, please use 2 tDBRow components and one with query

GRANT INDEX, SELECT, READ ON EVO.RAW_TRANSACTIONS TO OAX$OAC , OAX_USER

and another one with query

GRANT INDEX, SELECT, READ ON EVO.UCR_TRANS_EVO TO OAX$OAC , OAX_USER

NOTE, please don't add ; in the query

Re: tDBRow query in a Oracle DB

RVeitch_84 — Sun, 23 Apr 2023 11:25:48 GMT

I guess, I'm looking for another solution, because I have over 50 tables to apply grants to, and I really don't want to have 50 individual components?. Let me know if you have another solution?

Re: tDBRow query in a Oracle DB

Anonymous — Mon, 24 Apr 2023 00:28:54 GMT

I think the simplest way is to apply the grants statements in the sql-developer while connecting to the oracle db.

Best regards

Aiming

Re: tDBRow query in a Oracle DB

Anonymous — Mon, 24 Apr 2023 01:08:25 GMT

try this

"begin

GRANT INDEX, SELECT, READ ON EVO.RAW_TRANSACTIONS TO OAX$OAC , OAX_USER ;

GRANT INDEX, SELECT, READ ON EVO.UCR_TRANS_EVO TO OAX$OAC , OAX_USER ;

commit;

end;"

Re: tDBRow query in a Oracle DB

RVeitch_84 — Mon, 24 Apr 2023 12:53:41 GMT

I tried that and got the following error...

ORA-06550: line 3, column 1:

PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma

raise return select update while with <an identifier>

<a double-quoted delimited-identifier> <a bind variable> <<

continue close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe purge

json_exists json_value json_query json_object json_array

[ERROR] 07:52:54 talendjobs.test_grant_0_1.test_grant- tDBRow_1 - ORA-06550: line 3, column 1:

PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma

raise return select update while with <an identifier>

<a double-quoted delimited-identifier> <a bind variable> <<

continue close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe purge

json_exists json_value json_query json_object json_array

Re: tDBRow query in a Oracle DB

RVeitch_84 — Mon, 24 Apr 2023 12:56:21 GMT

I'm currently applying the grants with a script with my Oracle cilent software, I was looking for a solution in Talend Studio!

Re: tDBRow query in a Oracle DB

jlolling — Mon, 24 Apr 2023 18:56:37 GMT

There is a very simple solution: Use the component tSQLScriptParser. This component can parse SQL scripts and separate the statements and you can use its Iterate flow to a tDBRow component to run the single statements without any problems.

https://github.com/jlolling/talendcomp_tSQLScriptParser

Download the component version 1.7 from the Release section,

You can define the SQL script (just like you would do a all other db related components but without the limited to only one statement) inside the component or read simply an SQL file.