I have a plsql script to perform some task on db. But it doesn't contain any code for returning output(results are stored in variables). I need to get these results into FME SQL executor or creator.
Is there any code sample to start with this? All I need is a example plsql code to get results into FME SQL Executor.
The only way to get data from a PL/SQL block into FME is storing the results in some format.
Two possible solution:
1) Store the result in a table by using an INSERT SQL statement in the PL/SQL and follow by a FeatureReader transformer to read the data from the table.
2) Store the result in a text file using the PL/SQL package UTL_FILE and read the contents of the text file using the FeatureReader transformer (or in case of a single attribute using the AttributeFileReader transformer.
Hope this helps.
The only way to get data from a PL/SQL block into FME is storing the results in some format.
Two possible solution:
1) Store the result in a table by using an INSERT SQL statement in the PL/SQL and follow by a FeatureReader transformer to read the data from the table.
2) Store the result in a text file using the PL/SQL package UTL_FILE and read the contents of the text file using the FeatureReader transformer (or in case of a single attribute using the AttributeFileReader transformer.
Hope this helps.
Hi Erik,
Thank you for your response!
As you said I tried the first option but getting errors. Can you guide me with some example?
Hi Erik,
Thank you for your response!
As you said I tried the first option but getting errors. Can you guide me with some example?
What is the error you run into?
The only way to get data from a PL/SQL block into FME is storing the results in some format.
Two possible solution:
1) Store the result in a table by using an INSERT SQL statement in the PL/SQL and follow by a FeatureReader transformer to read the data from the table.
2) Store the result in a text file using the PL/SQL package UTL_FILE and read the contents of the text file using the FeatureReader transformer (or in case of a single attribute using the AttributeFileReader transformer.
Hope this helps.
Error I get is, Invalid SQL statement .
Is it possible to write a function which will return results?
https://knowledge.safe.com/questions/82948/sql-executor-run-a-procedure.html
Here you have mentioned about it. Is it the best way to do that? Can you please guide me about how to do this.
Error I get is, Invalid SQL statement .
Is it possible to write a function which will return results?
https://knowledge.safe.com/questions/82948/sql-executor-run-a-procedure.html
Here you have mentioned about it. Is it the best way to do that? Can you please guide me about how to do this.
@gauripearl Regarding your error Invalid SQL Statement: You may have to use the FME_SQL_DELIMITER parameter if you have multi-line statements.