tDBRow query in a Oracle DB

[RVeitch_84]

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.

[Anonymous]

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

[RVeitch_84]

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?

[Anonymous]

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

Best regards

Aiming

[Anonymous]

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;"

[RVeitch_84]

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

[RVeitch_84]

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

[jlolling]

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.