I am running a following script,
set serveroutput on;
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN dbms_output.put_line(message);
END;
Its throwing error for set serveroutput on. If I remove that statement query runs successfully with no results returned from oracle db.
@SteveAtSafe
I'm not sure what you want to accomplish, but this is expected behavior:
Please tell us what you need, then perhaps we can point you in the right direction.
I'm not sure what you want to accomplish, but this is expected behavior:
Please tell us what you need, then perhaps we can point you in the right direction.
I am trying to run a plsql script from SQL Creator(SQL statement) in FME. I have connected to my oracle db with connection in SQL Creator as Oracle spatial object.
My script looks like (Can't post the entire script),
set serveroutput on;
declare
var1 er1;
curr_dev varchar2(100);
several variables declared here..
begin
some code ...here
-- Finally get the output
DBMS_OUTPUT.PUT_LINE('Total number of fibers : ' || fibersCount);
DBMS_OUTPUT.PUT_LINE('Total number of fibers with Pencil Filter : ' || fibersWithPencilFilter );
DBMS_OUTPUT.PUT_LINE('% of fibers with Pencil Filter : ' || Round(fibersWithPencilFilter * 1.0/fibersCount,3) || ' %' );
end;
After running this code, I get an error ->
An error occurred while accessing a table result for feature type `';
message was `Execution of statement `SET SERVEROUTPUT ON;
BEGIN [entire code] END;' did not succeed; error was `ORA-00922: missing or invalid option'.
Is there any other alternative for this> How can I get the results from oracle database into FME.
I'm not sure what you want to accomplish, but this is expected behavior:
Please tell us what you need, then perhaps we can point you in the right direction.
Please guide me for an alternative way to get results into FME from Oracle? Do I need to change my script?
You cannot execeute PL SQL in FME, only regular SQL.
There is more information about the differences between PL SQL and SQL here: https://www.guru99.com/sql-vs-pl-sql.html
So basically your only solution is to rewrite your script as regular SQL. If the script contains logic that cannot be directly implemented in SQL, you can probably achieve the same thing using one or several SQLExecutors in conjunction with FME transformers such as the Tester etc.
You cannot execeute PL SQL in FME, only regular SQL.
There is more information about the differences between PL SQL and SQL here: https://www.guru99.com/sql-vs-pl-sql.html
So basically your only solution is to rewrite your script as regular SQL. If the script contains logic that cannot be directly implemented in SQL, you can probably achieve the same thing using one or several SQLExecutors in conjunction with FME transformers such as the Tester etc.
Thank you @david_r. This was really helpful. Now I have a clear direction.