Failure in Executing Oracle Procedure from DB Query Reader

I am trying to execute a stored procedure with Knime using Oracle 12.2 as the Database.

The DB SQL Injector looks like this (ignore the Oracle Procedure syntax):
DROP PROCEDURE simpleproc;
CREATE PROCEDURE simpleproc AS begin SELECT count(*) FROM CONTEXTS;

The above executes successfully and I see the Procedure is created.

However it is when using DB Query Reader to execute this procedure that things go wrong:
CALL simpleproc

The error in Knime console is:
ERROR DB Query Reader 3:772 Execute failed: ORA-06576: not a valid function or procedure name

This is just a sample, I have other Procedures to execute that already exist on Oracle side. But to start, I followed a Knime forum advice and implemented what was provided there on my installation but with Oracle as DB. I have also tried calling an existing procedure that is used in packages already, directly from Knime, like above, and that too failed with the same error.

The Knime Logs:
2021-04-29 19:44:18,362 : ERROR : KNIME-Worker-148-DB Query Reader 3:772 : : Node : DB Query Reader : 3:772 : Execute failed: ORA-06576: not a valid function or procedure name

java.sql.SQLException: ORA-06576: not a valid function or procedure name

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:366)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.impl.monitored.MonitoredStatement.executeQuery(MonitoredStatement.java:81)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:147)
at org.knime.database.node.io.reader.query.DBQueryReaderNodeModel.execute(DBQueryReaderNodeModel.java:165)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:576)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1245)
at org.knime.core.node.Node.execute(Node.java:1025)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:558)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:334)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:210)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)

Caused by: Error : 6576, Position : 5, Sql = call simpleproc, OriginalSql = call simpleproc, Error Msg = ORA-06576: not a valid function or procedure name

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 31 more

Iā€™d be really grateful if anyone would spare some time. I have tried multiple knime & stackoverflow forums but havenā€™t helped me so far.

Hi @jkgala,

just for confirmation - the procedure works if you do the call it in the sql developer? :thinking:

Do you get the same error when you use the

Yes, the procedures are being used normally via sql developer.

Hi @jkgala,

and you are also calling the procedure in the sql developer the exaxt same way? :thinking:

E.g.

From the error message it sounds more like a database or driver problem then a knime problem.
Could you check if the
DB Query Reader and SQL Executor Node return the same error? :thinking:

Hi @jkgala , Iā€™m slightly confused as I know you said ā€œignore the Oracle Procedure syntaxā€, but the statement
CREATE PROCEDURE simpleproc AS begin SELECT count(*) FROM CONTEXTS;
isnā€™t valid Oracle syntax, so unless you actually created it some other way, you might have an Oracle procedure called ā€œsimpleprocā€ but it will be marked as ā€œinvalidā€ and wonā€™t run. An Oracle procedure cannot return the result of a SELECT statement directly in the way you are showing it. What is the actual code for your procedure?

1 Like

Iā€™m not sure how you would go about getting results back from an Oracle stored proc in Knime.(I havenā€™t yet looked into it). Oracle stored procedures arenā€™t like sql-server ones in the way we get data back out.

However, to demonstrate a basic connection to Oracle that Iā€™ve just done on my Oracle, XE database, here is a workflow:

Iā€™m connecting to the database, using a Table creator to create a very simple table BB_TEST consisting of a single column ā€œNAMEā€.

which looks like this in SQL Developer:

Iā€™m then creating a stored procedure using a DB Executor:

Iā€™m then calling the stored procedure with a very basic call, using another DB Executor

This stored procedure writes the supplied name to the BB_TEST table, and I use a DB Query Reader to issue a SELECT statement to read back the contents of that table:

KNIME_oracle_db_create_proc.knwf (14.5 KB)

4 Likes

Thank-you so much for your time and help. I will try this way to replicate it in my environment.

About the syntax: For sharing the question, I placed this example to showcase the Oracle Procedure was created correctly at the DB level. I used multiple ways to call the Oracle Procedure (I also tried with existing Oracle Procedures that are working fine when being used directly within the SQL Developer).

  1. BEGIN
    SIMPLEPROC;
    END

  2. EXEC SIMPLEPROC

  3. and as also provided above, CALL SIMPLEPROC

By trial and error, I arrived at a solution though, by invoking a ORACLE FUNCTION instead of an Oracle Procedure. So, for the current goal, this method will work. The syntax used is:
select Oracle_function from dual within the node ā€˜DB Query Readerā€™ in Knime v4.3.2 release.

Here the error when I tried to execute an Oracle Procedure:
In the node ā€˜DB SQL Executorā€™, is ā€œbegin custom_drop_temp_objects; end;ā€
The reply from Knime is:
"ERROR DB SQL Executor 3:799 Execute failed: ORA-06550: line 1, column 30:
PLS-00103: Encountered the symbol ā€œend-of-fileā€ when expecting one of the following:

  • := . ( @ % ;*"

hi @jkgala

I have created a stored proc in sql developer that does nothing, but is the same name as yours:

create or replace procedure custom_drop_temp_objects as
begin
    -- do something
    null;
end;
/

If I call that using a DB Sql Executor, it executes the node without error, provided that I donā€™t have ā€œSupport multiple SQL statementsā€ ticked, and made sure that there was the ; after the ā€œendā€.

image
[edit- updated/fixed screenshot]
If I have it ticked, or missed out the semi-colon (so I just had ā€œendā€ and not ā€œend;ā€, I get the ORA-06550 message that you got.

As you can see from the screenshot, I was able to make multiple calls to stored procedures within a begin-end block.

4 Likes

To the point @takbb, the error is because I had checked the box - ā€˜Support Multiple SQL Statementsā€™.

As soon as I unchecked it, after looking at your workflows, the statements to execute the Oracle Procedures started working.

Thank-you very much @takbb .

4 Likes

Glad you got it working. I just updated the last screenshot of the nodes in my last post to correct the node comments, so as to avoid future confusion.

It was unfortunate that my earlier screenshots of the DB SQL Executor node contents didnā€™t have that check box visible, as I didnā€™t then realise the importance of it. But we got there! :slight_smile:

1 Like

Hi @jkgala ,
Just picking up on something you mentioned earlierā€¦

Are you sure you were actually able to call the function this way? In my tests of this, the node executed ok, but the data returned was an address reference to the java ResultSet object, rather than the data itself.

I found a convoluted way to return the result set by using Oracle XML features but was wondering if youā€™d managed to do something that made this work, or if it was just appearing to work?

It did work and returns the data that is the result of the function.
The sample statement was ā€œselect schema_owner from dualā€
and the result as in picture:
image

The function created is with following statements: It returns the current_user connected of the user_environment. (I am not that into tech, but for the workflow I am building, I want to plug-in some DEV related components and so determining ways to enable that)

ah ok. I understand. So your function is returning a scalar value (e.g. varchar2), which works fine.

I was thinking you were trying to return a result set from the function. If you have a function such as

create or replace function  simplefunc return sys_refcursor
as
   var_ref sys_refcursor;
begin
    open var_ref for
    select name from bb_test;
    
    return var_ref;
end;
/

Then you canā€™t get a resultset back directly, but I have found a way of doing it (if somebody really needed to and wanted to jump through the additional hoops!) so Iā€™ll add that on the end here once Iā€™ve put together a demo workflow for it. Then we have some more documentation in one place.

1 Like

Iā€™ve uploaded a demo workflow to the hub

It includes demo of how to return result sets from user defined functions: in a DB Query reader you need to utilise Oracle XML functions so that they can translate the function results back into data that can be understood by Knime. Maybe thereā€™s another way, but this is the only mechanism Iā€™ve managed to make work.

So instead of :

SELECT  
	COLUMN_NAME1, 
	COLUMN_NAME2, 
	COLUMN_NAME3 
FROM 
	FUNCTION_NAME

we need to write

SELECT 
	extractvalue(column_value,'/ROW/COLUMN_NAME1') "COLUMN_NAME1",
	extractvalue(column_value,'/ROW/COLUMN_NAME2') "COLUMN_NAME2",
	extractvalue(column_value,'/ROW/COLUMN_NAME3') "COLUMN_NAME3"	
FROM table( xmlsequence(FUNCTION_NAME)) 

There is a performance overhead incurred by XML translation, so for
very large resultsets this may not be practical!

Itā€™s a little convoluted, but if you really need it, it might be worth the pain! :slight_smile:

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.