Creating/update Oracle table using a sql procedure in FME...

You can do it using an anonymous block in the SQLExecutor, for example:

begin
    my_schema.my_package.my_function(my_parameter => '@Value(my_fme_attribute)');
end;

Attention: You should not use the FME_SQL_DELIMITER clause in this case.

@david_r. Thx a lot. I actually can call the procedure now, but it fails, because of some error in my arguments list - the first argument apparently, which is the primary key ??

But I can't see why??

An error occurred while accessing a table result for feature type `'; message was `Execution of statement `begin sch_driftssagervs.detail_merge.kontakt_p('53802', 'Kim Kruse', '29209139', '29209139', 'Driftsforstyrrelse', '20160524133000', 'migr1');

end;' did not succeed; error was `ORA-06550: line 1, column 51:

PLS-00363: expression '53802' cannot be used as an assignment target

ORA-06550: line 1, column 10:

end;' did not succeed; error was `ORA-06550: line 1, column 51:

PLS-00363: expression '53802' cannot be used as an assignment target

ORA-06550: line 1, column 10:

Based on the Oracle error message I'm guessing that the procedure "kontakt_p" expects an integer value as the first parameter (primary key?). Since you're passing the parameter with quotation marks around it, it is implicitely parsed as a string rather than an integer. So my recommandation would be to try removing the quotation marks around the first parameter.

So basically you do this:

begin
    my_schema.my_package.my_function(my_parameter => @Value(my_fme_attribute));
end;

Rather than this:

begin
    my_schema.my_package.my_function(my_parameter => '@Value(my_fme_attribute)');
end;

@david_r. Thanks again for your effort. However, it seems that no matter what notation, I use for the argument, I get the same error message. However, the argument is also defined as output. Can out or inout arguments in procedures be used at all in SQLExecutor?

It's difficult to tell without knowing the procedure declaration, but I would be surprised if it wasn't possible to call the procedure from FME, since FME basically just sends the SQL as a string to Oracle and let's the database handle the rest. So basically it's only up to you to "build" the SQL command string correctly in FME.

That said, have you tried something like

declare
  my_output_param number := @Value(my_fme_attribute);
begin
  my_schema.my_package.my_function(my_parameter => my_output_param);
end;

@david_r. Thx again - I'll try that too. But you're perfectly right - It's "just" a question of writing the SQL string in the correct syntax, and it puzzles me, that it should be so difficult.

Are you able (allowed) to post the function header here, so that we can se what the function expects?

You could also consider contacting your DBA and ask for a concrete example on how to call the stored procedure and post that here, if possible

@david_r: Looks "pretty much" like this:

procedure aaaaaa_p (

x in out number,

y in varchar2(200 byte),

z in number(20,0),

w in number(20,0),

p in Varchar(200 byte),

q in Date,

r in varchar2(10 byte) );

Is the error message still exactly the same, word for word?

Do you have a DBA you can discuss this issue with? It's difficult to debug without knowing exactly how things have been configured and how you're calling the procedure.

Another potential pitfall is the "date" parameter type. You may have to take extra care so that it comes in a format that Oracle understands. If your parameter is in the "FME date format", you can convert it to an Oracle date object like this

to_date('YYYYMMDD', '@Value(my_fme_date_attribute)')

or

to_date('YYYYMMDDHH24MISS', '@Value(my_fme_date_attribute)')

Hi again, Finally it works - however with some limitations.

It seems that you cannot have "in out" arguments in your procedure, if it's in a package (there is no return value).

And you can't parse an Oracle Date attribute directly as an argument in your procedure defined as Date.

Anyone who has some workarounds for these 'problems'?

Did you try my tips for handling date/timestamps below?

@david_r, Yes, I have tried several variants of your tips as argument in the procedure with or without 's. Either I get a syntax error, or I get an error message like this:

Attribute(string) : `_reader_error' has value `An error occurred while accessing a table result for feature type `'; message was `Execution of statement `begin fpo.package.xxxx(100005, 'Kim', '29209139', '29209139', 'Driftsforstyrrelse', 'migr1', to_date('YYYYMMDDHH24MISS', 20160524133000));

end;' did not succeed; error was `ORA-06550: line 1, column 116:

PLS-00307: too many declarations of 'TO_DATE' match this call

The call in the SQLExecutor looks like this:

FME_SQL_DELIMITER /

begin fpo.package.xxxx(@Value(sagsid), '@Value(N_ANMELDER)', '@Value(V_TELEFON)', '@Value(V_TELEFON)', '@Value(T_HAENDELSE_TYPE)', '@Value(init)', to_date('YYYYMMDDHH24MISS', @Value(D_START)));

end;

/

It looks like you've omitted the single quotation marks around the date value string. Try this:

 to_date('YYYYMMDDHH24MISS', '@Value(D_START)') 

 to_date('YYYYMMDDHH24MISS', '@Value(D_START)') 

@david_r. Thx a lot, David. I wish it was that easy. I have tried several combination using the quotation marks with no result. This time I got this message: 

did not succeed; error was `ORA-01821: date format not recognized

Sorry, looks like I had the parameters in the wrong order. This should work:

to_date('@Value(D_START)', 'YYYYMMDDHH24MISS') 

Documentation for the to_date function may also be useful: https://www.techonthenet.com/oracle/functions/to_date.php

Not that D_START will always need to have a value, it cannot be empy or missing.

to_date('@Value(D_START)', 'YYYYMMDDHH24MISS') 

@david_r. Thx a lot. Now it finally works. I am always very much in doubt about finding the correct notation in FME, and exact examples are hard to find. It's so easy to do it a little wrong. Thank you very much.