How can I create/update an Oracle table using a stored sql procedure instead of a FME-writer. The procedure already has a lot of validation and logging, so it would be a great advantage to use this instead of redoing it all in FME. Is this possible using i.e. SQLExecutor? If so, how to set it up in FME?
Userlevel 4
- 8177 replies
-
11 October 2018
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:
Userlevel 4
- 8177 replies
-
12 October 2018
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?
Userlevel 4
- 8177 replies
-
16 October 2018
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.
Userlevel 4
- 8177 replies
-
16 October 2018
Are you able (allowed) to post the function header here, so that we can se what the function expects?
Userlevel 4
- 8177 replies
-
16 October 2018
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) );
Userlevel 4
- 8177 replies
-
16 October 2018
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.
Userlevel 4
- 8177 replies
-
16 October 2018
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'?
Userlevel 4
- 8177 replies
-
12 November 2018
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?
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;
/
Userlevel 4
- 8177 replies
-
13 November 2018
@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)') It looks like you've omitted the single quotation marks around the date value string. Try this:
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
Userlevel 4
- 8177 replies
-
13 November 2018
@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.
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.
@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.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.