Icon

KNIME_​Demo Oracle Stored Procs and Functions

KNIME_Demo Oracle Stored Procs and Functions
This one doesn't give desired resultselect bb_func_2_all from dualSo to get the results of a user defined function refcursor, you need to use the variant below SELECT extractvalue(column_value,'/ROW/NAME') "NAME", extractvalue(column_value,'/ROW/AGE') "AGE", extractvalue(column_value,'/ROW/COUNTRY') "COUNTRY" FROM table( xmlsequence(BB_FUNC2_ALL)) SELECT extractvalue(column_value,'/ROW/NAME') "NAME", extractvalue(column_value,'/ROW/AGE') "AGE"FROM table( xmlsequence(bb_func_2_who('UK'))) select * from bb_test_2 Workflow demonstrating Oracle connection, executing DDL, DML statements,including creatingand calling stored procedures and functions.3 May 2021 @takbb Brian Bates A "knime_demo" user can be quickly created for the purposes of demonstration, provided you havethe required privileges on your Oracle database, with the following script:create user knime_demo identified by knime_demo temporary tablespace temp default tablespaceusers;grant resource, connect to knime_demo;alter user knime_demo quota 10M on users; select bb_func_2_country_for_person('Brian') from dual; execute the procedure(which writes to a table)select * from bb_test_2with simple sqlCreate table bb_test_2create procedures and functionsNote settings of Support multiple SQL statements and SQL Statement separator at the bottom of the config window, to match theOracle separator for DDL statements (e.g. CREATE PROCEDURE).This IS DIFFERENT TO the setting when later executing DML statements (e.g. SELECT, and BEGIN... END;)Connect OracleContains "knime_demo" user dummy server/credentials Enter your own connection detailsselect bb_func_2_all from dualAppears to execute okbut doesn't do what we wantdefine"drop if exists"stored procedureCall "drop_if_exists"procedure todrop table bb_test_2without error ifit doesn't existwrap the function with a select statementusing FROM TABLE(XMLSEQUENCE(bb_func2_all))wrap a function with a parameter with a select statementusing FROM TABLE(XMLSEQUENCE(bb_func2_who('UK')))Scalar functionreturn from sql query DB SQL Executor DB Query Reader DB Table Creator DB SQL Executor Oracle Connector DB Query Reader DB SQL Executor DB SQL Executor DB Query Reader DB Query Reader DB Query Reader This one doesn't give desired resultselect bb_func_2_all from dualSo to get the results of a user defined function refcursor, you need to use the variant below SELECT extractvalue(column_value,'/ROW/NAME') "NAME", extractvalue(column_value,'/ROW/AGE') "AGE", extractvalue(column_value,'/ROW/COUNTRY') "COUNTRY" FROM table( xmlsequence(BB_FUNC2_ALL)) SELECT extractvalue(column_value,'/ROW/NAME') "NAME", extractvalue(column_value,'/ROW/AGE') "AGE"FROM table( xmlsequence(bb_func_2_who('UK'))) select * from bb_test_2 Workflow demonstrating Oracle connection, executing DDL, DML statements,including creatingand calling stored procedures and functions.3 May 2021 @takbb Brian Bates A "knime_demo" user can be quickly created for the purposes of demonstration, provided you havethe required privileges on your Oracle database, with the following script:create user knime_demo identified by knime_demo temporary tablespace temp default tablespaceusers;grant resource, connect to knime_demo;alter user knime_demo quota 10M on users; select bb_func_2_country_for_person('Brian') from dual; execute the procedure(which writes to a table)select * from bb_test_2with simple sqlCreate table bb_test_2create procedures and functionsNote settings of Support multiple SQL statements and SQL Statement separator at the bottom of the config window, to match theOracle separator for DDL statements (e.g. CREATE PROCEDURE).This IS DIFFERENT TO the setting when later executing DML statements (e.g. SELECT, and BEGIN... END;)Connect OracleContains "knime_demo" user dummy server/credentials Enter your own connection detailsselect bb_func_2_all from dualAppears to execute okbut doesn't do what we wantdefine"drop if exists"stored procedureCall "drop_if_exists"procedure todrop table bb_test_2without error ifit doesn't existwrap the function with a select statementusing FROM TABLE(XMLSEQUENCE(bb_func2_all))wrap a function with a parameter with a select statementusing FROM TABLE(XMLSEQUENCE(bb_func2_who('UK')))Scalar functionreturn from sql queryDB SQL Executor DB Query Reader DB Table Creator DB SQL Executor Oracle Connector DB Query Reader DB SQL Executor DB SQL Executor DB Query Reader DB Query Reader DB Query Reader

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: @takbb Brian Bates
Created at: 2021-05-03
On NodePit since: 2021-05-03
Last update: 2024-05-02
Created with KNIME version: v4.3.2
Tags: OracleStored ProceduresUser Defined FunctionsXMLSEQUENCEDatabaseSQL ExtractorDMLDDL

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!