Skip to main content
Question

Using FME to run an Oracle begin/End statement

fmelizard
Contributor
Forum|alt.badge.img+17
Hi,

 

I'm using the "SQL Statement to exectute at end of translation".

 

 

I've set it to:

 

FME_SQL_DELIMITER ;

 

... Other SQL...

 

begin DBMS_SNAPSHOT.REFRESH( '"OSMM"."OS_ABP_V_DELIVERY_ADDR"','C'); end;

 

 

This works fine in Oracle SQL Developer (ti's to refresh a materialized view). But when I run it via FME I get Oracle errors.

 

2014-08-12 16:43:46|   2.3|  0.0|ERROR |Execution of statement `begin DBMS_SNAPSHOT.REFRESH( '"OSMM"."OS_ABP_V_DELIVERY_ADDR"','C')' did not succeed; error was `ORA-06550: line 1, column 67:

 

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

 

 

   := . ( % ;'.

 

Removing the "End;" or the ";" or both does nothing, I just get a slightly different varient on the same error.

 

 

Is it possible to run such a statement like this?

 

Thanks,

 

Jonathan

4 replies

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Author
  • Contributor
  • August 12, 2014
use another FKE SQL delimiter than ; ( : for example)
takashi
Influencer
  • takashiInfluencer
  • August 12, 2014
Hi Jonathan,

 

 

Although I'm not familiar with Oracle syntax, the reason may be that FME considered the semi colon before "end" as the end of a statement.

 

How about changing delimiter character (FME_SQL_DELIMITER)?

 

 

Takashi
david_r
Celebrity
  • david_rCelebrity
  • August 13, 2014
Hi,

 

 

I recommend removing the whole line containing FME_SQL_DELIMITER. You do not need it for anonymous blocks.

 

 

FME will give you a warning about the semicolons in the log window, which you can ignore.

 

 

If your SQLExecutor has other DML statements that require FME_SQL_DELIMITER, I'd recommend splitting them out into a separate SQLExecutor.

 

 

David
D
Forum|alt.badge.img
  • dcam
  • August 13, 2014
I have tried running a similar scipt in the SQLCreator and I can't get it to work not matter what I try. I ended up splitting the script up into three seperate Creators but you have to be careful as to which creator runs first.

Reply


Helpful Members This Week

Community Stats

31,137
Posts
118,053
Replies
38,778
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.