Question

Emptying an Oracle table without the right to use Truncate

  • 25 July 2016
  • 4 replies
  • 19 views

Hi,

I have several workbenches which read an FGDB, do a little transforming and renaming, then insert the data in several Oracle tables (one table per fetaureclass, 15 in total). Before the data is written, the tables must be emptied. The trick is that the user is not allowed the privilege to use truncate (if you give an Oracle user the privilege to truncate, that user can also drop tables - which is not allowed).

I have a procedure which allows me to truncate, using a different user, but it looks like I would need to call that procedure 15 times in the "SQL statementto execute before translation", instead of adding it to the format parameters of every featureclass. Is that correct? Not really what I was hoping for, but if it needs to be done it needs to be done :-)


4 replies

Badge +10

Can you use an sqlexecutor to delete all records in the table, prior to writing?

e.g. delete from Table1 where id > 0;

Badge +22

Can you use an sqlexecutor to delete all records in the table, prior to writing?

e.g. delete from Table1 where id > 0;

If any of the tables use an Identity column (auto increment) that will not be reset as would be the case if the table was actually truncated. No idea if it's relevant though.

 

 

I would probably try something with a stored proc with the table to truncate as a parameter, and then read the schema of the FGDB to get the featureclass(es) to trigger the stored proc. That way you don't have to explicitly call the procedure 15 times in the SQL to execute, and if ever your FGDB doesn't have a feature class then that table is not emptied* and if you add a 16th feature class, nothing needs to change on the drop tables side.

* if that is not the desired behaviour, ie you want all 15 tables to be dropped even if only one table is then filled by the fgdb, then I would have a stored proc that drops all 15 tables, and that get's called once in th SQL statements to execute before translataion

Badge +2

Hi,

I don't understand the reason behind not providing the rights when we are using stipulated/predefined commands.

Normally I would suggest to prepare a script with a password protected (don't inform the content to user who use it) to truncate the table and execute in-line before the required workspace using workspacerunner

Do note that you have to execute only once

Badge +2

@stefan

Why do you need to call it 15 times? As others have said, it really depends on the scenario you want when loading the data - always truncate all table, or only truncate a table if there is data to be loaded.

 

You could add a Sampler transformer and a Sample Rate = 1, Sampling Type = First N Features and Group By fme_feature_type so you get one of every class and route the into the SQLExecutor. That way you only truncate a table when loading data.

 

 

Reply