KNIME_Demo Oracle Stored Procs and Functions

Demonstration workflow showing how to execute both DML and DDL statements against an Oracle database.

Procedure calls can be made using DB SQL Extractor.

Regular SQL select statements and simple function calls returning scalar values (within select statements) may be made using DB Query Reader.

User-defined functions that return a resultset (i.e. and Oracle SYS_REFCURSOR) do not return the required results if you try to call them using
select MY_FUNCTION from dual;
As this returns a reference to the resultset rather than the resultset itself.

To return the resultset from a user defined function, using

SELECT
extractvalue(column_value,'/ROW/MY_COLUMN_NAME') "MY_COLUMN_NAME"
FROM
table( xmlsequence(MY_FUNCTION_NAME))

Things of note:
In DB SQL Executor, when issuing multiple DDL statements, such as:
create procedure proc1...
/
create procedure proc2...
/
create procedure proc3...

you must TICK "Support multiple SQL statements" and set the "SQL Statement separator" to "/" in the DB SQL Executor config.
But you cannot then use standard sql multi-line comments /* */ as these will fail.
Instead you have to use single line -- comments.

HOWEVER
For DML (select statements, or calls to stored procedires), you must UNTICK the "Support multiple SQL Statements" option, or it will fail with ORA-06550. You are however free then to use /* */ comments.

Multiple sql dml statements should be wrapped in a BEGIN... END; block.

@takbb Brian Bates
version 2