Truncate a table in Oracle Database before Ingestion in Data Factory V2-CodePudding

I'm working on a Ingestion Flow where we ingest the data from csv file to Oracle database. This is a truncate and load. So we should truncate the table before it is loaded. Trying to execute the below SP in the Lookup activity of ADF

BEGIN
execute oracle.cml_trunc_table('SCHEMA','TABLE_NAME')";
END;

Throws the below Error

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

:= . ( @ % ; immediate
The symbol ":=" was substituted for "ORACLE" to continue.

Also tried removing the "ORACLE" keyword and it failed with Invalid SQL/ Is there any other approach to execute the truncate table statement

Thanks in advance

CodePudding user response：

I don't know anything about Azure, but - as error you got is related to Oracle, it says that you've used invalid syntax.

It looks like that you're trying to call a procedure named cml_trunc_table which is a) owned by Oracle user named oracle, or b) part of a package named oracle.

execute, on the other hand, looks as if you tried to a) execute that procedure at SQL*Plus prompt, or b) run dynamic SQL (but then you have to use execute immediate - that's why error says that "immediate" might be missing).

From my point of view, you should try one of these:

Remove execute, entirely:

begin
  oracle.cms_trunc_table('SCHEMA', 'TABLE_NAME');
end;
/

or (if you're running it at SQL*Plus prompt - I doubt you are):

exec oracle.cms_trunc_table('SCHEMA', 'TABLE_NAME');

or use dynamic SQL, but then you'd just truncate the table, not call the procedure:

begin
  execute immediate 'truncate table schema.table_name';
end;
/

If I had to bet, I'd put my money on the 1st option I posted.

CodePudding user response：

One more thing is that the lookup activity expects some values to be returned back . Pardon my zero knowldge on PL/SQL , but in the tSQL works , I will return some values , so the last line of stored procedure should hhave somelike

SELECT somecolumn .